package cn.ksource.web.facade.tj;
|
|
import java.text.DecimalFormat;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.LinkedList;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
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.NumberUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.web.Constants;
|
import cn.ksource.web.service.DataDictionaryService;
|
import cn.ksource.web.util.ChartUtil;
|
|
@Service
|
public class LocalStatisFacadeImpl implements LocalStatisFacade {
|
|
@Autowired
|
private BaseDao baseDao;
|
|
@Autowired
|
private DataDictionaryService dataDictionaryService;
|
|
|
//获取查询Map
|
private Map getQueryMap(HttpServletRequest request){
|
String queryType = request.getParameter("queryType");
|
|
Map queryMap = new HashMap();
|
if(queryType.equals("1")){//按事件类型查询
|
queryMap.put("col","TYPE_ID");
|
queryMap.put("queryType",queryType);
|
queryMap.put("dicCategoryKey",Constants.INCIDENT_TYPE);
|
}else if(queryType.equals("2")){//按事件来源
|
queryMap.put("col","SOURCE_ID");
|
queryMap.put("queryType",queryType);
|
queryMap.put("dicCategoryKey",Constants.INCIDENT_SOURCE);
|
}else if(queryType.equals("3")){//关闭原因
|
queryMap.put("col","RESOLVE_TYPE_ID");
|
queryMap.put("queryType",queryType);
|
queryMap.put("dicCategoryKey",Constants.CLOSE_REASON);
|
}else if(queryType.equals("4")){//优先级
|
queryMap.put("col","PRIORITY_ID");
|
queryMap.put("queryType",queryType);
|
queryMap.put("dicCategoryKey",Constants.INCIDENT_EVENT_PRI);
|
}else if(queryType.equals("5")){//影响度
|
queryMap.put("col","INFLUENCE_ID");
|
queryMap.put("queryType",queryType);
|
queryMap.put("dicCategoryKey",Constants.EVENT_EFFECT_DG);
|
}else if(queryType.equals("6")){//服务级别
|
queryMap.put("col","SLA_ID");
|
queryMap.put("queryType",queryType);
|
}else if(queryType.equals("7")){//响应超时
|
queryMap.put("col","ANSWER_TIMEOUT");
|
queryMap.put("queryType",queryType);
|
}else if(queryType.equals("8")){//处理超时
|
queryMap.put("col","DEAL_TIMEOUT");
|
queryMap.put("queryType",queryType);
|
}
|
|
return queryMap;
|
}
|
|
//获取初始化list
|
private List getQueryTypeList(Map queryMap){
|
String queryType = queryMap.get("queryType").toString();
|
List queryTypeList = new ArrayList<Map>();
|
if(queryType.equals("1")||queryType.equals("2")||queryType.equals("3")||queryType.equals("4")
|
||queryType.equals("5")){
|
queryTypeList = dataDictionaryService.getDataDictionaryByCategoryKey(queryMap.get("dicCategoryKey").toString());
|
}else if(queryType.equals("6")){
|
String sql = " select id DATAKEY,level_name DATAVALUE from SC_SLA where STATE=1 order by serial asc ";
|
queryTypeList = baseDao.queryForList(sql,queryMap);
|
}else if(queryType.equals("7")){
|
String sql = " select 1 DATAKEY,'响应超时' DATAVALUE union select 2 DATAKEY,'响应及时' DATAVALUE ";
|
queryTypeList = baseDao.queryForList(sql);
|
}else if(queryType.equals("8")){
|
String sql = " select 1 DATAKEY,'处理超时' DATAVALUE union select 2 DATAKEY,'处理及时' DATAVALUE ";
|
queryTypeList = baseDao.queryForList(sql);
|
}
|
return queryTypeList;
|
}
|
|
@Override
|
public List localTypePie(HttpServletRequest request) {
|
|
String cusId = request.getParameter("cusId");
|
String type = request.getParameter("type");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
if(!StringUtil.notEmptyNum(type)) {
|
type = "2";
|
}
|
if(type.equals("1")) {
|
beginTime = beginTime.replace("-", "")+"01";
|
endTime = endTime.replace("-", "")+"31";
|
} else {
|
beginTime = beginTime.replace("-", "");
|
endTime = endTime.replace("-", "");
|
}
|
//获取初始化LIST
|
Map queryMap = getQueryMap(request);
|
List<Map> list = getQueryTypeList(queryMap);
|
List<Map> dataList = getQueryList_Pie(request,queryMap);
|
|
List listResult = new ArrayList();
|
if(list!=null&&list.size() > 0){
|
for(Map resMap:list){
|
List pieDate = new ArrayList();
|
resMap.put("num", 0);
|
if(dataList!=null&&dataList.size()>0){
|
for(Map tempMap:dataList){
|
if(tempMap.get("COLKEY").toString().equals(resMap.get("DATAKEY").toString())){
|
resMap.put("num", tempMap.get("NUM"));
|
break;
|
}
|
}
|
}
|
pieDate.add(resMap.get("DATAVALUE"));
|
pieDate.add(resMap.get("num"));
|
listResult.add(pieDate);
|
}
|
}
|
System.out.println("事件类型---------------------->"+JsonUtil.list2Json(listResult));
|
return listResult;
|
}
|
|
@Override
|
public Map localTypeLine(HttpServletRequest request) {
|
|
String type = request.getParameter("type");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
|
if(!StringUtil.notEmptyNum(type)) {
|
type = "2";
|
}
|
List<String> categories = new ArrayList<String>();//获取X轴数据
|
if(type.equals("1")) {
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
|
} else {
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
|
}
|
|
|
Map queryMap = getQueryMap(request);
|
List<Map> types = getQueryTypeList(queryMap);
|
List<Map> typeMsg = getQueryList_Line(request,queryMap);
|
|
Map cacheMap = new HashMap();
|
if(null!=typeMsg && typeMsg.size()>0) {
|
for(Map msg : typeMsg) {
|
String typeId = ConvertUtil.obj2StrBlank(msg.get("COLKEY"));
|
int num = ConvertUtil.obj2Int(msg.get("NUM"));
|
String time = ConvertUtil.obj2StrBlank(msg.get("DC_TIME"));
|
String key = time + "-" + typeId;
|
cacheMap.put(key, num);
|
}
|
}
|
|
|
List resultList = new ArrayList();
|
|
if(null!=types && types.size()>0) {
|
for(Map t : types) {
|
Map newMap = new HashMap();
|
String key = ConvertUtil.obj2StrBlank(t.get("DATAKEY"));
|
String name = ConvertUtil.obj2StrBlank(t.get("DATAVALUE"));
|
|
newMap.put("name", name);
|
List list = new ArrayList();
|
for(String cate : categories) {
|
String unitKey = cate + "-" + key;
|
if(cacheMap.containsKey(unitKey)) {
|
list.add(cacheMap.get(unitKey));
|
} else {
|
list.add(0);
|
}
|
}
|
newMap.put("data", list);
|
resultList.add(newMap);
|
}
|
}
|
|
System.out.println(JsonUtil.list2Json(resultList));
|
Map resultMap = new HashMap();
|
resultMap.put("cate", categories);
|
resultMap.put("data", resultList);
|
|
System.out.println("line-------------->"+JsonUtil.map2Json(resultMap));
|
return resultMap;
|
}
|
|
|
|
|
//获取饼状图查询结果
|
private List getQueryList_Pie(HttpServletRequest request,Map queryMap){
|
|
String cusId = request.getParameter("cusId");
|
String type = request.getParameter("type");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String queryType = request.getParameter("queryType");
|
List<Map> queryList = new ArrayList<Map>();
|
Map paramMap = new HashMap();
|
StringBuilder buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE state!=5 ");
|
if(queryType.equals("3")){
|
buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
|
}
|
if(StringUtil.notEmpty(cusId)) {
|
buffer.append(" AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
}
|
if(StringUtil.notEmpty(type)) {
|
buffer.append(" AND C.CREATE_TIME >= :beginTime AND C.CREATE_TIME <= :endTime ");
|
|
if(type.equals("1")) {
|
paramMap.put("beginTime", beginTime+"01000000");
|
paramMap.put("endTime", endTime+"31240000");
|
} else {
|
paramMap.put("beginTime", beginTime+"000000");
|
paramMap.put("endTime", endTime+"240000");
|
}
|
|
}
|
buffer.append(" and $KEY is not null ");
|
buffer.append(" GROUP BY $KEY ");
|
|
String sql = buffer.toString().replace("$KEY", queryMap.get("col").toString());
|
List<Map> dataList = baseDao.queryForList(sql, paramMap);
|
return dataList;
|
}
|
|
//获取线形图查询结果
|
private List getQueryList_Line(HttpServletRequest request,Map queryMap){
|
|
String cusId = request.getParameter("cusId");
|
String type = request.getParameter("type");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String queryType = request.getParameter("queryType");
|
Map paramMap = new HashMap();
|
StringBuilder builder = new StringBuilder("");
|
if(type.equals("2")) {
|
builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,to_char(C.CREATE_TIME,'yyyy-MM-dd') AS DC_TIME FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
|
} else {
|
builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,to_char(C.CREATE_TIME,'yyyy-MM') AS DC_TIME FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
|
}
|
if(!queryType.equals("3")){
|
builder.append(" and state!=5 ");
|
}
|
if(StringUtil.notEmpty(cusId)) {
|
builder.append(" AND C.CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
}
|
if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)) {
|
beginTime = beginTime.replaceAll("-", "");
|
endTime = endTime.replaceAll("-", "");
|
builder.append(" AND C.CREATE_TIME>=:beginTime AND C.CREATE_TIME <=:endTime ");
|
if(type.equals("1")) {
|
paramMap.put("beginTime", beginTime+"01000000");
|
paramMap.put("endTime", endTime+"31240000");
|
} else {
|
paramMap.put("beginTime", beginTime+"000000");
|
paramMap.put("endTime", endTime+"240000");
|
}
|
}
|
builder.append(" and $KEY is not null ");
|
if(type.equals("2")) {
|
builder.append(" GROUP BY $KEY,to_char(C.CREATE_TIME,'yyyy-MM-dd') ");
|
}else{
|
builder.append(" GROUP BY $KEY,to_char(C.CREATE_TIME,'yyyy-MM') ");
|
}
|
|
String sql = builder.toString().replace("$KEY", queryMap.get("col").toString());
|
return baseDao.queryForList(sql,paramMap);
|
}
|
|
@Override
|
public Map localTimeLine(HttpServletRequest request){
|
String selDay = request.getParameter("selDay");
|
|
String cusId = request.getParameter("cusId");
|
|
StringBuilder sql = new StringBuilder();
|
Map paramMap = new HashMap();
|
|
sql.append(" SELECT to_number(substr(HAPPEN_TIME||'',9,2)) MHOUR,count(ID) NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C");
|
sql.append(" where c.state!=5 and to_number(substr(HAPPEN_TIME||'',1,8))=:selDay and to_number(substr(HAPPEN_TIME||'',9,2))>=7 and to_number(substr(HAPPEN_TIME||'',9,2))<=18 ");
|
if(StringUtil.notEmpty(cusId) ) {
|
sql.append(" AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
}
|
sql.append(" GROUP BY to_number(substr(HAPPEN_TIME||'',9,2)) ");
|
|
paramMap.put("selDay", selDay);
|
List<Map> list = baseDao.queryForList(sql.toString(),paramMap);
|
Map categoriesMap = new HashMap();
|
categoriesMap.put("7:00-9:00", 0);
|
categoriesMap.put("9:00-11:00", 0);
|
categoriesMap.put("11:00-13:00", 0);
|
categoriesMap.put("13:00-15:00", 0);
|
categoriesMap.put("15:00-17:00", 0);
|
categoriesMap.put("17:00-18:00", 0);
|
//x轴
|
List<String> categoriesList = new ArrayList<String>();
|
categoriesList.add("7:00-9:00");
|
categoriesList.add("9:00-11:00");
|
categoriesList.add("11:00-13:00");
|
categoriesList.add("13:00-15:00");
|
categoriesList.add("15:00-17:00");
|
categoriesList.add("17:00-18:00");
|
|
|
if(list!=null&&list.size()>0){
|
for(Map map:list){
|
String hour = map.get("MHOUR").toString();
|
if(hour.equals("7")||hour.equals("8")){
|
Integer sum = Integer.valueOf(categoriesMap.get("7:00-9:00").toString())+Integer.valueOf(map.get("NUM").toString());
|
categoriesMap.put("7:00-9:00", sum);
|
}else if(hour.equals("9")||hour.equals("10")){
|
Integer sum = Integer.valueOf(categoriesMap.get("9:00-11:00").toString())+Integer.valueOf(map.get("NUM").toString());
|
categoriesMap.put("9:00-11:00", sum);
|
}else if(hour.equals("11")||hour.equals("12")){
|
Integer sum = Integer.valueOf(categoriesMap.get("11:00-13:00").toString())+Integer.valueOf(map.get("NUM").toString());
|
categoriesMap.put("11:00-13:00", sum);
|
}else if(hour.equals("13")||hour.equals("14")){
|
Integer sum = Integer.valueOf(categoriesMap.get("13:00-15:00").toString())+Integer.valueOf(map.get("NUM").toString());
|
categoriesMap.put("13:00-15:00", sum);
|
}else if(hour.equals("15")||hour.equals("16")){
|
Integer sum = Integer.valueOf(categoriesMap.get("15:00-17:00").toString())+Integer.valueOf(map.get("NUM").toString());
|
categoriesMap.put("15:00-17:00", sum);
|
}else if(hour.equals("17")||hour.equals("18")){
|
Integer sum = Integer.valueOf(categoriesMap.get("17:00-18:00").toString())+Integer.valueOf(map.get("NUM").toString());
|
categoriesMap.put("17:00-18:00", sum);
|
}
|
}
|
}
|
List dataList = new ArrayList();
|
dataList.add(categoriesMap.get("7:00-9:00"));
|
dataList.add(categoriesMap.get("9:00-11:00"));
|
dataList.add(categoriesMap.get("11:00-13:00"));
|
dataList.add(categoriesMap.get("13:00-15:00"));
|
dataList.add(categoriesMap.get("15:00-17:00"));
|
dataList.add(categoriesMap.get("17:00-18:00"));
|
List seriesList = new ArrayList();
|
Map seriesMap = new HashMap();
|
seriesMap.put("name","数量");
|
seriesMap.put("data",dataList);
|
seriesList.add(seriesMap);
|
Map resMap = new HashMap();
|
resMap.put("categories", categoriesList);
|
resMap.put("series", seriesList);
|
|
System.out.println("发生时间统计-------------->"+JsonUtil.map2Json(resMap));
|
return resMap;
|
}
|
|
|
|
@Override
|
public Map localTotalChart(String cusId,String cusName,String beginTime,String endTime) {
|
|
beginTime = beginTime + "000000";
|
endTime = endTime + "666666";
|
|
List<Map> typeList = getDicList(Constants.INCIDENT_TYPE);
|
List<Map> sourceList = getDicList(Constants.INCIDENT_SOURCE);
|
List<Map> closeList = getDicList(Constants.CLOSE_REASON);
|
List<Map> priList = getDicList(Constants.INCIDENT_EVENT_PRI);
|
List<Map> effectList = getDicList(Constants.EVENT_EFFECT_DG);
|
List<Map> levelList = baseDao.queryForList(" select LEVEL_NAME labelName from SC_SLA where state=1 order by SERIAL asc ");
|
List<Map> timeoutList = baseDao.queryForList(" select '超时' labelName union select '正常' labelName ");
|
List<Map> resolveList = getDicList(Constants.RESOLVE_WAY);
|
List<Map> stateList = baseDao.queryForList(" select '待响应' labelName union select '处理中' labelName union select '已完成' labelName ");
|
String blankKey = "";
|
String blankSql = "";
|
String tempSql = "";
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select mtotal.total_num,mtotal.mdate,mtype.type_num,msource.source_num,mclose.close_num,mpri.pri_num,meffect.effect_num, ");
|
sql.append(" mlevel.level_num,manswer.answer_num,mdeal.deal_num,mresolve.resolve_num,mstate.state_num ");
|
sql.append(" from ");
|
sql.append(" ( ");
|
sql.append(" select count(ID) total_num,substr(HAPPEN_TIME||'',1,8) mdate from SC_WORKFLOW_INCIDENT_LOCAL ");
|
sql.append(" where HAPPEN_TIME>=:beginTime and HAPPEN_TIME<=:endTime ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append("and CUSTOMER_ID=:cusId ");
|
}
|
sql.append(" group by substr(HAPPEN_TIME||'',1,8) ");
|
|
sql.append(" ) mtotal ");
|
//事件类型
|
sql = getPartSql(sql,"type_num",Constants.INCIDENT_TYPE,blankSql,"TYPE_ID","mtype","1",cusId);
|
//事件来源
|
sql = getPartSql(sql,"source_num",Constants.INCIDENT_SOURCE,blankSql,"SOURCE_ID","msource","1",cusId);
|
//关闭原因
|
sql = getPartSql(sql,"close_num",Constants.CLOSE_REASON,blankSql,"RESOLVE_TYPE_ID","mclose","1",cusId);
|
//优先级
|
sql = getPartSql(sql,"pri_num",Constants.INCIDENT_EVENT_PRI,blankSql,"PRIORITY_ID","mpri","1",cusId);
|
//影响度
|
sql = getPartSql(sql,"effect_num",Constants.EVENT_EFFECT_DG,blankSql,"INFLUENCE_ID","meffect","1",cusId);
|
//服务级别
|
tempSql = " select id DATAKEY,SERIAL ordernum from SC_SLA where STATE=1 ";
|
sql = getPartSql(sql,"level_num",blankKey,tempSql,"SLA_ID","mlevel","1",cusId);
|
//响应时间
|
tempSql = " select 1 DATAKEY,1 ordernum union select 2 DATAKEY,2 ordernum ";
|
sql = getPartSql(sql,"answer_num",blankKey,tempSql,"ANSWER_TIMEOUT","manswer","1",cusId);
|
//解决时间
|
sql = getPartSql(sql,"deal_num",blankKey,tempSql,"DEAL_TIMEOUT","mdeal","1",cusId);
|
//解决途径
|
sql = getPartSql(sql,"resolve_num",Constants.RESOLVE_WAY,blankSql,"RESOLVE_TYPE_ID","mresolve","1",cusId);
|
//事件状态
|
tempSql = " select 1 DATAKEY,1 ordernum union select 2 DATAKEY,2 ordernum union select 3 DATAKEY,3 ordernum ";
|
sql = getPartSql(sql,"state_num",blankKey,tempSql,"FLOWSTATE","mstate","2",cusId);
|
|
Map param = new HashMap();
|
param.put("cusId", cusId);
|
param.put("beginTime", beginTime);
|
param.put("endTime", endTime);
|
List<Map> resList = baseDao.queryForList(sql.toString(),param);
|
Map resMap = new HashMap();
|
resMap.put("typeList", typeList);
|
resMap.put("sourceList", sourceList);
|
resMap.put("closeList", closeList);
|
resMap.put("priList", priList);
|
resMap.put("effectList", effectList);
|
resMap.put("levelList", levelList);
|
resMap.put("timeoutList", timeoutList);
|
resMap.put("resolveList", resolveList);
|
resMap.put("stateList", stateList);
|
resMap.put("resList", resList);
|
resMap.put("cusName", cusName);
|
|
return resMap;
|
}
|
|
/**
|
* 获取组装sql
|
* @param sql
|
* @param categoryKey 数据字典分类key
|
* @param tempSql 临时sql
|
* @param column 列名称
|
* @param tempTableName 临时表名称
|
* @param flag 标示
|
* @return 组装后sql
|
*/
|
private StringBuilder getPartSql(StringBuilder sql,String numName,String categoryKey,String tempSql,
|
String column,String tempTableName,String flag,String cusId){
|
sql.append(" left join ");
|
sql.append(" ( ");
|
sql.append(" select mdate,WM_CONCAT(num) "+numName+" from ");
|
sql.append(" ( ");
|
sql.append(" select count(i.ID) num,mdate,datakey,ordernum from ");
|
sql.append(" ( ");
|
sql.append(" select DATAKEY,mdate,ordernum from ");
|
sql.append(" (select substr(HAPPEN_TIME||'',1,8) mdate from SC_WORKFLOW_INCIDENT_LOCAL group by substr(HAPPEN_TIME||'',1,8) ) d,");
|
sql.append(" ( ");
|
if(StringUtil.isNotBlank(categoryKey)){
|
sql = getDicSql(sql,categoryKey);
|
}else{
|
sql.append(tempSql);
|
}
|
sql.append(" ) c ");
|
sql.append(" ) b ");
|
sql.append(" left join ");
|
if(flag.equals("1")){
|
sql.append("( select * from SC_WORKFLOW_INCIDENT_LOCAL ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" where CUSTOMER_ID = :cusId ");
|
}
|
sql.append(" ) ");
|
}else{
|
sql = getStateSql(sql,cusId);
|
}
|
sql.append(" i on i."+column+" = b.DATAKEY and substr(HAPPEN_TIME||'',1,8)=b.mdate ");
|
sql.append(" group by b.mdate,b.datakey,b.ordernum ");
|
sql.append(" ) t group by mdate ");
|
sql.append(" ) "+tempTableName);
|
sql.append(" on "+tempTableName+".mdate = mtotal.mdate ");
|
return sql;
|
}
|
|
/**
|
* 获取数据字典查询sql
|
* @param sql
|
* @param categoryKey
|
* @return
|
*/
|
private StringBuilder getDicSql(StringBuilder sql,String categoryKey){
|
sql.append(" select d.DATAKEY,d.ordernum from CONFIG_DATA_DICTIONARY d ");
|
sql.append(" inner join CONFIG_DATA_DICTIONARY_CATEGORY cate ");
|
sql.append(" on d.CATEGORYID = cate.ID ");
|
sql.append(" where cate.CATEGORYKEY='"+categoryKey+"'");
|
return sql;
|
}
|
|
/**
|
* 获取事件状态sql
|
* @param sql
|
* @return
|
*/
|
private StringBuilder getStateSql(StringBuilder sql,String cusId){
|
sql.append(" (");
|
sql.append(" select c.CUSTOMER_ID,c.id,n.FLOWSTATE,c.HAPPEN_TIME from SC_WORKFLOW_INCIDENT_LOCAL c ");
|
sql.append(" inner join workflow_base b on c.flow_id = b.id and b.wfstate<>3 ");
|
sql.append(" inner JOIN workflow_node n on b.CURRENT_NODE_ID = n.id ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" where c.CUSTOMER_ID = :cusId ");
|
}
|
sql.append(" )");
|
return sql;
|
}
|
|
|
private List<Map> getDicList(String categoryKey){
|
String sql = " select d.DATAVALUE labelName from CONFIG_DATA_DICTIONARY d " +
|
" inner join CONFIG_DATA_DICTIONARY_CATEGORY c on " +
|
" d.CATEGORYID = c.ID where c.CATEGORYKEY=:categoryKey order by d.ordernum ASC";
|
Map param = new HashMap();
|
param.put("categoryKey",categoryKey);
|
return baseDao.queryForList(sql,param);
|
}
|
|
@Override
|
public List<Map> queryLocalIncidentStatis(String cusId,String subCustomerId,String beginTime,String endTime,String wfstate,String jjrId) {
|
|
beginTime = beginTime + "000000";
|
endTime = endTime + "240000";
|
Map param= new HashMap();
|
param.put("cusId", cusId);
|
param.put("cusId", cusId);
|
param.put("beginTime", beginTime);
|
param.put("endTime", endTime);
|
param.put("subCustomerId", subCustomerId);
|
param.put("jjrId", jjrId);
|
StringBuilder sql=new StringBuilder();
|
sql.append(" SELECT A.*,B.WFSTATE FROM SC_WORKFLOW_INCIDENT_LOCAL A,workflow_base B WHERE 1=1 ");
|
if(StringUtil.notEmpty(cusId)){
|
sql.append(" AND A.CUSTOMER_ID=:cusId ");
|
}
|
sql.append(" AND A.ID=B.BUSINESS_ID ");
|
if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)){
|
sql.append(" AND A.CREATE_TIME>:beginTime AND A.CREATE_TIME<:endTime");
|
}
|
if(StringUtil.notEmpty(subCustomerId)){
|
sql.append(" AND A.SUB_CUSTOMER_ID=:subCustomerId");
|
}
|
if(StringUtil.notEmpty(wfstate)){
|
sql.append(" AND B.WFSTATE=:wfstate");
|
param.put("wfstate", wfstate);
|
}
|
if(StringUtil.notEmpty(jjrId)){
|
sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
|
}
|
List localList=baseDao.queryForList(sql.toString(), param);
|
return localList;
|
}
|
@Override
|
public List<Map> queryLocalIncidentStatisTwo(String cusId,String subCustomerId,String beginTime,String endTime,String wfstate,String jjrId,String type_id) {
|
|
beginTime = beginTime + "000000";
|
endTime = endTime + "240000";
|
Map param= new HashMap();
|
param.put("cusId", cusId);
|
param.put("cusId", cusId);
|
param.put("beginTime", beginTime);
|
param.put("endTime", endTime);
|
param.put("subCustomerId", subCustomerId);
|
param.put("jjrId", jjrId);
|
param.put("type_id", type_id);
|
StringBuilder sql=new StringBuilder();
|
sql.append(" SELECT A.*,B.WFSTATE FROM SC_WORKFLOW_INCIDENT_LOCAL A,workflow_base B WHERE 1=1 ");
|
if(StringUtil.notEmpty(cusId)){
|
sql.append(" AND A.CUSTOMER_ID=:cusId ");
|
}
|
sql.append(" AND A.ID=B.BUSINESS_ID ");
|
if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)){
|
sql.append(" AND A.CREATE_TIME>:beginTime AND A.CREATE_TIME<:endTime");
|
}
|
if(StringUtil.notEmpty(subCustomerId)){
|
sql.append(" AND A.SUB_CUSTOMER_ID=:subCustomerId");
|
}
|
if(StringUtil.notEmpty(type_id)){
|
sql.append(" AND A.TYPE_ID=:type_id");
|
}
|
if(StringUtil.notEmpty(wfstate)){
|
sql.append(" AND B.WFSTATE=:wfstate");
|
param.put("wfstate", wfstate);
|
}
|
if(StringUtil.notEmpty(jjrId)){
|
sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
|
}
|
List localList=baseDao.queryForList(sql.toString(), param);
|
return localList;
|
}
|
|
////////////////////////////////////////////////////////////////////////////////////////////////////
|
/**
|
* 将数据字典转化成DataKeyMap
|
* @author chenlong
|
* @param dicList
|
* @return
|
*/
|
private Map dicListToDataKeyMap(List<Map> dicList){
|
Map dataKeyMap = new HashMap();
|
for(Map dic:dicList){
|
dataKeyMap.put(ConvertUtil.obj2StrBlank(dic.get("DATAKEY")),ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
|
}
|
return dataKeyMap;
|
}
|
|
/**
|
* 格式化数据字典
|
* @author chenlong
|
* @param dicList
|
*/
|
private void formatDicList(List<Map> dicList){
|
for(Map dic:dicList){
|
dic.put("key", ConvertUtil.obj2StrBlank(dic.get("DATAKEY")));
|
dic.put("val", ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
|
}
|
}
|
|
/**
|
* 组织时间查询条件
|
* @author chenlong
|
* @param frequency
|
* @param sql
|
* @param colName
|
*/
|
private void getDateWhereSql(String frequency,StringBuilder sql,String colName){
|
if(frequency.equals("day")||frequency==null){
|
sql.append(" and date_format("+colName+",'%Y%m%d')>=:startDate and date_format("+colName+",'%Y%m%d')<=:endDate ");
|
}else if(frequency.equals("month")){
|
sql.append(" and date_format("+colName+",'%Y%m')>=:startDate and date_format("+colName+",'%Y%m')<=:endDate ");
|
}
|
}
|
|
@Override
|
public Map localTypePie(String frequency,String cusId,String startDate,String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.INCIDENT_TYPE);
|
formatDicList(categoryList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,type_id category from sc_workflow_incident_local where 1=1 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by type_id ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
|
}
|
|
@Override
|
public Map localSourcePie(String frequency, String cusId,String startDate, String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.INCIDENT_SOURCE);
|
formatDicList(categoryList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,source_id category from sc_workflow_incident_local where 1=1 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by source_id ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
|
}
|
|
@Override
|
public Map localCloseCol(String frequency, String cusId,String startDate, String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List dicList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.CLOSE_REASON);
|
Map dataKeyMap = dicListToDataKeyMap(dicList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,resolve_type_id datakey,1 as category from sc_workflow_incident_local where resolve_type_id is not null ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by resolve_type_id ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
|
Map categoryMap = new HashMap();
|
categoryMap.put("key", "1");
|
categoryMap.put("val", "数量");
|
List<Map> categoryList = new ArrayList<Map>();
|
categoryList.add(categoryMap);
|
return ChartUtil.createHchartLineMap(datas,categoryList,dataKeyMap,0.0);
|
}
|
|
@Override
|
public Map localPriPie(String frequency, String cusId, String startDate,String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.INCIDENT_EVENT_PRI);
|
formatDicList(categoryList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,priority_id category from sc_workflow_incident_local where 1=1 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by priority_id ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
|
}
|
|
@Override
|
public Map localInfluenceCol(String frequency, String cusId,String startDate, String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List dicList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.EVENT_EFFECT_DG);
|
Map dataKeyMap = dicListToDataKeyMap(dicList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,influence_id datakey,1 as category from sc_workflow_incident_local where 1=1 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by influence_id ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
|
Map categoryMap = new HashMap();
|
categoryMap.put("key", "1");
|
categoryMap.put("val", "数量");
|
List<Map> categoryList = new ArrayList<Map>();
|
categoryList.add(categoryMap);
|
return ChartUtil.createHchartLineMap(datas,categoryList,dataKeyMap,0.0);
|
}
|
|
|
@Override
|
public Map localLvPie(String frequency, String cusId, String startDate,String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select id as 'key',level_name as val from sc_sla where state=1 order by serial asc");
|
List<Map> categoryList = baseDao.queryForList(sql.toString(),param);
|
|
sql.setLength(0);
|
sql.append(" select count(id) val,sla_id category from sc_workflow_incident_local where 1=1 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by sla_id ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
|
}
|
|
@Override
|
public Map localResponsePie(String frequency, String cusId,String startDate, String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List<Map> categoryList = new ArrayList<Map>();
|
for(int i=1;i<=2;i++){
|
Map category = new HashMap();
|
category.put("key", String.valueOf(i));
|
if(i==1){
|
category.put("val", "响应超时");
|
}else{
|
category.put("val", "响应及时");
|
}
|
categoryList.add(category);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,answer_timeout category from sc_workflow_incident_local where answer_timeout is not null ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by answer_timeout ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
|
}
|
|
@Override
|
public Map localDealPie(String frequency, String cusId,String startDate, String endDate) {
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List<Map> categoryList = new ArrayList<Map>();
|
for(int i=1;i<=2;i++){
|
Map category = new HashMap();
|
category.put("key", String.valueOf(i));
|
if(i==1){
|
category.put("val", "处理超时");
|
}else{
|
category.put("val", "处理及时");
|
}
|
categoryList.add(category);
|
}
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,deal_timeout category from sc_workflow_incident_local where deal_timeout is not null ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by deal_timeout ");
|
List datas = baseDao.queryForList(sql.toString(),param);
|
return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
|
}
|
|
@Override
|
public Map localHappendTimeLine(String frequency, String cusId,String startDate, String endDate) {
|
Map chartMap = new HashMap();
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select hour(happen_time) mhour,count(id) num from sc_workflow_incident_local c");
|
sql.append(" where hour(happen_time)>=7 and hour(happen_time)<=18 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by mhour ");
|
|
List<Map> list = baseDao.queryForList(sql.toString(),param);
|
Map categoriesMap = new HashMap();
|
categoriesMap.put("7:00-9:00", 0);
|
categoriesMap.put("9:00-11:00", 0);
|
categoriesMap.put("11:00-13:00", 0);
|
categoriesMap.put("13:00-15:00", 0);
|
categoriesMap.put("15:00-17:00", 0);
|
categoriesMap.put("17:00-18:00", 0);
|
//x轴
|
List<String> categoriesList = new ArrayList<String>();
|
categoriesList.add("7:00-9:00");
|
categoriesList.add("9:00-11:00");
|
categoriesList.add("11:00-13:00");
|
categoriesList.add("13:00-15:00");
|
categoriesList.add("15:00-17:00");
|
categoriesList.add("17:00-18:00");
|
|
if(list!=null&&list.size()>0){
|
for(Map map:list){
|
String hour = map.get("mhour").toString();
|
if(hour.equals("7")||hour.equals("8")){
|
Integer sum = Integer.valueOf(categoriesMap.get("7:00-9:00").toString())+Integer.valueOf(map.get("num").toString());
|
categoriesMap.put("7:00-9:00", sum);
|
}else if(hour.equals("9")||hour.equals("10")){
|
Integer sum = Integer.valueOf(categoriesMap.get("9:00-11:00").toString())+Integer.valueOf(map.get("num").toString());
|
categoriesMap.put("9:00-11:00", sum);
|
}else if(hour.equals("11")||hour.equals("12")){
|
Integer sum = Integer.valueOf(categoriesMap.get("11:00-13:00").toString())+Integer.valueOf(map.get("num").toString());
|
categoriesMap.put("11:00-13:00", sum);
|
}else if(hour.equals("13")||hour.equals("14")){
|
Integer sum = Integer.valueOf(categoriesMap.get("13:00-15:00").toString())+Integer.valueOf(map.get("num").toString());
|
categoriesMap.put("13:00-15:00", sum);
|
}else if(hour.equals("15")||hour.equals("16")){
|
Integer sum = Integer.valueOf(categoriesMap.get("15:00-17:00").toString())+Integer.valueOf(map.get("num").toString());
|
categoriesMap.put("15:00-17:00", sum);
|
}else if(hour.equals("17")||hour.equals("18")){
|
Integer sum = Integer.valueOf(categoriesMap.get("17:00-18:00").toString())+Integer.valueOf(map.get("num").toString());
|
categoriesMap.put("17:00-18:00", sum);
|
}
|
}
|
List dataList = new ArrayList();
|
dataList.add(categoriesMap.get("7:00-9:00"));
|
dataList.add(categoriesMap.get("9:00-11:00"));
|
dataList.add(categoriesMap.get("11:00-13:00"));
|
dataList.add(categoriesMap.get("13:00-15:00"));
|
dataList.add(categoriesMap.get("15:00-17:00"));
|
dataList.add(categoriesMap.get("17:00-18:00"));
|
List seriesList = new ArrayList();
|
Map seriesMap = new HashMap();
|
seriesMap.put("name","数量");
|
seriesMap.put("data",dataList);
|
seriesList.add(seriesMap);
|
chartMap.put("categories", categoriesList);
|
chartMap.put("series", seriesList);
|
chartMap.put("nodata", false);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
@Override
|
public List<Map> queryIncidentDclStatis(String cusId,String subCustomerId,String beginTime,String endTime) {
|
Map paramMap = new HashMap();
|
|
//通过项目编号，查询该项目的所有的服务目录
|
String sql = "SELECT COUNT(ID) AS NUM,THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME FROM SC_WORKFLOW_INCIDENT_LOCAL WHERE state!=10 and CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId AND CREATE_TIME > :beginTime AND CREATE_TIME <= :endTime ";
|
if(StringUtil.notEmpty(subCustomerId)) {
|
sql+= " AND SUB_CUSTOMER_ID = :subCustomerId ";
|
paramMap.put("subCustomerId", subCustomerId);
|
}
|
|
StringBuilder builder1 = new StringBuilder(sql);
|
StringBuilder builder2 = new StringBuilder(sql);
|
StringBuilder builder3 = new StringBuilder(sql);
|
StringBuilder builder4 = new StringBuilder(sql);
|
|
|
String endSql = " GROUP BY THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME ";
|
|
builder1.append(endSql);
|
|
builder2.append(" AND ANSWER_TIMEOUT = 1 ").append(endSql);
|
|
builder3.append(" AND DEAL_TIMEOUT = 1 ").append(endSql);
|
|
builder4.append(" AND ANSWER_TIMEOUT = 2 AND DEAL_TIMEOUT = 2 ").append(endSql);
|
|
|
|
paramMap.put("cusId", cusId);
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginTime", beginTime+"000000");
|
paramMap.put("endTime", endTime+"240000");
|
|
|
List<Map> list1 = baseDao.queryForList(builder1.toString(),paramMap);
|
|
List<Map> list2 = baseDao.queryForList(builder2.toString(),paramMap);
|
|
List<Map> list3 = baseDao.queryForList(builder3.toString(),paramMap);
|
|
List<Map> list4 = baseDao.queryForList(builder4.toString(),paramMap);
|
|
|
if(null!=list1 && list1.size()>0) {
|
Map map2 = new HashMap();
|
for(Map map : list2) {
|
map2.put(map.get("THIRD_CATEGORY_ID"), map);
|
}
|
|
Map map3 = new HashMap();
|
for(Map map : list3) {
|
map3.put(map.get("THIRD_CATEGORY_ID"), map);
|
}
|
|
Map map4 = new HashMap();
|
for(Map map : list4) {
|
map4.put(map.get("THIRD_CATEGORY_ID"), map);
|
}
|
|
for(Map map : list1) {
|
String thirdCategoryId = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
|
if(map2.containsKey(thirdCategoryId)) {
|
Map m = (Map)map2.get(thirdCategoryId);
|
int num = ConvertUtil.obj2Int(m.get("NUM"));
|
map.put("xy", num);
|
} else {
|
map.put("xy", 0);
|
}
|
|
if(map3.containsKey(thirdCategoryId)) {
|
Map m = (Map)map3.get(thirdCategoryId);
|
int num = ConvertUtil.obj2Int(m.get("NUM"));
|
System.out.println("num:"+num);
|
map.put("cl", num);
|
} else {
|
map.put("cl", 0);
|
}
|
|
double jj = 0;
|
|
if(map4.containsKey(thirdCategoryId)) {
|
Map m = (Map)map4.get(thirdCategoryId);
|
int num = ConvertUtil.obj2Int(m.get("NUM"));
|
|
jj = ConvertUtil.obj2Double(m.get("NUM"));
|
|
map.put("jj", num);
|
} else {
|
map.put("jj", 0);
|
}
|
|
|
double total = ConvertUtil.obj2Double(map.get("NUM"));
|
|
|
double jjl = NumberUtil.div(jj, total, 3);
|
|
//jjl = NumberUtil.mul(jjl, ConvertUtil.obj2Double(100));
|
|
map.put("jjl", jjl);
|
|
}
|
|
}
|
|
System.out.println(JsonUtil.list2Json(list1));
|
return list1;
|
}
|
@Override
|
public Map queryCloseReport(String cusId,String beginDay,String endDay) {
|
|
Map paramMap = new HashMap();
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginDay", beginDay+"000000");
|
paramMap.put("endDay", endDay+"666666");
|
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
Map extendMap = new HashMap();
|
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(close_num,0) close_num from ");
|
builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal left join ");
|
builder.append(" (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=3 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mclose ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID ");
|
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
|
|
//long startTime = System.currentTimeMillis();
|
//查询数据字典
|
List<Map> resolveList = dataDictionaryService.getDataDictionaryByCategoryKey("RESOLVE_WAY");
|
List<Map> closeList = dataDictionaryService.getDataDictionaryByCategoryKey("CLOSE_REASON");
|
//long endTime = System.currentTimeMillis();
|
//System.out.println("程序运行时间："+(endTime-startTime)+"ms");
|
//查询解决方式,关闭原因
|
builder.setLength(0);
|
builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',RESOLVE_TYPE_ID) m_key from SC_WORKFLOW_INCIDENT_LOCAL ");
|
builder.append(" where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by RESOLVE_TYPE_ID,THIRD_CATEGORY_ID ");
|
List<Map> extendList = baseDao.queryForList(builder.toString(),paramMap);
|
if(extendList!=null&&extendList.size()>0){
|
for(Map extend : extendList){
|
extendMap.put(extend.get("m_key"), extend.get("m_num"));
|
}
|
}
|
|
//组织表头
|
lebalList.add("服务目录");
|
lebalList.add("驻场事件数");
|
lebalList.add("已取消驻场事件");
|
for(Map map:resolveList){
|
lebalList.add(map.get("DATAVALUE").toString());
|
}
|
for(Map map:closeList){
|
lebalList.add(map.get("DATAVALUE").toString());
|
}
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("close_num")));
|
//组织解决方式数据
|
for(Map map:resolveList){
|
String key = base.get("THIRD_CATEGORY_ID")+"_"+map.get("DATAKEY").toString();
|
if(extendMap.get(key)!=null){
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get(key)));
|
}else{
|
tempList.add("0");
|
}
|
}
|
//组织关闭原因数据
|
for(Map map:closeList){
|
String key = base.get("THIRD_CATEGORY_ID")+"_"+map.get("DATAKEY").toString();
|
if(extendMap.get(key)!=null){
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get(key)));
|
}else{
|
tempList.add("0");
|
}
|
}
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
@Override
|
public Map queryTypeReport(String cusId,String beginDay,String endDay) {
|
|
Map paramMap = new HashMap();
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginDay", beginDay+"000000");
|
paramMap.put("endDay", endDay+"666666");
|
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
Map extendMap = new HashMap();
|
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num from ");
|
builder.append(" (select THIRD_CATEGORY_NAME,FIRST_CATEGORY_NAME,SECOND_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,FIRST_CATEGORY_NAME,SECOND_CATEGORY_NAME) mtotal ");
|
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
|
|
//查询数据字典
|
List<Map> typeList = dataDictionaryService.getDataDictionaryByCategoryKey("INCIDENT_TYPE");
|
//查询解决方式,关闭原因
|
builder.setLength(0);
|
builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID) m_key,THIRD_CATEGORY_NAME,THIRD_CATEGORY_ID,TYPE_ID from SC_WORKFLOW_INCIDENT_LOCAL ");
|
builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by TYPE_ID,THIRD_CATEGORY_ID,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID),THIRD_CATEGORY_NAME ");
|
List<Map> extendList = baseDao.queryForList(builder.toString(),paramMap);
|
if(extendList!=null&&extendList.size()>0){
|
for(Map extend : extendList){
|
extendMap.put(extend.get("m_key"), extend.get("m_num"));
|
}
|
}
|
|
//组织表头
|
lebalList.add("服务目录");
|
for(Map map:typeList){
|
lebalList.add(map.get("DATAVALUE").toString());
|
}
|
lebalList.add("合计");
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
|
for(Map map:typeList){
|
String key = base.get("THIRD_CATEGORY_ID")+"_"+map.get("DATAKEY").toString();
|
if(extendMap.get(key)!=null){
|
tempList.add(ConvertUtil.obj2StrBlank(extendMap.get(key)));
|
}else{
|
tempList.add("0");
|
}
|
}
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
@Override
|
public Map queryDealTimeReport(String cusId,String beginDay,String endDay) {
|
|
Map paramMap = new HashMap();
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginDay", beginDay+"000000");
|
paramMap.put("endDay", endDay+"666666");
|
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
Map extendMap = new HashMap();
|
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(finish_num,0) finish_num,truncate(ifnull(avg_time,0)*3600*1000,0) avg_time,truncate(ifnull(sum_time,0)*3600*1000,0) sum_time from ");
|
builder.append(" (select AVG(a.DEAL_USE_TIME) avg_time,sum(a.DEAL_USE_TIME) sum_time,b.CATEGORY_NAME THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL a,SC_SERVCE_CATEGORY b where a.state!=10 and a.CUSTOMER_ID=:cusId and a.CREATE_TIME>=:beginDay and a.CREATE_TIME<=:endDay and a.THIRD_CATEGORY_ID=b.id group by a.THIRD_CATEGORY_ID,b.CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal left join ");
|
builder.append(" (select THIRD_CATEGORY_NAME,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=2 or b.WFSTATE=4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME) mfinish ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
|
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
|
|
//组织表头
|
lebalList.add("服务目录");
|
lebalList.add("驻场事件总数");
|
lebalList.add("已完成数");
|
lebalList.add("处理总时长");
|
lebalList.add("平均处理时长");
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
for(Map base:baseList){
|
DecimalFormat df = new DecimalFormat("######0.00");
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(DateUtil.secToTime(Math.round((Double)base.get("sum_time")))));
|
tempList.add(ConvertUtil.obj2StrBlank(DateUtil.secToTime(Math.round((Double)base.get("avg_time")))));
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
@Override
|
public Map queryDealSituationReport(String cusId,String beginDay,String endDay) {
|
|
Map paramMap = new HashMap();
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginDay", beginDay+"000000");
|
paramMap.put("endDay", endDay+"666666");
|
paramMap.put("nodeTemplateId", Constants.YXCL);
|
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
Map extendMap = new HashMap();
|
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_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, ");
|
builder.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((IFNULL(total_num,0)-IFNULL(timeout_num,0))*100/IFNULL(total_num,0),2) ontime_rate from ");
|
builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal ");
|
builder.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.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) manswer ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
|
builder.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 i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mactivi ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
|
builder.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 and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfinish ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
|
builder.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 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtimeout ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mtimeout.THIRD_CATEGORY_ID ");
|
builder.append(" left join (select count(id) pri_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where SLA_NAME='LEVEL1' and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mpri ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mpri.THIRD_CATEGORY_ID ");
|
builder.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 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mques ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mques.THIRD_CATEGORY_ID ");
|
builder.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 and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mdeal ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
|
builder.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 and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfirst ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mfirst.THIRD_CATEGORY_ID ");
|
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
|
|
//组织表头
|
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){
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+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);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
@Override
|
public Map queryDealDayReport(Map param) {
|
String sql="select a.ID,a.zsxm from AC_USER_REF_ROLE b,ac_role c,gg_user a where a.zt=1 and a.id=b.YHBH and b.JSBH=c.ID and c.IDENTIFY='LOCATION' ";
|
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("userName")))){
|
param.put("userName", "%"+ConvertUtil.obj2Str(param.get("userName"))+"%");
|
sql+=" and a.zsxm like :userName ";
|
}
|
List<Map> userList=baseDao.queryForList(sql, param);
|
sql="SELECT count(*) num,RESOLVE_USER_ID,to_number(substr(RESOLVE_TIME,1,8)) day from sc_workflow_incident_local ";
|
|
sql+=" where to_number(substr(RESOLVE_TIME,1,8))>=:beginDay and to_number(substr(RESOLVE_TIME,1,8))<=:endDay ";
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
|
sql+=" and CUSTOMER_ID=:cusId ";
|
}
|
sql+=" GROUP BY RESOLVE_USER_ID,to_number(substr(RESOLVE_TIME,1,8))";
|
List<Map> numList=baseDao.queryForList(sql, param);
|
Long beginDay=ConvertUtil.obj2Long(param.get("beginDay"));
|
Long endDay=ConvertUtil.obj2Long(param.get("endDay"));
|
beginDay=DateUtil.getDateAdd(beginDay, -1, 8);
|
endDay=DateUtil.getDateAdd(endDay, 1, 8);
|
List<String> categories = DateUtil.getDates(beginDay, endDay);
|
if(categories.size()==0){
|
categories.add(ConvertUtil.obj2Str(DateUtil.getCurrentDate8()));
|
}
|
List<Map> dayList=new LinkedList<Map>();
|
if(categories.size()>0){
|
for(String s:categories){
|
Map mapa=new HashMap();
|
mapa.put("day", s);
|
mapa.put("num", 0);
|
dayList.add(mapa);
|
}
|
}
|
if(userList.size()>0){
|
for(Map mapb:userList){
|
mapb.put("dayList", dayList);
|
}
|
}
|
if(userList.size()>0){
|
for(Map map:userList){
|
String userId=ConvertUtil.obj2Str(map.get("id"));
|
List<Map> list2=new LinkedList<Map>();
|
for(Map mm:dayList){
|
Map mn=new HashMap();
|
mn.putAll(mm);
|
list2.add(mn);
|
}
|
if(list2.size()>0){
|
for(Map mapc:list2){
|
String day=ConvertUtil.obj2Str(mapc.get("day"));
|
if(numList.size()>0){
|
for(Map mapd:numList){
|
String RESOLVE_USER_ID=ConvertUtil.obj2Str(mapd.get("RESOLVE_USER_ID"));
|
String relove_day=ConvertUtil.obj2Str(mapd.get("day"));
|
if(userId.equals(RESOLVE_USER_ID) && day.equals(relove_day)){
|
mapc.put("num", mapd.get("num"));
|
}
|
}
|
}
|
}
|
}
|
map.put("dayList", list2);
|
}
|
}
|
Map result=new HashMap();
|
result.put("dayList", dayList);
|
result.put("userList", userList);
|
return result;
|
}
|
|
|
}
|
