package cn.ksource.web.facade.tj;
|
|
import cn.ksource.beans.GG_USER;
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
import cn.ksource.core.util.*;
|
import cn.ksource.web.Constants;
|
import cn.ksource.web.entity.LoginEntity;
|
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.servlet.http.HttpServletRequest;
|
import java.text.DecimalFormat;
|
import java.util.*;
|
|
@Service("incidentStatisFacade")
|
public class IncidentStatisFacadeImpl implements IncidentStatisFacade {
|
|
@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 incidentTypePie(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 incidentTypeLine(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);
|
resultMap.put("step", Math.round(categories.size()/8));
|
|
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");
|
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 C WHERE 1=1 ");
|
if(StringUtil.notEmpty(cusId) ) {
|
buffer.append(" AND CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", 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");
|
|
Map paramMap = new HashMap();
|
StringBuilder builder = new StringBuilder("");
|
if(type.equals("2")) {
|
builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m-%d') AS DC_TIME FROM SC_WORKFLOW_INCIDENT C WHERE 1=1 ");
|
} else {
|
builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m') AS DC_TIME FROM SC_WORKFLOW_INCIDENT C WHERE 1=1 ");
|
}
|
|
if(StringUtil.notEmpty(cusId) ) {
|
builder.append(" AND C.CUSTOMER_ID = :cusId 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 ");
|
builder.append(" GROUP BY $KEY,DC_TIME ");
|
String sql = builder.toString().replace("$KEY", queryMap.get("col").toString());
|
return baseDao.queryForList(sql,paramMap);
|
}
|
|
@Override
|
public Map incidentTimeLine(HttpServletRequest request){
|
String selDay = request.getParameter("selDay");
|
String cusId = request.getParameter("cusId");
|
|
StringBuilder sql = new StringBuilder();
|
Map paramMap = new HashMap();
|
sql.append(" SELECT HOUR(HAPPEN_TIME) MHOUR,count(ID) NUM FROM SC_WORKFLOW_INCIDENT C");
|
sql.append(" where DATE_FORMAT(HAPPEN_TIME,'%Y%m%d')=:selDay and HOUR(HAPPEN_TIME)>=7 and HOUR(HAPPEN_TIME)<=18 ");
|
if(StringUtil.notEmpty(cusId) ) {
|
sql.append(" AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
paramMap.put("cusId", cusId);
|
}
|
sql.append(" GROUP BY MHOUR ");
|
|
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 incidentDealRateColumn(HttpServletRequest request){
|
String selDay = request.getParameter("selDay");
|
String cusId = request.getParameter("cusId");
|
String queryType = request.getParameter("queryType");
|
|
StringBuilder sql = new StringBuilder();
|
Map paramMap = new HashMap();
|
sql.append(" select CUSTOMER_ID,floor((count(ID)/(select count(ID) from workflow_base c where businesstype = 8 ))*100) RATE ");
|
sql.append(" from workflow_base C where businesstype = 8 and wfstate=2 and CUSTOMER_ID is not null ");
|
sql.append(" and CURRENT_NODE_ID = :nodeId ");
|
if(queryType.equals("frontLine")){
|
paramMap.put("nodeId", Constants.KFRY);
|
}else{
|
paramMap.put("nodeId", Constants.YXCL);
|
}
|
if(StringUtil.notEmpty(cusId)) {
|
sql.append(" AND CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
|
}
|
sql.append(" GROUP BY CUSTOMER_ID ");
|
List<Map> queryList = baseDao.queryForList(sql.toString(), paramMap);
|
List<String> categories = new ArrayList<String>();//X轴类别
|
List series = new ArrayList();
|
Map seriesMap = new HashMap();
|
List dataList = new ArrayList();
|
seriesMap.put("name", "解决率");
|
seriesMap.put("data", dataList);
|
series.add(seriesMap);
|
Map resMap = new HashMap();
|
resMap.put("categories", categories);
|
resMap.put("series", series);
|
return resMap;
|
}
|
|
@Override
|
public Map incidentDealRateLine(HttpServletRequest request) {
|
String selDay = request.getParameter("selDay");
|
String cusId = request.getParameter("cusId");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
|
List<String> categories = new ArrayList<String>();//获取X轴数据
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
|
|
StringBuilder sql = new StringBuilder();
|
Map paramMap = new HashMap();
|
sql.append(" select DATE_FORMAT(CREATETIME,'%Y%m%d') MDAY,CURRENT_NODE_ID,floor((count(ID)/(select count(ID) from workflow_base c where businesstype = 8 ))*100) RATE ");
|
sql.append(" from workflow_base C where businesstype = 8 and wfstate=2 and CUSTOMER_ID is not null ");
|
sql.append(" and CURRENT_NODE_ID in ('"+Constants.KFRY+"','"+Constants.YXCL+"') ");
|
if(StringUtil.notEmpty(cusId) ) {
|
sql.append(" AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
}
|
sql.append(" GROUP BY CURRENT_NODE_ID,MDAY ");
|
System.out.println("sql is ---------------"+sql.toString());
|
List<Map> queryList = baseDao.queryForList(sql.toString(), paramMap);
|
|
List series = new ArrayList();//最终数据集合
|
|
Map tempMap = new HashMap();
|
tempMap.put(Constants.KFRY, "服务台解决率");
|
tempMap.put(Constants.YXCL, "一线解决率");
|
Iterator it=tempMap.keySet().iterator();
|
while(it.hasNext()){
|
String key = it.next().toString() ;
|
String value = tempMap.get(key).toString();
|
Map seriesMap = new HashMap();
|
List dataList = new ArrayList();
|
seriesMap.put("name", value);
|
for(String categorie:categories){
|
Integer rate = 0;
|
for(Map queryMap:queryList){
|
if(queryMap.get("CURRENT_NODE_ID").toString().equals(key)&&categorie.equals(queryMap.get("MDAY").toString())){
|
rate = Integer.valueOf(queryMap.get("RATE").toString());
|
break;
|
}
|
}
|
dataList.add(rate);
|
}
|
seriesMap.put("data", dataList);
|
series.add(seriesMap);
|
}
|
|
Map resMap = new HashMap();
|
resMap.put("categories", categories);
|
resMap.put("series", series);
|
resMap.put("step", Math.round(categories.size()/8));
|
return resMap;
|
}
|
|
@Override
|
public Map incidentTotalChart(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 ");
|
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 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 ");
|
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 order by b.ordernum");
|
sql.append(" ) t group by t.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+"' order by ordernum");
|
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,b.WFSTATE from SC_WORKFLOW_INCIDENT 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 and c.STATE !=10");
|
}
|
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 queryIncidentStatis(String cusId,String subCustomerId,String beginTime,String endTime,String jjState,String csState,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,B.CURRENT_DEALER_NAME FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B WHERE 1=1 AND A.ID=B.BUSINESS_ID");
|
if(StringUtil.notEmpty(cusId) ){
|
sql.append(" AND A.CUSTOMER_ID=:cusId ");
|
}
|
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(jjrId)){
|
sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
|
}
|
if(StringUtil.notEmpty(csState)){
|
if(csState.equals("1")){
|
sql.append(" AND (A.ANSWER_TIMEOUT=1 or A.DEAL_TIMEOUT=1) ");
|
}else if(csState.equals("2")){
|
sql.append(" AND A.ANSWER_TIMEOUT=2 AND A.DEAL_TIMEOUT=2 ");
|
}
|
}
|
if(StringUtil.notEmpty(jjState)){
|
if(jjState.equals("1")){
|
sql.append(" AND B.WFSTATE in(2,4) ");
|
}else if(jjState.equals("2")){
|
sql.append(" AND B.WFSTATE=1 ");
|
}
|
}
|
List incidentList=baseDao.queryForList(sql.toString(), param);
|
return incidentList;
|
}
|
@Override
|
public List queryIncidentStatisTwo(String cusId,String subCustomerId,String beginTime,String endTime,String jjState,String csState,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,B.CURRENT_DEALER_NAME FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B WHERE 1=1 AND A.ID=B.BUSINESS_ID");
|
if(StringUtil.notEmpty(cusId) ){
|
sql.append(" AND A.CUSTOMER_ID=:cusId ");
|
}
|
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(jjrId)){
|
sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
|
}
|
if(StringUtil.notEmpty(type_id)){
|
sql.append(" AND A.TYPE_ID=:type_id");
|
}
|
if(StringUtil.notEmpty(csState)){
|
if(csState.equals("1")){
|
sql.append(" AND (A.ANSWER_TIMEOUT=1 or A.DEAL_TIMEOUT=1) ");
|
}else if(csState.equals("2")){
|
sql.append(" AND A.ANSWER_TIMEOUT=2 AND A.DEAL_TIMEOUT=2 ");
|
}
|
}
|
if(StringUtil.notEmpty(jjState)){
|
if(jjState.equals("1")){
|
sql.append(" AND B.WFSTATE in(2,4) ");
|
}else if(jjState.equals("2")){
|
sql.append(" AND B.WFSTATE=1 ");
|
}
|
}
|
List incidentList=baseDao.queryForList(sql.toString(), param);
|
return incidentList;
|
}
|
|
/**
|
* 事件达成率统计
|
*/
|
@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 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 queryOrderClStatis(HttpServletRequest request,int type) {
|
Map resultMap = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String subCustomerId = request.getParameter("sub_customer_id");
|
StringBuilder builder1 = new StringBuilder("SELECT COUNT(A.ID) AS NUM,B.THIRD_CATEGORY_ID,B.THIRD_CATEGORY_NAME FROM WORKFLOW_BASE A,SC_WORKFLOW_INCIDENT B WHERE A.BUSINESS_ID = B.ID AND A.WFSTATE != 5 AND A.BUSINESSTYPE = :businessType AND A.CUSTOMER_ID = :cusId AND A.CUSTOMER_ID = :cusId AND B.CREATE_TIME > :beginTime AND B.CREATE_TIME <= :endTime ");
|
StringBuilder builder2 = new StringBuilder("SELECT COUNT(A.ID) AS NUM,B.THIRD_CATEGORY_ID,A.WFSTATE FROM WORKFLOW_BASE A,SC_WORKFLOW_INCIDENT B WHERE A.BUSINESS_ID = B.ID AND A.WFSTATE != 5 AND A.BUSINESSTYPE = :businessType AND A.CUSTOMER_ID = :cusId AND A.CUSTOMER_ID = :cusId AND B.CREATE_TIME > :beginTime AND B.CREATE_TIME <= :endTime ");
|
|
Map paramMap = new HashMap();
|
|
if(StringUtil.notEmpty(subCustomerId)) {
|
builder1.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
|
builder2.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
|
paramMap.put("subCustomerId", subCustomerId);
|
}
|
|
builder1.append(" GROUP BY B.THIRD_CATEGORY_ID ");
|
builder2.append(" GROUP BY B.THIRD_CATEGORY_ID,A.WFSTATE ");
|
|
paramMap.put("cusId", cusId);
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginTime", beginTime+"000000");
|
paramMap.put("endTime", endTime+"240000");
|
paramMap.put("businessType", type);
|
|
|
List<Map> list1 = baseDao.queryForList(builder1.toString(),paramMap);
|
|
List<Map> list2 = baseDao.queryForList(builder2.toString(),paramMap);
|
|
int total = 0;
|
int totaljxz = 0;
|
int totalywc = 0;
|
int totalygb = 0;
|
int totalypj = 0;
|
int totalygq = 0;
|
|
if(null!=list1 && list1.size()>0) {
|
Map map2 = new HashMap();
|
for(Map map : list2) {
|
String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
|
String wfstate = ConvertUtil.obj2StrBlank(map.get("WFSTATE"));
|
String unitKey = third_category_id+"-"+wfstate;
|
String num = ConvertUtil.obj2StrBlank(map.get("NUM"));
|
map2.put(unitKey, num);
|
}
|
|
|
for(Map map : list1) {
|
int num = ConvertUtil.obj2Int(map.get("NUM"));
|
total += num;
|
|
|
String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
|
String unitKey1 = third_category_id+"-1";
|
|
int jxz = 0;
|
if(map2.containsKey(unitKey1)) {
|
jxz = ConvertUtil.obj2Int(map2.get(unitKey1));
|
}
|
map.put("jxz", jxz);
|
|
totaljxz+=jxz;
|
|
int ywc = 0;
|
String unitKey2 = third_category_id+"-2";
|
if(map2.containsKey(unitKey2)) {
|
ywc = ConvertUtil.obj2Int(map2.get(unitKey2));
|
}
|
map.put("ywc", ywc);
|
|
totalywc += ywc;
|
|
|
int ygb = 0;
|
String unitKey3 = third_category_id+"-3";
|
if(map2.containsKey(unitKey3)) {
|
ygb = ConvertUtil.obj2Int(map2.get(unitKey3));
|
}
|
map.put("ygb", ygb);
|
|
totalygb += ygb;
|
|
int ypj = 0;
|
String unitKey4 = third_category_id+"-4";
|
if(map2.containsKey(unitKey4)) {
|
ypj = ConvertUtil.obj2Int(map2.get(unitKey4));
|
}
|
map.put("ypj", ypj);
|
|
totalypj += ypj;
|
|
int ygq = 0;
|
String unitKey6 = third_category_id+"-6";
|
if(map2.containsKey(unitKey6)) {
|
ygq = ConvertUtil.obj2Int(map2.get(unitKey6));
|
}
|
map.put("ygq", ygq);
|
|
totalygq += ygq;
|
|
}
|
}
|
|
resultMap.put("reports", list1);
|
resultMap.put("totaljxz", totaljxz);
|
resultMap.put("totalywc", totalywc);
|
resultMap.put("totalygb", totalygb);
|
resultMap.put("totalypj", totalypj);
|
resultMap.put("totalygq", totalygq);
|
resultMap.put("total", total);
|
System.out.println(JsonUtil.map2Json(resultMap));
|
return resultMap;
|
}
|
|
|
//////////////////////////////////////////////////////////////////////////////////////////////////////
|
@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.FIRST_CATEGORY_NAME,mtotal.SECOND_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 where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,\n" +
|
" SECOND_CATEGORY_NAME,\n" +
|
" FIRST_CATEGORY_NAME) mtotal left join ");
|
builder.append(" (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT 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 ");
|
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.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_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 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 ");
|
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 ");
|
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("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_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.FIRST_CATEGORY_NAME,mtotal.SECOND_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,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT 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 ,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME) mtotal left join ");
|
builder.append(" (select THIRD_CATEGORY_NAME,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=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,i.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.FIRST_CATEGORY_NAME,mtotal.SECOND_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 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 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 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 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 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 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 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 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 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 queryEngineerDealReport(HttpServletRequest request) {
|
String cusId = request.getParameter("cusId");
|
String beginDay = request.getParameter("beginDay");
|
String endDay = request.getParameter("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.user_name,IFNULL(mactivi.num ,0)activi_num,IFNULL(mfinish.num ,0) finish_num,IFNULL(msend.num ,0) send_num, ");
|
builder.append(" IFNULL(truncate(mfinish.num*100/mtotal.num,2),0) deal_rate,IFNULL(mtime.deal_time,0) deal_time,IFNULL(mscore.score ,0) score from ");
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) num,user_name,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
|
builder.append(" ) u WHERE current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mtotal left join ");
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) num,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
|
builder.append(" ) t where b.CURRENT_DEALER_ID = t.USER_ID and b.WFSTATE=1 and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mactivi on mtotal.user_id = mactivi.user_id left join ");
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) num,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
|
builder.append(" ) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mfinish on mtotal.user_id = mfinish.user_id left join ");
|
builder.append(" ( ");
|
builder.append(" select truncate(avg(TIMESTAMPDIFF(SECOND,b.CREATETIME,b.ENDTIME)*1000),0) deal_time,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
|
builder.append(" ) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mtime on mtotal.user_id = mtime.user_id left join ");
|
builder.append(" ( ");
|
builder.append(" select truncate(avg(SCORE),2) score,i.user_id from AUDITING_SATIS_INFO i,( ");
|
builder.append(" select u.USER_ID from SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" where g.ID = u.GROUP_ID and CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
|
builder.append(" ) u where i.USER_ID=u.USER_ID ");
|
builder.append(" and BUS_TYPE=8 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay and CUSTOMER_ID=:cusId ");
|
builder.append(" ) mscore on mtotal.user_id = mscore.user_id left join ");
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) num,user_name,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
|
builder.append(" ) u WHERE current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.CURRENT_DEALER_ID<>t.user_id and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) msend on mtotal.user_id = msend.user_id ");
|
List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
|
|
//组织表头
|
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("user_name")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("send_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("deal_rate"))+"%");
|
tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("deal_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("score")));
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
@Override
|
public Map queryServiceDealReport(HttpServletRequest request) {
|
|
String cusId = request.getParameter("cusId");
|
String beginDay = request.getParameter("beginDay");
|
String endDay = request.getParameter("endDay");
|
|
//String deskId = Constants.getDeskTop(Constants.ZFWT_DESK);
|
String deskId = "";
|
Map paramMap = new HashMap();
|
paramMap.put("cusId", cusId);
|
paramMap.put("cusId", cusId);
|
paramMap.put("deskId", deskId);
|
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.user_name,IFNULL(create_num ,0) create_num,IFNULL(finish_num ,0) finish_num,truncate(IFNULL(finish_num ,0)*100/total_num,2) deal_rate, ");
|
builder.append(" IFNULL(forward_num ,0) forward_num,IFNULL(repeat_num,0) repeat_num,IFNULL(error_num,0) error_num,IFNULL(invest_num ,0) invest_num from ");
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) total_num,user_name,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) u WHERE current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mtotal left join ");
|
//创建事件数
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) create_num,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) t where b.CREATERID = t.USER_ID and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mcreate on mtotal.user_id = mcreate.user_id left join ");
|
//完成事件数
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) finish_num,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mfinish on mtotal.user_id = mfinish.user_id left join ");
|
//转发数
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) forward_num,user_id from workflow_base b , ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) u WHERE current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.CURRENT_DEALER_ID<>t.user_id and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mforward on mtotal.user_id = mforward.user_id left join ");
|
//重复事件数
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) repeat_num,user_id from workflow_base b , SC_WORKFLOW_INCIDENT i, ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) u WHERE current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.id=i.FLOW_ID and i.RESOLVE_TYPE_ID ='SJCF' and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mrepeat on mtotal.user_id = mrepeat.user_id left join ");
|
//消失，误报
|
builder.append(" ( ");
|
builder.append(" select count(b.ID) error_num,user_id from workflow_base b , SC_WORKFLOW_INCIDENT i, ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) u WHERE current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.id=i.FLOW_ID and (i.RESOLVE_TYPE_ID ='XS' or i.RESOLVE_TYPE_ID ='WB') and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) merror on mtotal.user_id = merror.user_id left join ");
|
//调查
|
builder.append(" ( ");
|
builder.append(" select count(s.id) invest_num,u.user_id from AUDITING_SATIS_INFO s,( ");
|
builder.append(" SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
|
builder.append(" WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
|
builder.append(" union select id user_id from gg_user where DESKID = :deskId and ZT=1 ");
|
builder.append(" union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId ");
|
builder.append(" ) u where s.INVEST_USER_ID =u.USER_ID and CREATE_TIME>:beginDay and CREATE_TIME<:endDay and BUS_TYPE=8 and CUSTOMER_ID=:cusId ");
|
builder.append(" ) minvest on mtotal.user_id = minvest.user_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("满意度调查数");
|
|
//组织数据
|
if(baseList!=null&&baseList.size()>0){
|
for(Map base:baseList){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("user_name")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("create_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("deal_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("forward_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("repeat_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("error_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("invest_num")));
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
|
/**
|
* 将数据字典转化成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 substr("+colName+"||'',1,8)>=:startDate and substr("+colName+"||'',1,8)<=:endDate ");
|
}else if(frequency.equals("month")){
|
sql.append(" and substr("+colName+"||'',1,6)>=:startDate and substr("+colName+"||'',1,6)<=:endDate ");
|
}
|
}
|
|
@Override
|
public Map incidentTypePie(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 where 1=1 AND STATE!=10");
|
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 incidentSourcePie(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 where state!=10 ");
|
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 incidentCloseCol(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 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 incidentPriPie(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 where state!=10 ");
|
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 incidentInfluenceCol(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 where state!=10");
|
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 incidentLvPie(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 where state!=10 ");
|
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 incidentResponsePie(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 where state!=10 and 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 incidentDealPie(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 where state!=10 and 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 incidentHappendTimeLine(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 to_number(substr(happen_time||'',9,2)) mhour,count(id) num from sc_workflow_incident c");
|
sql.append(" where c.state!=10 and to_number(substr(happen_time||'',9,2))>=7 and to_number(substr(happen_time||'',9,2))<=18 ");
|
if(StringUtil.isNotBlank(cusId)){
|
sql.append(" and customer_id = :cusId ");
|
}
|
getDateWhereSql(frequency,sql,"create_time");
|
sql.append(" group by to_number(substr(happen_time||'',9,2)) ");
|
|
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 Map queryDealEngineerReportData(Map param) {
|
String sql="SELECT id,ZSXM,IFNULL(b.score,0) score,IFNULL(b.num,0) mydnum,IFNULL(c.num,0) cynum,IFNULL(d.num,0) wcnum FROM" +
|
" gg_user a " +
|
"LEFT JOIN " +
|
"( SELECT sum(SCORE) score,count(b.id) num,RESOLVE_USER_ID FROM order_satis_info a," +
|
"workflow_base b,sc_workflow_incident c WHERE a.BUSINESS_ID = b.id ";
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
|
sql+=" and to_number(substr(c.create_time||'',1,8))>=:beginDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
|
sql+=" and to_number(substr(c.create_time||'',1,8))<=:endDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
|
sql+=" and c.CUSTOMER_ID=:cusId ";
|
}
|
sql+=" AND c.id = b.BUSINESS_ID GROUP BY RESOLVE_USER_ID) b on a.id=b.RESOLVE_USER_ID " +
|
"LEFT JOIN " +
|
"(SELECT sum(d.num) num,d.CURRENT_DEALER_ID" +
|
" from (SELECT count(*) num," +
|
"c.CURRENT_DEALER_ID from sc_workflow_incident a,workflow_base b,workflow_node c" +
|
" where a.id=b.BUSINESS_ID and b.id=c.FLOWID ";
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
|
sql+=" and to_number(substr(a.create_time||'',1,8))>=:beginDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
|
sql+=" and to_number(substr(a.create_time||'',1,8))<=:endDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
|
sql+=" and a.CUSTOMER_ID=:cusId ";
|
}
|
sql+=" group by c.CURRENT_DEALER_ID,a.id) d" +
|
" GROUP BY CURRENT_DEALER_ID) c" +
|
" on c.CURRENT_DEALER_ID=a.id " +
|
" LEFT JOIN " ;
|
sql+=" (SELECT count(*) num,RESOLVE_USER_ID from sc_workflow_incident WHERE 1=1 ";
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
|
|
sql+=" and to_number(substr(create_time||'',1,8))>=:beginDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
|
sql+=" and to_number(substr(create_time||'',1,8))<=:endDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
|
sql+=" and CUSTOMER_ID=:cusId ";
|
}
|
sql+=" GROUP BY RESOLVE_USER_ID) d " +
|
"on a.id=d.RESOLVE_USER_ID" +
|
" WHERE a.zt = 1 ";
|
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 *,0 num from sc_sla where state=1 ORDER BY SERIAL";
|
List<Map> slaList=baseDao.queryForList(sql);
|
if(userList.size()>0){
|
for(Map ma:userList){
|
ma.put("slaList", slaList);
|
}
|
}
|
if(userList.size()>0){
|
sql="SELECT RESOLVE_USER_ID,SLA_ID,COUNT(a.id) num from sc_workflow_incident a," +
|
"workflow_base b where a.id=b.BUSINESS_ID and b.WFSTATE in(2,4) ";
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
|
sql+=" and to_number(substr(a.create_time||'',1,8))>=:beginDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
|
sql+=" and to_number(substr(a.create_time||'',1,8))<=:endDay ";
|
}
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
|
sql+=" and a.CUSTOMER_ID=:cusId ";
|
}
|
sql+=" GROUP BY RESOLVE_USER_ID,SLA_ID,SLA_NAME order by SLA_NAME";
|
List<Map> lvList=baseDao.queryForList(sql, param);
|
if(lvList.size()>0){
|
for(Map mapa:userList){
|
String userId=ConvertUtil.obj2Str(mapa.get("id"));
|
List<Map> list2=new LinkedList<Map>();
|
for(Map mm:slaList){
|
Map mn=new HashMap();
|
mn.putAll(mm);
|
list2.add(mn);
|
}
|
if(list2.size()>0){
|
for(Map mapb:list2){
|
String slaId=ConvertUtil.obj2Str(mapb.get("ID"));
|
for(Map mapc:lvList){
|
String RESOLVE_USER_ID=ConvertUtil.obj2Str(mapc.get("RESOLVE_USER_ID"));
|
String SLA_ID=ConvertUtil.obj2Str(mapc.get("SLA_ID"));
|
if(RESOLVE_USER_ID.equals(userId) && SLA_ID.equals(slaId)){
|
mapb.put("num", mapc.get("num"));
|
}
|
}
|
}
|
mapa.put("slaList", list2);
|
}
|
}
|
}
|
for(Map mape:userList){
|
String score=ConvertUtil.obj2Str(mape.get("score"));
|
String mydnum=ConvertUtil.obj2Str(mape.get("mydnum"));
|
String cynum=ConvertUtil.obj2Str(mape.get("cynum"));
|
String wcnum=ConvertUtil.obj2Str(mape.get("wcnum"));
|
if(StringUtil.isEmpty(mydnum) || mydnum.equals("0") || StringUtil.isEmpty(score) || score.equals("0")){
|
mape.put("myf", "0.00");
|
}else{
|
double myf=NumberUtil.div(ConvertUtil.obj2Double(score), ConvertUtil.obj2Double(mydnum),2);
|
mape.put("myf", myf);
|
}
|
if(StringUtil.isEmpty(cynum) || cynum.equals("0") || StringUtil.isEmpty(wcnum) || wcnum.equals("0")){
|
mape.put("jjl", "0");
|
}else{
|
double jjl=NumberUtil.div(ConvertUtil.obj2Double(wcnum), ConvertUtil.obj2Double(cynum),4);
|
jjl=jjl*100;
|
mape.put("jjl", jjl);
|
}
|
List<Map> list2=(List<Map>) mape.get("slaList");
|
int sum=0;
|
for(Map mapd:list2){
|
if(StringUtil.isEmpty(ConvertUtil.obj2Str(mapd.get("num")))){
|
sum+=0;
|
}else{
|
sum+=ConvertUtil.obj2Int(mapd.get("num"));
|
}
|
}
|
mape.put("sum", sum);
|
}
|
}
|
Map result=new HashMap();
|
result.put("userList", userList);
|
result.put("slaList", slaList);
|
return result;
|
}
|
|
/* (non-Javadoc)
|
* @see cn.ksource.web.facade.tj.IncidentStatisFacade#getGcsList(java.util.Map)
|
*/
|
@Override
|
public List<Map> getGcsList(Map param) {
|
String sql="select ID,ZSXM from gg_user where zt=1";
|
if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("type")))){
|
sql=" select a.ID,a.ZSXM from gg_user a,ac_user_ref_role b,ac_role c where a.ID=B.YHBH and C.ID=B.JSBH and C.IDENTIFY='LOCATION'";
|
|
}
|
return baseDao.queryForList(sql, param);
|
}
|
|
}
|
