package cn.ksource.web.facade.workReport;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.DateUtil;
|
import cn.ksource.core.util.JsonUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.core.web.WebUtil;
|
import cn.ksource.web.Constants;
|
import cn.ksource.web.service.DataDictionaryService;
|
import cn.ksource.web.util.ChartUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
@SuppressWarnings("rawtypes")
|
@Service
|
public class WorkReportFacadeImpl implements WorkReportFacade{
|
@Resource
|
private BaseDao baseDao;
|
@Autowired
|
private DataDictionaryService dicService;
|
|
|
@Override
|
public List<Map> getWeekByYear(String year) {
|
Calendar calendar = Calendar.getInstance();
|
calendar.set(Calendar.YEAR, Integer.valueOf(year));
|
List<Map> list = new ArrayList<Map>();
|
int count = DateUtil.getWeekNumByYear(Integer.valueOf(year));
|
SimpleDateFormat sf=new SimpleDateFormat("yyyy/MM/dd");
|
if(count>0){
|
for(int i=1;i<=count;i++){
|
Map map = new HashMap();
|
map.put("name", year+"年第"+i+"周");
|
map.put("val", year+i);
|
String beginDay = DateUtil.format("yyyy/MM/dd", DateUtil.getYearWeekFirstDay(Integer.valueOf(year),i));
|
String endDay = DateUtil.format("yyyy/MM/dd", DateUtil.getYearWeekEndDay(Integer.valueOf(year),i));
|
map.put("beginDay", beginDay);
|
map.put("endDay", endDay);
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
|
@Override
|
public List<Map> getWeekByMonth(String date){
|
List<Map> list = new ArrayList<Map>();
|
int count = DateUtil.getWeekNumByMonth(date);
|
SimpleDateFormat sf=new SimpleDateFormat("yyyy/MM/dd");
|
if(count>0){
|
for(int i=1;i<=count;i++){
|
Map map = new HashMap();
|
if(date.substring(4,5).equals("0")){
|
map.put("name",date.substring(0,4)+"年"+date.substring(4).replace("0", "")+"月 第"+i+"周");
|
}else{
|
map.put("name",date.substring(0,4)+"年"+date.substring(4)+"月 第"+i+"周");
|
}
|
String beginDay = DateUtil.format("yyyy/MM/dd", DateUtil.getMonthWeekFirstDay(date,i));
|
String endDay = DateUtil.format("yyyy/MM/dd", DateUtil.getMonthWeekEndDay(date,i));
|
int weekNum = DateUtil.getYearWeekNum(date,i);
|
|
map.put("val", date.substring(0,4)+weekNum);
|
map.put("beginDay", beginDay);
|
map.put("endDay", endDay);
|
list.add(map);
|
}
|
}
|
return list;
|
}
|
|
@Override
|
public List<Map> getMonthByYear(String year) {
|
List<Map> list = new ArrayList<Map>();
|
for(int i=1;i<=12;i++){
|
Map map = new HashMap();
|
map.put("name", year+"年"+i+"月");
|
if(i<10){
|
map.put("val", year+"0"+i);
|
}else{
|
map.put("val", year+i);
|
}
|
list.add(map);
|
}
|
return list;
|
}
|
|
@Override
|
public Map getOrderSurveyChart(String selDate,String statisType, String cusId) {
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
|
sql.append(" select wfstate data_name,businesstype cate_id,count(id) num from workflow_base where wfstate in (1,2,3,4) ");
|
setWhereSql(sql,"createtime",selDate,statisType,param);
|
sql.append(" and customer_id=:cusId group by wfstate,businesstype order by businesstype,wfstate");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
Map<String,Map> dataSetMap = new HashMap();
|
if(dataList!=null && dataList.size()>0){
|
for(Map map:dataList) {
|
dataSetMap.put(map.get("data_name") + "_" + map.get("cate_id"), map);
|
}
|
}
|
if(dataList!=null && dataList.size()>0){
|
for(Map map:dataList) {
|
String data_name =map.get("data_name").toString() ;
|
if(data_name.equals("4")) {
|
String key = "2" + "_" + map.get("cate_id").toString() ;
|
if (dataSetMap.get(key) != null){
|
Integer num=ConvertUtil.obj2Int(dataSetMap.get(key).get("num"))+ConvertUtil.obj2Int(map.get("num"));
|
dataSetMap.get(key).put("num",num);
|
}else{
|
map.put("data_name",2);
|
}
|
}
|
}
|
}
|
LinkedHashMap<String,String> dataNameMap = new LinkedHashMap<String, String>();
|
dataNameMap.put("1", "进行中");
|
dataNameMap.put("2", "已完成");
|
dataNameMap.put("3", "已关闭");
|
List<Map> cateList = getOrderCateList();
|
Map chartMap = ChartUtil.getChartMapByGroupData(dataList,dataNameMap,cateList,0.0);
|
System.out.println("chartMap"+JsonUtil.map2Json(chartMap));
|
return chartMap;
|
}
|
|
@Override
|
public Map getOrderSurveyTable(String selDate,String statisType, String cusId) {
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
|
sql.append(" select wfstate data_name,businesstype cate_id,count(id) num from workflow_base where wfstate in (1,2,3,4) ");
|
setWhereSql(sql,"createtime",selDate,statisType,param);
|
sql.append(" and customer_id=:cusId group by businesstype,wfstate ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
sql.setLength(0);
|
sql.append(" SELECT businesstype cate_id,count(id) num FROM workflow_base WHERE customer_id = :cusId ");
|
setWhereSql(sql,"createtime",selDate,statisType,param);
|
sql.append(" group by businesstype ");
|
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
|
|
Map dataSetMap = new HashMap();
|
Map extendSetMap = new HashMap();
|
for(Map map:dataList){
|
dataSetMap.put(map.get("data_name")+"_"+map.get("cate_id"), map);
|
}
|
for(Map map:extendList){
|
extendSetMap.put(map.get("cate_id").toString(), map);
|
}
|
//获取工单类型List
|
List<Map> cateList = getOrderCateList();
|
|
List<String> labelList = new ArrayList<String>();
|
List<List<String>> tableDataList = new ArrayList<List<String>>();
|
//组织表头
|
labelList.add("分类");
|
labelList.add("总数");
|
labelList.add("进行中");
|
labelList.add("已完成");
|
labelList.add("已关闭");
|
if(dataList!=null&&dataList.size()>0){
|
Integer totalNum = 0;
|
Integer activiNum = 0;
|
Integer finishNum = 0;
|
Integer closeNum = 0;
|
//组织数据
|
for(Map cate:cateList){
|
String key = cate.get("key").toString();
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(cate.get("val").toString());
|
|
if(extendSetMap.get(key)!=null){
|
tempList.add(((Map)extendSetMap.get(key)).get("num").toString());
|
totalNum = totalNum + Integer.valueOf(((Map)extendSetMap.get(key)).get("num").toString());
|
}else{
|
tempList.add("0");
|
}
|
|
key = "1" + "_" + cate.get("key").toString() ;
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num").toString());
|
activiNum = activiNum + Integer.valueOf(((Map)dataSetMap.get(key)).get("num").toString());
|
}else{
|
tempList.add("0");
|
}
|
String finish_tempList="0";
|
key = "2" + "_" + cate.get("key").toString() ;
|
if(dataSetMap.get(key)!=null){
|
finish_tempList=((Map)dataSetMap.get(key)).get("num").toString();
|
finishNum = finishNum + Integer.valueOf(((Map)dataSetMap.get(key)).get("num").toString());
|
}
|
String close_tempList="";
|
key = "3" + "_" + cate.get("key").toString();
|
if(dataSetMap.get(key)!=null){
|
close_tempList=((Map)dataSetMap.get(key)).get("num").toString();
|
closeNum = closeNum + Integer.valueOf(((Map)dataSetMap.get(key)).get("num").toString());
|
}
|
|
key = "4" + "_" + cate.get("key").toString();
|
if(dataSetMap.get(key)!=null){
|
finish_tempList=String.valueOf(Integer.valueOf(finish_tempList)+Integer.valueOf(((Map)dataSetMap.get(key)).get("num").toString()));
|
finishNum = finishNum + Integer.valueOf(((Map)dataSetMap.get(key)).get("num").toString());
|
}
|
if(finish_tempList!=""){
|
tempList.add(finish_tempList);
|
}else{
|
tempList.add("0");
|
}
|
if(close_tempList!=""){
|
tempList.add(close_tempList);
|
}else{
|
tempList.add("0");
|
}
|
tableDataList.add(tempList);
|
}
|
List<String> tempList = new ArrayList<String>();
|
tempList.add("合计");
|
tempList.add(totalNum.toString());
|
tempList.add(activiNum.toString());
|
tempList.add(finishNum.toString());
|
tempList.add(closeNum.toString());
|
tableDataList.add(tempList);
|
}
|
Map tableMap = new HashMap();
|
tableMap.put("labelList", labelList);
|
tableMap.put("dataList", tableDataList);
|
return tableMap;
|
}
|
|
|
|
/**
|
* 获取工单类型列表
|
* @author chenlong
|
* @return
|
*/
|
private List<Map> getOrderCateList(){
|
List<Map> cateList = new ArrayList<Map>();
|
for(int i=6;i<=10;i++){
|
Map tempMap = new HashMap();
|
if(i==6){
|
tempMap.put("key", "6");
|
tempMap.put("val", "例行维护");
|
}else if(i==7){
|
tempMap.put("key", "7");
|
tempMap.put("val", "健康检查");
|
}else if(i==8){
|
tempMap.put("key", "8");
|
tempMap.put("val", "事件");
|
}else if(i==9){
|
tempMap.put("key", "9");
|
tempMap.put("val", "问题");
|
}else if(i==10){
|
tempMap.put("key", "10");
|
tempMap.put("val", "驻场");
|
}
|
if(!tempMap.isEmpty()){
|
cateList.add(tempMap);
|
}
|
}
|
return cateList;
|
}
|
|
/**
|
* 组装类型查询条件
|
* @author chenlong
|
* @param sql
|
* @param colName
|
* @param selDate
|
* @param statisType
|
* @param param
|
*/
|
private void setWhereSql(StringBuilder sql,String colName,String selDate,String statisType,SqlParameter param){
|
if(statisType.equals("week")){
|
String year = selDate.substring(0, 4);
|
String week = selDate.substring(4);
|
param.addValue("year", year)
|
.addValue("week", week);
|
sql.append(" and year("+colName+")=:year and week("+colName+",5)=:week ");
|
}else if(statisType.equals("quarter")){
|
String year = selDate.substring(0, 4);
|
String quarter = selDate.substring(4);
|
param.addValue("year", year)
|
.addValue("quarter", quarter);
|
sql.append(" and year("+colName+")=:year and quarter("+colName+")=:quarter ");
|
}else if(statisType.equals("month")){
|
param.addValue("month", selDate);
|
sql.append(" and date_format("+colName+",'%Y%m')=:month ");
|
}else if(statisType.equals("year")){
|
param.addValue("year", selDate);
|
sql.append(" and date_format("+colName+",'%Y')=:year ");
|
}
|
}
|
|
@Override
|
public Map getSatisSurveyChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.addValue("selDate", selDate)
|
.addValue("cusId", cusId);
|
sql.append(" select truncate(avg(all_score),2) from totle_satis_info where customer_id=:cusId ");
|
if(statisType.equals("month")){
|
sql.append(" and month =:selDate ");
|
}else if(statisType.equals("year")){
|
sql.append(" and left(month,4) =:selDate ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and concat(left(month,4),quarter(concat(month,'01'))) =:selDate ");
|
}
|
sql.append(" order by update_time desc ");
|
Double y = baseDao.queryForDouble(sql.toString(), param);
|
if(y==null){
|
chartMap.put("nodata", true);
|
}
|
chartMap.put("y", y);
|
return chartMap;
|
}
|
|
@Override
|
public Map getSatisSurveyTable(String selDate, String statisType,String cusId) {
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cus_id", cusId);
|
param.put("selDate", selDate);
|
|
sql.append("SELECT AVG(A.ALL_SCORE) AS ALL_SCORE ,B.CUSTOMER_NAME,B.YWJL_NAME,B.ID FROM TOTLE_SATIS_INFO A,SC_PARTNER_CUSTOMER_INFO B WHERE A.CUSTOMER_ID = B.ID AND A.CUSTOMER_ID = :cus_id ");
|
if(statisType.equals("month")){
|
sql.append(" AND A.MONTH =:selDate ");
|
}else if(statisType.equals("year")){
|
sql.append(" AND left(A.MONTH,4) =:selDate ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" AND CONCAT(left(month,4),quarter(CONCAT(month,'01'))) =:selDate ");
|
}
|
sql.append(" GROUP BY A.CUSTOMER_ID");
|
Map map = baseDao.queryForMap(sql.toString(),param);
|
|
StringBuffer detailsql = new StringBuffer("SELECT A.CUSTOMER_ID,B.SATIS_TYPE,SUM(B.ALL_COUNT) AS ALL_COUNT,SUM(SATIS_COUNT) AS SATIS_COUNT,AVG(FG_RATE) AS FG_RATE,AVG(REQUEST_FG_RATE) AS REQUEST_FG_RATE,AVG(SATIS_SCORE) AS SATIS_SCORE,AVG(REQUEST_SATIS_SCORE) AS REQUEST_SATIS_SCORE,AVG(SATIS_QZ) AS SATIS_QZ FROM TOTLE_SATIS_INFO A,TOTLE_SATIS_DETAIL B WHERE A.ID = B.BUS_ID AND A.CUSTOMER_ID = :cus_id ");
|
if(statisType.equals("month")){
|
detailsql.append(" AND A.MONTH =:selDate ");
|
}else if(statisType.equals("year")){
|
detailsql.append(" AND left(A.MONTH,4) =:selDate ");
|
}else if(statisType.equals("quarter")){
|
detailsql.append(" AND CONCAT(left(month,4),quarter(CONCAT(month,'01'))) =:selDate ");
|
}
|
detailsql.append(" GROUP BY A.CUSTOMER_ID,B.SATIS_TYPE ORDER BY B.SATIS_TYPE ");
|
List<Map> list = baseDao.queryForList(detailsql.toString(), param);
|
if(list.size() > 0){
|
for(Map map1:list){
|
map1.put("SATIS_TYPE_TEXT", Constants.getmapCUSTOMER_SATIS_TEMPLATE_TEMPLATE_TYPE_Label(ConvertUtil.obj2StrBlank(map1.get("SATIS_TYPE"))));
|
}
|
}
|
map.put("satisList", list);
|
return map;
|
}
|
|
@Override
|
public Map getIncidentTypeChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,type_id,type_name from sc_workflow_incident where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by type_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("type_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_TYPE);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getSatisDetailChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select truncate(avg(d.satis_score),2) score,d.satis_type from totle_satis_info i,totle_satis_detail d where i.id = d.bus_id and i.customer_id = :cusId ");
|
if(statisType.equals("month")){
|
sql.append(" and month =:selDate ");
|
}else if(statisType.equals("year")){
|
sql.append(" and left(month,4) =:selDate ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and concat(left(month,4),quarter(concat(month,'01'))) =:selDate ");
|
}
|
sql.append(" group by d.SATIS_TYPE ");
|
sql.append(" union ");
|
sql.append(" select truncate(avg(all_score),2) score,0 as satis_type from totle_satis_info where customer_id = :cusId ");
|
if(statisType.equals("month")){
|
sql.append(" and month =:selDate ");
|
}else if(statisType.equals("year")){
|
sql.append(" and left(month,4) =:selDate ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and concat(left(month,4),quarter(concat(month,'01'))) =:selDate ");
|
}
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>1){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("satis_type").toString(), data);
|
}
|
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
categories.add("分数");
|
for(int i=0;i<=3;i++){
|
if(i!=2){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(i==0){
|
tempMap.put("name","总分数");
|
if(dataSetMap.get("0")!=null){
|
tempList.add(((Map)dataSetMap.get("0")).get("score"));
|
}else{
|
tempList.add(0);
|
}
|
}else if(i==1){
|
tempMap.put("name","响应支持类");
|
if(dataSetMap.get("1")!=null){
|
tempList.add(((Map)dataSetMap.get("1")).get("score"));
|
}else{
|
tempList.add(0);
|
}
|
}else if(i==3){
|
tempMap.put("name","驻场服务类");
|
if(dataSetMap.get("3")!=null){
|
tempList.add(((Map)dataSetMap.get("3")).get("score"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
}
|
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentMonChart(String selDate, String cusId, String statisType) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
|
if(statisType.equals("week")){
|
df = new SimpleDateFormat("yyyyw");
|
}
|
Calendar cal = Calendar.getInstance();
|
cal.setMinimalDaysInFirstWeek(7);
|
cal.setFirstDayOfWeek(Calendar.MONDAY); //设置每周的第一天为星期一
|
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//每周从周一开始
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
|
if(statisType.equals("month")){
|
cal.add(Calendar.MONTH, -1);
|
}else if(statisType.equals("week")){
|
cal.add(Calendar.MONTH, -1);
|
}
|
String lastDate = df.format(cal.getTime());
|
if(statisType.equals("quarter")){
|
lastDate = getLastQuarter(selDate);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
param.put("selDate",selDate);
|
param.put("lastDate",lastDate);
|
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
|
|
|
if(statisType.equals("month")){
|
sql.append(" select date_format(createtime,'%Y%m') cate_id,");
|
}else if(statisType.equals("week")){
|
sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
|
}
|
sql.append(" wfstate data_name,count(id) num from workflow_base where wfstate in (1,2,3,4) and businesstype=:busType ");
|
if(statisType.equals("month")){
|
sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
|
}else if(statisType.equals("week")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5) ) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5) ) ");
|
}
|
|
sql.append(" and customer_id=:cusId group by cate_id,data_name ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
Map<String,Map> dataSet = new HashMap();
|
if(dataList!=null && dataList.size()>0){
|
for(Map map:dataList) {
|
dataSet.put(map.get("data_name") + "_" + map.get("cate_id"), map);
|
}
|
}
|
if(dataList!=null && dataList.size()>0){
|
for(Map map:dataList) {
|
String data_name =map.get("data_name").toString() ;
|
if(data_name.equals("4")) {
|
String key = "2" + "_" + map.get("cate_id").toString() ;
|
if (dataSet.get(key) != null){
|
Integer num=ConvertUtil.obj2Int(dataSet.get(key).get("num"))+ConvertUtil.obj2Int(map.get("num"));
|
dataSet.get(key).put("num",num);
|
}else{
|
map.put("data_name",2);
|
}
|
}
|
}
|
}
|
|
if(dataList!=null&&dataList.size()>0){
|
sql.setLength(0);
|
if(statisType.equals("month")){
|
sql.append(" select date_format(createtime,'%Y%m') cate_id,");
|
}else if(statisType.equals("week")){
|
sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
|
}
|
sql.append(" count(id) num FROM workflow_base WHERE customer_id = :cusId and businesstype=:busType ");
|
if(statisType.equals("month")){
|
sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
|
}else if(statisType.equals("week")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)= substring(:lastDate,5) ) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)= substring(:lastDate,5) ) ");
|
}
|
sql.append(" group by cate_id ");
|
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
|
|
Map dataSetMap = new HashMap();
|
Map extendSetMap = new HashMap();
|
for(Map map:dataList){
|
dataSetMap.put(map.get("data_name")+"_"+map.get("cate_id"), map);
|
}
|
for(Map map:extendList){
|
extendSetMap.put(map.get("cate_id"), map);
|
}
|
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
if(statisType.equals("month")){
|
categories.add("上月");
|
categories.add("本月");
|
}else if(statisType.equals("week")){
|
categories.add("上周");
|
categories.add("本周");
|
}else if(statisType.equals("quarter")){
|
categories.add("上季度");
|
categories.add("本季度");
|
}
|
|
for(int i=0;i<4;i++){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(i==0){
|
tempMap.put("name", "总数");
|
}else if(i==1){
|
tempMap.put("name", "进行中");
|
}else if(i==2){
|
tempMap.put("name", "已完成");
|
}else if(i==3){
|
tempMap.put("name", "已关闭");
|
}
|
//同比，环比
|
for(int j=0;j<2;j++){
|
String key = lastDate;
|
if(j==1){
|
key = selDate;
|
}
|
|
if(i==0){
|
if(extendSetMap.get(key)!=null){
|
tempList.add(((Map)extendSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}else{
|
if(dataSetMap.get(i+"_"+key)!=null){
|
tempList.add(((Map)dataSetMap.get(i+"_"+key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
|
}
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
System.out.println("chartMap"+JsonUtil.map2Json(chartMap));
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentSourceChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,source_id from sc_workflow_incident where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by source_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("source_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_SOURCE);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentCloseChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,resolve_type_id from sc_workflow_incident where customer_id = :cusId and resolve_type_id is not null ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by resolve_type_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("resolve_type_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.CLOSE_REASON);
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
categories.add("关闭原因");
|
for(Map dic:dicList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
tempMap.put("name",dic.get("DATAVALUE").toString());
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
chartMap.put("series", series);
|
chartMap.put("categories", categories);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentFinishRateChart(String selDate, String statisType,String cusId) {
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
sql.append(" select TRUNCATE((total_num-IFNULL(outtime_num,0))*100/total_num,2) ontime_rate from ( ");
|
sql.append(" select count(id) total_num from sc_workflow_incident where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" ) mtotal, ( ");
|
sql.append(" select count(id) outtime_num from sc_workflow_incident where customer_id = :cusId and (answer_timeout=1 or deal_timeout=1) ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" ) mfinish ");
|
Double y = baseDao.queryForDouble(sql.toString(), param);
|
Map chartMap = new HashMap();
|
if(y==null){
|
chartMap.put("nodata", true);
|
}
|
chartMap.put("y", y);
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLvChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,sla_id from sc_workflow_incident where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by sla_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("sla_id"), data);
|
}
|
|
sql.setLength(0);
|
sql.append(" select id,level_name from sc_sla where state=1 order by serial asc");
|
List<Map> dicList = baseDao.queryForList(sql.toString(), param);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("level_name"));
|
if(dataSetMap.get(dic.get("id").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("id").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentStatusTable(String selDate, String statisType,String cusId) {
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("nodeTemplateId", Constants.YXCL);
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select mtotal.THIRD_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num, ");
|
sql.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(first_num, 0)*100/total_num,2) first_rate,truncate((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
|
sql.append(" (select THIRD_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mtotal ");
|
sql.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) manswer ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) mactivi ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) mfinish ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(id) timeout_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mtimeout ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mtimeout.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(id) pri_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where SLA_NAME='LEVEL1' and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mpri ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mpri.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(id) ques_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where QUESTIONID is not null and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mques ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mques.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mdeal ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(i.id) first_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) mfirst ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfirst.THIRD_CATEGORY_ID ");
|
List<Map> baseList = baseDao.queryForList(sql.toString(),param);
|
|
//组织表头
|
lebalList.add("服务目录");
|
lebalList.add("事件数");
|
lebalList.add("待响应");
|
lebalList.add("进行中");
|
lebalList.add("已完成");
|
lebalList.add("超时数");
|
lebalList.add("紧急事件");
|
lebalList.add("升级问题数");
|
lebalList.add("平均处理时长");
|
lebalList.add("一线解决率");
|
lebalList.add("服务级别达成率");
|
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
sql.setLength(0);
|
sql.append(" select total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num, ");
|
sql.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(first_num, 0)*100/total_num,2) first_rate,truncate((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
|
sql.append(" (select count(id) total_num from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mtotal, ");
|
sql.append(" (select count(i.id) answer_num from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) manswer, ");
|
sql.append(" (select count(i.id) activi_num from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mactivi, ");
|
sql.append(" (select count(i.id) finish_num from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mfinish, ");
|
sql.append(" (select count(id) timeout_num from SC_WORKFLOW_INCIDENT where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mtimeout, ");
|
sql.append(" (select count(id) pri_num from SC_WORKFLOW_INCIDENT where SLA_NAME='LEVEL1' and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mpri, ");
|
sql.append(" (select count(id) ques_num from SC_WORKFLOW_INCIDENT where QUESTIONID is not null and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mques, ");
|
sql.append(" (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time from SC_WORKFLOW_INCIDENT where customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mdeal, ");
|
sql.append(" (select count(i.id) first_num from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mfirst ");
|
Map extendMap = baseDao.queryForMap(sql.toString(),param);
|
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("answer_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("timeout_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("pri_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("ques_num")));
|
tempList.add(DateUtil.secToTime(Math.round((Double)base.get("avg_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("first_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("ontime_rate"))+"%");
|
dataList.add(tempList);
|
}
|
List<String> tempList = new ArrayList<String>();
|
tempList.add("合计");
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("answer_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("timeout_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("pri_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ques_num")));
|
tempList.add(DateUtil.secToTime(Math.round((Double)extendMap.get("avg_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("first_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ontime_rate"))+"%");
|
dataList.add(tempList);
|
}
|
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
@Override
|
public Map getIncidentStateChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
param.put("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
|
sql.append(" select count(id) num,wfstate from workflow_base where businesstype =:busType and customer_id = :cusId ");
|
setWhereSql(sql, "createtime", selDate, statisType, param);
|
sql.append(" group by wfstate order by wfstate");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
Map<String,Map> dataxx = new HashMap();
|
if(dataList!=null && dataList.size()>0){
|
for(Map map:dataList) {
|
dataxx.put(map.get("wfstate").toString(), map);
|
}
|
}
|
if(dataList!=null && dataList.size()>0){
|
for(Map map:dataList) {
|
String data_name =map.get("wfstate").toString() ;
|
if(data_name.equals("4")) {
|
String key = "2".toString() ;
|
if (dataxx.get(key) != null){
|
Integer num=ConvertUtil.obj2Int(dataxx.get(key).get("num"))+ConvertUtil.obj2Int(map.get("num"));
|
dataxx.get(key).put("num",num);
|
}else{
|
map.put("wfstate",2);
|
}
|
}
|
}
|
}
|
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("wfstate").toString(), data);
|
}
|
|
List<Map> dicList = new ArrayList<Map>();
|
for(int i=1;i<=3;i++){
|
Map map = new HashMap();
|
if(i==1){
|
map.put("DATAVALUE", "进行中");
|
}else if(i==2){
|
map.put("DATAVALUE", "已完成");
|
}else if(i==3){
|
map.put("DATAVALUE", "已关闭");
|
}
|
map.put("DATAKEY", i);
|
dicList.add(map);
|
}
|
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionMonChart(String selDate, String cusId, String statisType) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
|
if(statisType.equals("week")){
|
df = new SimpleDateFormat("yyyyw");
|
}
|
Calendar cal = Calendar.getInstance();
|
cal.setFirstDayOfWeek(Calendar.MONDAY); //设置每周的第一天为星期一
|
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//每周从周一开始
|
cal.setMinimalDaysInFirstWeek(7);
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
if(statisType.equals("month")){
|
cal.add(Calendar.MONTH, -1);
|
}else if(statisType.equals("week")){
|
cal.add(Calendar.WEEK_OF_YEAR, -1);
|
}
|
String lastDate = df.format(cal.getTime());
|
if(statisType.equals("quarter")){
|
lastDate = getLastQuarter(selDate);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
param.put("selDate",selDate);
|
param.put("lastDate",lastDate);
|
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION);
|
|
|
if(statisType.equals("month")){
|
sql.append(" select date_format(createtime,'%Y%m') cate_id,");
|
}else if(statisType.equals("week")){
|
sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
|
}
|
sql.append(" wfstate data_name,count(id) num from workflow_base where wfstate in (1,2,3) and businesstype=:busType ");
|
if(statisType.equals("month")){
|
sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
|
}else if(statisType.equals("week")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
|
}
|
|
sql.append(" and customer_id=:cusId group by cate_id,data_name ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
if(dataList!=null&&dataList.size()>0){
|
sql.setLength(0);
|
if(statisType.equals("month")){
|
sql.append(" select date_format(createtime,'%Y%m') cate_id,");
|
}else if(statisType.equals("week")){
|
sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
|
}
|
sql.append(" count(id) num FROM workflow_base WHERE customer_id = :cusId and businesstype=:busType ");
|
if(statisType.equals("month")){
|
sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
|
}else if(statisType.equals("week")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
|
}
|
sql.append(" group by cate_id ");
|
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
|
|
Map dataSetMap = new HashMap();
|
Map extendSetMap = new HashMap();
|
for(Map map:dataList){
|
dataSetMap.put(map.get("data_name")+"_"+map.get("cate_id"), map);
|
}
|
for(Map map:extendList){
|
extendSetMap.put(map.get("cate_id"), map);
|
}
|
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
if(statisType.equals("month")){
|
categories.add("上月");
|
categories.add("本月");
|
}else if(statisType.equals("week")){
|
categories.add("上周");
|
categories.add("本周");
|
}else if(statisType.equals("quarter")){
|
categories.add("上季度");
|
categories.add("本季度");
|
}
|
|
for(int i=0;i<4;i++){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(i==0){
|
tempMap.put("name", "总数");
|
}else if(i==1){
|
tempMap.put("name", "进行中");
|
}else if(i==2){
|
tempMap.put("name", "已完成");
|
}else if(i==3){
|
tempMap.put("name", "已关闭");
|
}
|
//同比，环比
|
for(int j=0;j<2;j++){
|
String key = lastDate;
|
if(j==1){
|
key = selDate;
|
}
|
|
if(i==0){
|
if(extendSetMap.get(key)!=null){
|
tempList.add(((Map)extendSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}else{
|
if(dataSetMap.get(i+"_"+key)!=null){
|
tempList.add(((Map)dataSetMap.get(i+"_"+key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
|
}
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionSourceChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,source_id from sc_workflow_question where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by source_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("source_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.QUESTION_SOURCE);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionCloseChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,resolve_type_id from sc_workflow_question where customer_id = :cusId and state=7 ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by resolve_type_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("resolve_type_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.QUESTIONCLOSE_RESOLVE_TYPE);
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
categories.add("关闭原因");
|
for(Map dic:dicList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
tempMap.put("name",dic.get("DATAVALUE").toString());
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
chartMap.put("series", series);
|
chartMap.put("categories", categories);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionEffectChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,influence_id from sc_workflow_question where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by influence_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("influence_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.EVENT_EFFECT_DG);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionStatusTable(String selDate, String statisType,String cusId) {
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
//定义报表变量
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
//查询数据
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select THIRD_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(sleep_num,0) sleep_num, ");
|
sql.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from ( ");
|
sql.append(" select count(b.id) total_num,i.THIRD_CATEGORY_NAME,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" GROUP BY I.THIRD_CATEGORY_ID ");
|
sql.append(" ) mtotal left join ( ");
|
sql.append(" select count(b.id) answer_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i,( ");
|
sql.append(" select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");
|
sql.append(" ) n where b.ID = i.FLOW_ID and b.id = n.FLOWID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and b.WFSTATE = 1 GROUP BY I.THIRD_CATEGORY_ID ");
|
sql.append(" ) manswer on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID left join ( ");
|
sql.append(" select count(b.id) activi_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and b.WFSTATE=1 GROUP BY I.THIRD_CATEGORY_ID ");
|
sql.append(" ) mactivi on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID left join ( ");
|
sql.append(" select count(b.id) sleep_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and b.WFSTATE=6 GROUP BY I.THIRD_CATEGORY_ID ");
|
sql.append(" ) msleep on mtotal.THIRD_CATEGORY_ID = msleep.THIRD_CATEGORY_ID left join ( ");
|
sql.append(" select count(b.id) finish_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and (b.WFSTATE=2 or b.WFSTATE=4) GROUP BY I.THIRD_CATEGORY_ID ");
|
sql.append(" ) mfinish on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID left join ( ");
|
sql.append(" select count(b.id) close_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and i.state=7 GROUP BY I.THIRD_CATEGORY_ID ");
|
sql.append(" ) mclose on mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID ");
|
List<Map> baseList = baseDao.queryForList(sql.toString(),param);
|
//组织表头
|
lebalList.add("服务目录");
|
lebalList.add("问题总数");
|
lebalList.add("待响应");
|
lebalList.add("进行中");
|
lebalList.add("已挂起");
|
lebalList.add("已解决");
|
lebalList.add("已关闭");
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
sql.setLength(0);
|
sql.append(" select total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(sleep_num,0) sleep_num, ");
|
sql.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from ( ");
|
sql.append(" select count(b.id) total_num from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" ) mtotal, ( ");
|
sql.append(" select count(b.id) answer_num from workflow_base b ,SC_WORKFLOW_QUESTION i,( ");
|
sql.append(" select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");
|
sql.append(" ) n where b.ID = i.FLOW_ID and b.id = n.FLOWID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and b.WFSTATE = 1 ");
|
sql.append(" ) manswer , ( ");
|
sql.append(" select count(b.id) activi_num from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and b.WFSTATE=1 ");
|
sql.append(" ) mactivi , ( ");
|
sql.append(" select count(b.id) sleep_num from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and b.WFSTATE=6 ");
|
sql.append(" ) msleep , ( ");
|
sql.append(" select count(b.id) finish_num from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and (b.WFSTATE=2 or b.WFSTATE=4) ");
|
sql.append(" ) mfinish , ( ");
|
sql.append(" select count(b.id) close_num from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
sql.append(" where b.ID = i.FLOW_ID and b.BUSINESSTYPE = 9 and b.customer_id = :cusId ");
|
setWhereSql(sql, "b.createtime", selDate, statisType, param);
|
sql.append(" and i.state=7 ");
|
sql.append(" ) mclose ");
|
Map extendMap = baseDao.queryForMap(sql.toString(),param);
|
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("answer_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("sleep_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("close_num")));
|
dataList.add(tempList);
|
}
|
List<String> tempList = new ArrayList<String>();
|
tempList.add("合计");
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("answer_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("sleep_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("close_num")));
|
dataList.add(tempList);
|
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
@Override
|
public Map getIncidentLocalMonChart(String selDate, String cusId, String statisType) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
|
if(statisType.equals("week")){
|
df = new SimpleDateFormat("yyyyw");
|
}
|
Calendar cal = Calendar.getInstance();
|
cal.setFirstDayOfWeek(Calendar.MONDAY); //设置每周的第一天为星期一
|
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);//每周从周一开始
|
cal.setMinimalDaysInFirstWeek(7);
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
if(statisType.equals("month")){
|
cal.add(Calendar.MONTH, -1);
|
}else if(statisType.equals("week")){
|
cal.add(Calendar.WEEK_OF_YEAR, -1);
|
}
|
String lastDate = df.format(cal.getTime());
|
if(statisType.equals("quarter")){
|
lastDate = getLastQuarter(selDate);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
param.put("selDate",selDate);
|
param.put("lastDate",lastDate);
|
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT_LOCAL);
|
|
|
if(statisType.equals("month")){
|
sql.append(" select date_format(createtime,'%Y%m') cate_id,");
|
}else if(statisType.equals("week")){
|
sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
|
}
|
sql.append(" ( CASE WHEN WFSTATE = 1 THEN '1' WHEN WFSTATE = 2 THEN '2' WHEN WFSTATE = 3 THEN '3' WHEN WFSTATE = 4 THEN '2' WHEN WFSTATE = 6 THEN '6' END ) data_name,count(id) num from workflow_base where wfstate in (1,2,3,4) and businesstype=:busType ");
|
if(statisType.equals("month")){
|
sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
|
}else if(statisType.equals("week")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
|
}
|
|
sql.append(" and customer_id=:cusId group by cate_id,data_name ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
|
if(dataList!=null&&dataList.size()>0){
|
sql.setLength(0);
|
if(statisType.equals("month")){
|
sql.append(" select date_format(createtime,'%Y%m') cate_id,");
|
}else if(statisType.equals("week")){
|
sql.append(" select concat(year(createtime),week(createtime,5)) cate_id,");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(year(createtime),quarter(createtime)) cate_id,");
|
}
|
sql.append(" count(id) num FROM workflow_base WHERE customer_id = :cusId and businesstype=:busType ");
|
if(statisType.equals("month")){
|
sql.append(" and (date_format(createtime,'%Y%m') = :selDate or date_format(createtime,'%Y%m')=:lastDate) ");
|
}else if(statisType.equals("week")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (week(createtime,5) = substring(:selDate,5) or week(createtime,5)=substring(:lastDate,5)) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and year(createtime)=left(:selDate,4) and (quarter(createtime) = substring(:selDate,5) or quarter(createtime)=substring(:lastDate,5)) ");
|
}
|
sql.append(" group by cate_id ");
|
List<Map> extendList = baseDao.queryForList(sql.toString(),param);
|
|
Map dataSetMap = new HashMap();
|
Map extendSetMap = new HashMap();
|
for(Map map:dataList){
|
dataSetMap.put(map.get("data_name")+"_"+map.get("cate_id"), map);
|
}
|
for(Map map:extendList){
|
extendSetMap.put(map.get("cate_id"), map);
|
}
|
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
if(statisType.equals("month")){
|
categories.add("上月");
|
categories.add("本月");
|
}else if(statisType.equals("week")){
|
categories.add("上周");
|
categories.add("本周");
|
}else if(statisType.equals("quarter")){
|
categories.add("上季度");
|
categories.add("本季度");
|
}
|
|
for(int i=0;i<4;i++){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(i==0){
|
tempMap.put("name", "总数");
|
}else if(i==1){
|
tempMap.put("name", "进行中");
|
}else if(i==2){
|
tempMap.put("name", "已完成");
|
}else if(i==3){
|
tempMap.put("name", "已关闭");
|
}
|
//同比，环比
|
for(int j=0;j<2;j++){
|
String key = lastDate;
|
if(j==1){
|
key = selDate;
|
}
|
|
if(i==0){
|
if(extendSetMap.get(key)!=null){
|
tempList.add(((Map)extendSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}else{
|
if(dataSetMap.get(i+"_"+key)!=null){
|
tempList.add(((Map)dataSetMap.get(i+"_"+key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
|
}
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalSourceChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,source_id from sc_workflow_incident_local where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by source_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("source_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_SOURCE);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalTypeChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,type_id,type_name from sc_workflow_incident_local where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by type_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("type_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.INCIDENT_TYPE);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("DATAVALUE"));
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalCloseChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,resolve_type_id from sc_workflow_incident_local where customer_id = :cusId and resolve_type_id is not null ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by resolve_type_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("resolve_type_id"), data);
|
}
|
|
List<Map> dicList = dicService.getDataDictionaryByCategoryKey(Constants.CLOSE_REASON);
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
categories.add("关闭原因");
|
for(Map dic:dicList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
tempMap.put("name",dic.get("DATAVALUE").toString());
|
if(dataSetMap.get(dic.get("DATAKEY").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("DATAKEY").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
chartMap.put("series", series);
|
chartMap.put("categories", categories);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalFinishRateChart(String selDate,String statisType, String cusId) {
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
sql.append(" select TRUNCATE((total_num-IFNULL(outtime_num,0))*100/total_num,2) ontime_rate from ( ");
|
sql.append(" select count(id) total_num from sc_workflow_incident_local where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" ) mtotal, ( ");
|
sql.append(" select count(id) outtime_num from sc_workflow_incident_local where customer_id = :cusId and (answer_timeout=1 or deal_timeout=1) ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" ) mfinish ");
|
Double y = baseDao.queryForDouble(sql.toString(), param);
|
Map chartMap = new HashMap();
|
if(y==null){
|
chartMap.put("nodata", true);
|
}
|
chartMap.put("y", y);
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalLvChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("selDate", selDate);
|
sql.append(" select count(id) num,sla_id from sc_workflow_incident_local where customer_id = :cusId ");
|
setWhereSql(sql, "create_time", selDate, statisType, param);
|
sql.append(" group by sla_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("sla_id"), data);
|
}
|
|
sql.setLength(0);
|
sql.append(" select id,level_name from sc_sla where state=1 order by serial asc");
|
List<Map> dicList = baseDao.queryForList(sql.toString(), param);
|
List seriesData = new ArrayList();
|
for(Map dic:dicList){
|
List tempList = new ArrayList();
|
tempList.add(dic.get("level_name"));
|
if(dataSetMap.get(dic.get("id").toString())!=null){
|
tempList.add(((Map)dataSetMap.get(dic.get("id").toString())).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
seriesData.add(tempList);
|
}
|
chartMap.put("seriesData", seriesData);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalStatusTable(String selDate, String statisType,String cusId) {
|
SqlParameter param = new SqlParameter();
|
param.put("cusId", cusId);
|
param.put("nodeTemplateId", Constants.YXCL);
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select mtotal.THIRD_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num, ");
|
sql.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(finish_num, 0)*100/total_num,2) first_rate,truncate((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
|
sql.append(" (select a.THIRD_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL a,workflow_base b where a.customer_id=:cusId and a.id=b.BUSINESS_ID and b.WFSTATE<>3 ");
|
setWhereSql(sql, "a.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by a.THIRD_CATEGORY_ID) mtotal ");
|
sql.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.WFSTATE<>3 and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) manswer ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and b.WFSTATE<>3 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) mactivi ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) mfinish ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(id) timeout_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mtimeout ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mtimeout.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(id) pri_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where SLA_NAME='LEVEL1' and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mpri ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mpri.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(id) ques_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where QUESTIONID is not null and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mques ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mques.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by THIRD_CATEGORY_ID) mdeal ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
|
sql.append(" left join (select count(i.id) first_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" group by i.THIRD_CATEGORY_ID) mfirst ");
|
sql.append(" on mtotal.THIRD_CATEGORY_ID = mfirst.THIRD_CATEGORY_ID ");
|
List<Map> baseList = baseDao.queryForList(sql.toString(),param);
|
|
//组织表头
|
lebalList.add("服务目录");
|
lebalList.add("事件数");
|
lebalList.add("待响应");
|
lebalList.add("进行中");
|
lebalList.add("已完成");
|
lebalList.add("超时数");
|
lebalList.add("紧急事件");
|
lebalList.add("升级问题数");
|
lebalList.add("平均处理时长");
|
lebalList.add("驻场解决率");
|
lebalList.add("服务级别达成率");
|
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
sql.setLength(0);
|
sql.append(" select total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num, ");
|
sql.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(finish_num, 0)*100/total_num,2) first_rate,truncate((total_num-IFNULL(timeout_num, 0))*100/total_num,2) ontime_rate from ");
|
sql.append(" (select count(a.id) total_num from SC_WORKFLOW_INCIDENT_LOCAL a,workflow_base b where a.customer_id=:cusId and a.id=b.BUSINESS_ID and b.WFSTATE<>3 ");
|
setWhereSql(sql, "a.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mtotal, ");
|
sql.append(" (select count(i.id) answer_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.WFSTATE<>3 and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) manswer, ");
|
sql.append(" (select count(i.id) activi_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mactivi, ");
|
sql.append(" (select count(i.id) finish_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mfinish, ");
|
sql.append(" (select count(id) timeout_num from SC_WORKFLOW_INCIDENT_LOCAL where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mtimeout, ");
|
sql.append(" (select count(id) pri_num from SC_WORKFLOW_INCIDENT_LOCAL where SLA_NAME='LEVEL1' and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mpri, ");
|
sql.append(" (select count(id) ques_num from SC_WORKFLOW_INCIDENT_LOCAL where QUESTIONID is not null and customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mques, ");
|
sql.append(" (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time from SC_WORKFLOW_INCIDENT_LOCAL where customer_id=:cusId ");
|
setWhereSql(sql, "CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mdeal, ");
|
sql.append(" (select count(i.id) first_num from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.customer_id=:cusId ");
|
setWhereSql(sql, "i.CREATE_TIME", selDate, statisType, param);
|
sql.append(" ) mfirst ");
|
Map extendMap = baseDao.queryForMap(sql.toString(),param);
|
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("answer_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("timeout_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("pri_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("ques_num")));
|
|
tempList.add(DateUtil.secToTime(Math.round((Double)base.get("avg_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("first_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("ontime_rate"))+"%");
|
dataList.add(tempList);
|
}
|
List<String> tempList = new ArrayList<String>();
|
tempList.add("合计");
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("answer_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("timeout_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("pri_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ques_num")));
|
tempList.add(DateUtil.secToTime(Math.round((Double)extendMap.get("avg_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("first_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get("ontime_rate"))+"%");
|
dataList.add(tempList);
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
@Override
|
public Map getSatisMonChart(String selDate, String statisType, String cusId) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
|
if(statisType.equals("year")){
|
df = new SimpleDateFormat("yyyy");
|
}
|
Calendar cal = Calendar.getInstance();
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
if(statisType.equals("month")){
|
cal.add(Calendar.MONTH, -1);
|
}else if(statisType.equals("year")){
|
cal.add(Calendar.YEAR, -1);
|
}
|
String lastDate = df.format(cal.getTime());
|
if(statisType.equals("quarter")){
|
lastDate = getLastQuarter(selDate);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
param.put("selDate",selDate);
|
param.put("lastDate",lastDate);
|
param.put("busType",Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
|
|
//查询数据
|
if(statisType.equals("month")){
|
sql.append(" select i.month cate_id, ");
|
}else if(statisType.equals("year")){
|
sql.append(" select left(i.month,4) cate_id, ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(left(i.month,4),quarter(concat(i.month,'01'))) cate_id, ");
|
}
|
sql.append(" d.satis_type data_name,truncate(avg(d.satis_score),0) num from totle_satis_info i,totle_satis_detail d where i.id = d.bus_id ");
|
if(statisType.equals("month")){
|
sql.append(" and (i.month = :selDate or i.month=:lastDate) ");
|
}else if(statisType.equals("year")){
|
sql.append(" and (left(i.month,4) = :selDate or left(i.month,4)=:lastDate) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and (concat(left(i.month,4),quarter(concat(i.month,'01'))) = :selDate or concat(left(i.month,4),quarter(concat(i.month,'01')))=:lastDate) ");
|
}
|
sql.append(" and i.customer_id=:cusId group by cate_id,data_name ");
|
sql.append(" union ");
|
if(statisType.equals("month")){
|
sql.append(" select i.month cate_id, ");
|
}else if(statisType.equals("year")){
|
sql.append(" select left(i.month,4) cate_id, ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" select concat(left(i.month,4),quarter(concat(i.month,'01'))) cate_id, ");
|
}
|
sql.append(" 0 as data_name,truncate(avg(i.all_score),0) num from totle_satis_info i where 1=1 ");
|
if(statisType.equals("month")){
|
sql.append(" and (i.month = :selDate or i.month=:lastDate) ");
|
}else if(statisType.equals("year")){
|
sql.append(" and (left(i.month,4) = :selDate or left(i.month,4)=:lastDate) ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and (concat(left(i.month,4),quarter(concat(i.month,'01'))) = :selDate or concat(left(i.month,4),quarter(concat(i.month,'01')))=:lastDate) ");
|
}
|
sql.append(" and i.customer_id=:cusId group by cate_id,data_name ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
Map extendSetMap = new HashMap();
|
for(Map map:dataList){
|
dataSetMap.put(map.get("data_name")+"_"+map.get("cate_id"), map);
|
}
|
|
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
if(statisType.equals("month")){
|
categories.add("上月");
|
categories.add("本月");
|
}else if(statisType.equals("year")){
|
categories.add("上年");
|
categories.add("本年");
|
}else if(statisType.equals("quarter")){
|
categories.add("上季度");
|
categories.add("本季度");
|
}
|
|
for(int i=0;i<=3;i++){
|
if(i!=2){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(i==0){
|
tempMap.put("name", "总评分");
|
}else if(i==1){
|
tempMap.put("name", "响应支持类");
|
}else if(i==3){
|
tempMap.put("name", "驻场服务类");
|
}
|
//同比，环比
|
for(int j=0;j<2;j++){
|
String key = lastDate;
|
if(j==1){
|
key = selDate;
|
}
|
if(dataSetMap.get(i+"_"+key)!=null){
|
tempList.add(((Map)dataSetMap.get(i+"_"+key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
}
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getSatisConverRateColChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.addValue("selDate", selDate)
|
.addValue("cusId", cusId);
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select truncate(avg(d.fg_rate),2) rate,100-truncate(avg(d.fg_rate),2) fill_rate,satis_type from totle_satis_info i,totle_satis_detail d where i.id = d.bus_id ");
|
if(statisType.equals("month")){
|
sql.append(" and i.month=:selDate ");
|
}else if(statisType.equals("quarter")){
|
sql.append(" and concat(left(i.month,4),quarter(concat(i.month,'01')))=:selDate ");
|
}
|
sql.append(" and i.customer_id=:cusId group by d.satis_type ");
|
|
List<Map> dataList= baseDao.queryForList(sql.toString(), param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("satis_type").toString(), data);
|
}
|
List<String> categories = new ArrayList<String>();
|
for(int i=1;i<=3;i++){
|
if(i==1){
|
categories.add("响应支持类");
|
}else if(i==3){
|
categories.add("驻场服务类");
|
}
|
}
|
List<Map> series = new ArrayList<Map>();
|
|
for(int i=1;i<=2;i++){
|
Map tempMap = new HashMap();
|
if(i==1){
|
tempMap.put("name", "未调查");
|
}else{
|
tempMap.put("name", "已调查");
|
}
|
List tempList = new ArrayList();
|
for(int j=1;j<=3;j++){
|
if(j!=2){
|
String key = String.valueOf(j);
|
if(i==1){
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("fill_rate"));
|
}else{
|
tempList.add(0);
|
}
|
}else{
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("rate"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
}
|
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentNumChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("selDate", selDate)
|
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) num,date_format(createtime,'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
|
sql.append(" and date_format(createtime,'%Y') = :selDate group by cate_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(ConvertUtil.obj2StrBlank(data.get("cate_id")),data);
|
}
|
List<String> categories = new ArrayList<String>();
|
List<Map> series = new ArrayList<Map>();
|
Map tempMap = new HashMap();
|
tempMap.put("name", "数量");
|
List tempList = new ArrayList();
|
for(int i=1;i<=12;i++){
|
categories.add(i+"月");
|
String key = selDate + i;
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentYearChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyy");
|
Calendar cal = Calendar.getInstance();
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
cal.add(Calendar.YEAR, -1);
|
String lastDate = df.format(cal.getTime());
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("selDate", selDate)
|
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT)
|
.addValue("lastDate", lastDate);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) num,date_format(createtime,'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
|
sql.append(" and (date_format(createtime,'%Y') = :selDate or date_format(createtime,'%Y') = :lastDate) group by cate_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(ConvertUtil.obj2StrBlank(data.get("cate_id")),data);
|
}
|
List<String> categories = new ArrayList<String>();
|
categories.add("数量");
|
List<Map> series = new ArrayList<Map>();
|
|
for(int i=1;i<=2;i++){
|
Map tempMap = new HashMap();
|
tempMap.put("name", "去年");
|
List tempList = new ArrayList();
|
String key = lastDate;
|
if(i==2){
|
key = selDate;
|
tempMap.put("name", "今年");
|
}
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionNumChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("selDate", selDate)
|
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) num,date_format(createtime,'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
|
sql.append(" and date_format(createtime,'%Y') = :selDate group by cate_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(ConvertUtil.obj2StrBlank(data.get("cate_id")),data);
|
}
|
List<String> categories = new ArrayList<String>();
|
List<Map> series = new ArrayList<Map>();
|
Map tempMap = new HashMap();
|
tempMap.put("name", "数量");
|
List tempList = new ArrayList();
|
for(int i=1;i<=12;i++){
|
categories.add(i+"月");
|
String key = selDate + i;
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getQuestionYearChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyy");
|
Calendar cal = Calendar.getInstance();
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
cal.add(Calendar.YEAR, -1);
|
String lastDate = df.format(cal.getTime());
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("selDate", selDate)
|
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION)
|
.addValue("lastDate", lastDate);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) num,date_format(createtime,'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
|
sql.append(" and (date_format(createtime,'%Y') = :selDate or date_format(createtime,'%Y') = :lastDate) group by cate_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(ConvertUtil.obj2StrBlank(data.get("cate_id")),data);
|
}
|
List<String> categories = new ArrayList<String>();
|
categories.add("数量");
|
List<Map> series = new ArrayList<Map>();
|
|
for(int i=1;i<=2;i++){
|
Map tempMap = new HashMap();
|
tempMap.put("name", "去年");
|
List tempList = new ArrayList();
|
String key = lastDate;
|
if(i==2){
|
key = selDate;
|
tempMap.put("name", "今年");
|
}
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalNumChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("selDate", selDate)
|
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT_LOCAL);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) num,date_format(createtime,'%Y%c') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
|
sql.append(" and date_format(createtime,'%Y') = :selDate group by cate_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(ConvertUtil.obj2StrBlank(data.get("cate_id")),data);
|
}
|
List<String> categories = new ArrayList<String>();
|
List<Map> series = new ArrayList<Map>();
|
Map tempMap = new HashMap();
|
tempMap.put("name", "数量");
|
List tempList = new ArrayList();
|
for(int i=1;i<=12;i++){
|
categories.add(i+"月");
|
String key = selDate + i;
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public Map getIncidentLocalYearChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SimpleDateFormat df = new SimpleDateFormat("yyyy");
|
Calendar cal = Calendar.getInstance();
|
try {
|
cal.setTime(df.parse(selDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
cal.add(Calendar.YEAR, -1);
|
String lastDate = df.format(cal.getTime());
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("selDate", selDate)
|
.addValue("busType", Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT_LOCAL)
|
.addValue("lastDate", lastDate);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) num,date_format(createtime,'%Y') cate_id from workflow_base where businesstype = :busType and customer_id =:cusId ");
|
sql.append(" and (date_format(createtime,'%Y') = :selDate or date_format(createtime,'%Y') = :lastDate) group by cate_id ");
|
List<Map> dataList = baseDao.queryForList(sql.toString(),param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(ConvertUtil.obj2StrBlank(data.get("cate_id")),data);
|
}
|
List<String> categories = new ArrayList<String>();
|
categories.add("数量");
|
List<Map> series = new ArrayList<Map>();
|
|
for(int i=1;i<=2;i++){
|
Map tempMap = new HashMap();
|
tempMap.put("name", "去年");
|
List tempList = new ArrayList();
|
String key = lastDate;
|
if(i==2){
|
key = selDate;
|
tempMap.put("name", "今年");
|
}
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("num"));
|
}else{
|
tempList.add(0);
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
|
return chartMap;
|
}
|
|
@Override
|
public Map getSatisNumChart(String selDate, String statisType, String cusId) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.put("cusId",cusId);
|
param.put("selDate",selDate);
|
|
StringBuilder builder = new StringBuilder();
|
builder.append(" SELECT I.ALL_SCORE,D.SATIS_SCORE,D.SATIS_TYPE,I.MONTH FROM TOTLE_SATIS_DETAIL D ,TOTLE_SATIS_INFO I ");
|
builder.append(" WHERE LEFT(I.MONTH,4)=:selDate AND D.BUS_ID = I.ID AND I.CUSTOMER_ID = :cusId GROUP BY D.SATIS_TYPE,I.MONTH ORDER BY SATIS_TYPE,MONTH");
|
List<Map> queryList = baseDao.queryForList(builder.toString(), param);
|
builder.setLength(0);
|
Map<String,Object> queryMap = new HashMap<String,Object>();
|
|
if(queryList!=null&&queryList.size()>0){
|
for(Map map:queryList){
|
String key = map.get("SATIS_TYPE").toString()+"_"+map.get("MONTH").toString();
|
queryMap.put(key, map);
|
}
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = new ArrayList<String>();
|
for(int i=1;i<=12;i++){
|
categories.add(i+"月");
|
}
|
|
List<String> typeList = new ArrayList();
|
typeList.add("1");
|
typeList.add("3");
|
|
Map tempWholeMap = new HashMap();
|
List tempWholeList = new ArrayList();
|
tempWholeMap.put("name", "整体得分");
|
tempWholeMap.put("type", "spline");
|
//组装数据
|
for(String type:typeList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(type.equals("1")){
|
tempMap.put("name", "响应支持类");
|
}else if(type.equals("3")){
|
tempMap.put("name", "驻场服务类");
|
}
|
for(int i=1;i<=12;i++){
|
String key = type + "_" + selDate;
|
if(i<10){
|
key = key + "0" + i;
|
}else{
|
key = key + i;
|
}
|
|
if(queryMap.get(key)!=null){
|
tempList.add(((Map)queryMap.get(key)).get("SATIS_SCORE"));
|
//整体得分
|
if(type.equals("1")){
|
tempWholeList.add(((Map)queryMap.get(key)).get("ALL_SCORE"));
|
}
|
}else{
|
tempList.add(0);
|
//整体得分
|
if(type.equals("1")){
|
tempWholeList.add(0);
|
}
|
}
|
}
|
|
tempMap.put("data", tempList);
|
tempMap.put("type", "column");
|
series.add(tempMap);
|
if(type.equals("3")){
|
tempWholeMap.put("data", tempWholeList);
|
series.add(tempWholeMap);
|
}
|
chartMap.put("series", series);
|
chartMap.put("categories", categories);
|
}
|
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public boolean updateWorkSummary(HttpServletRequest request) {
|
|
String id = request.getParameter("id");
|
String note = request.getParameter("note");
|
String summaryType = request.getParameter("summaryType");
|
String summaryDate = request.getParameter("summaryDate");
|
String cusId = request.getParameter("cusId");
|
Map user = WebUtil.getLoginUser(request).getLoginUser();
|
String userId = user.get("ID").toString();
|
String userName = user.get("ZSXM").toString();
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
|
param.addValue("note", note)
|
.addValue("cusId", cusId)
|
.addValue("userId", userId)
|
.addValue("userName", userName)
|
.addValue("createTime", DateUtil.getCurrentDate14())
|
.addValue("updateTime", DateUtil.getCurrentDate14())
|
.addValue("summaryType", summaryType)
|
.addValue("summaryDate", summaryDate);
|
|
if(StringUtil.isBlank(id)){
|
sql.append(" insert into work_summary (id,create_time,create_user_id,create_user_name,note,update_time,summary_type,summary_date,cus_id,state) ");
|
sql.append(" values (:id,:createTime,:userId,:userName,:note,:updateTime,:summaryType,:summaryDate,:cusId,1) ");
|
param.addValue("id", StringUtil.getUUID());
|
}else{
|
sql.append(" update work_summary set note=:note,update_time=:updateTime where id=:id ");
|
param.addValue("id", id);
|
}
|
baseDao.execute(sql.toString(), param);
|
return true;
|
}
|
|
@Override
|
public Map getWorkSummary(String cusId, String summaryType,String summaryDate) {
|
StringBuilder sql = new StringBuilder();
|
SqlParameter param = new SqlParameter();
|
sql.append(" select id,note from work_summary where cus_id=:cusId and summary_type=:summaryType and summary_date=:summaryDate and state=1 ");
|
param.addValue("cusId", cusId)
|
.addValue("summaryType", summaryType)
|
.addValue("summaryDate", summaryDate);
|
return baseDao.queryForMap(sql.toString(),param);
|
}
|
|
@Override
|
public Map getSatisConverRateSplineChart(String selDate, String statisType,String cusId) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.addValue("selDate", selDate)
|
.addValue("cusId", cusId);
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select truncate(avg(d.fg_rate),2) rate,i.month from totle_satis_info i,totle_satis_detail d where i.id = d.bus_id ");
|
sql.append(" and left(i.month,4)=:selDate and i.customer_id=:cusId group by i.month ");
|
List<Map> dataList= baseDao.queryForList(sql.toString(), param);
|
if(dataList!=null&&dataList.size()>0){
|
Map dataSetMap = new HashMap();
|
for(Map data:dataList){
|
dataSetMap.put(data.get("month").toString(), data);
|
}
|
List<String> categories = new ArrayList<String>();
|
List<Map> series = new ArrayList<Map>();
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
tempMap.put("name", "覆盖率");
|
for(int i=1;i<=12;i++){
|
categories.add(i+"月");
|
String key = selDate;
|
if(i<10){
|
key = key + "0" + i;
|
}else{
|
key = key + i;
|
}
|
if(dataSetMap.get(key)!=null){
|
tempList.add(((Map)dataSetMap.get(key)).get("rate"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
chartMap.put("categories", categories);
|
chartMap.put("series", series);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public List<String> getCusYearToCur(String cusId) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId);
|
String sql = "select year(CREATE_TIME) from SC_PARTNER_CUSTOMER_INFO where id = :cusId";
|
Integer startYear = baseDao.queryForInteger(sql,param);
|
Calendar cal = Calendar.getInstance();
|
Integer endYear = cal.get(Calendar.YEAR);
|
List<String> years = new ArrayList<String>();
|
for(int i=endYear;i>=startYear;i--){
|
years.add(String.valueOf(i));
|
}
|
return years;
|
}
|
|
private String getLastQuarter(String selDate){
|
Integer year = Integer.valueOf(selDate.substring(0, 4));
|
Integer quarter = Integer.valueOf(selDate.substring(4));
|
if(quarter==1){
|
year = year - 1;
|
quarter = 4;
|
}else{
|
quarter = quarter - 1;
|
}
|
return year.toString() + quarter.toString();
|
}
|
|
@Override
|
public boolean getEditFlag(String cusId, String userId) {
|
String sql = " select count(id) from sc_partner_customer_info where id = :cusId and ywjl_id=:userId ";
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId);
|
param.addValue("userId", userId);
|
Integer num = baseDao.queryForInteger(sql,param);
|
if(num!=null&&num>0){
|
return true;
|
}
|
return false;
|
}
|
|
@Override
|
public String getNRecordDate(Map<String, String> params) {
|
String col = "";
|
if(params.get("type").equals("week")){
|
col = "week(createtime,5)";
|
}else if(params.get("type").equals("month")){
|
col = "date_format(createtime,'%Y%m')";
|
}else if(params.get("type").equals("quarter")){
|
col = "quarter(createtime)";
|
}else if(params.get("type").equals("year")){
|
col = "year(createtime)";
|
}
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select GROUP_CONCAT(d) from ( ");
|
sql.append(" select $col d from workflow_base where businesstype in (8,9,10) and customer_id=:cusId ");
|
if(!params.get("type").equals("year")){
|
sql.append(" and year(createtime)=:year ");
|
}
|
sql.append(" group by $col ");
|
sql.append(" ) t ");
|
String rsql = sql.toString().replace("$col", col);
|
return ConvertUtil.obj2StrBlank(baseDao.queryForString(rsql,params));
|
}
|
}
|
