package cn.ksource.web.facade.tj;
|
|
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.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.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
@Service
|
public class QuestionStatisFacadeImpl implements QuestionStatisFacade {
|
|
@Autowired
|
private BaseDao baseDao;
|
|
@Autowired
|
private DataDictionaryService dataDictionaryService;
|
|
//获取查询Map
|
private Map getQueryMap(HttpServletRequest request){
|
String focus = request.getParameter("focus");
|
|
Map queryMap = new HashMap();
|
if(focus.equals("QUESTION_SOURCE")){//按问题来源
|
queryMap.put("col","SOURCE_ID");
|
queryMap.put("focus",focus);
|
queryMap.put("dicCategoryKey",Constants.QUESTION_SOURCE);
|
}else if(focus.equals("QUESTION_CLOSE")){//关闭原因
|
queryMap.put("col","RESOLVE_TYPE_ID");
|
queryMap.put("focus",focus);
|
queryMap.put("dicCategoryKey",Constants.QUESTIONCLOSE_RESOLVE_TYPE);
|
}else if(focus.equals("QUESTION_PRI")){//优先级
|
queryMap.put("col","PRIORITY_ID");
|
queryMap.put("focus",focus);
|
queryMap.put("dicCategoryKey",Constants.INCIDENT_EVENT_PRI);
|
}else if(focus.equals("QUESTION_EFFECT")){//影响度
|
queryMap.put("col","INFLUENCE_ID");
|
queryMap.put("focus",focus);
|
queryMap.put("dicCategoryKey",Constants.EVENT_EFFECT_DG);
|
}else if(focus.equals("QUESTION_SERVER_CATALOG")){//服务目录
|
String lv1Id = request.getParameter("lv1Id");
|
String lv2Id = request.getParameter("lv2Id");
|
if(StringUtil.isBlank(lv1Id)){
|
queryMap.put("col","FIRST_CATEGORY_ID");
|
}else if(StringUtil.isNotBlank(lv1Id)&&StringUtil.isBlank(lv2Id)){
|
queryMap.put("col","SECOND_CATEGORY_ID");
|
}else if(StringUtil.isNotBlank(lv2Id)){
|
queryMap.put("col","THIRD_CATEGORY_ID");
|
}
|
queryMap.put("lv1Id",lv1Id);
|
queryMap.put("lv2Id",lv2Id);
|
queryMap.put("focus",focus);
|
}
|
return queryMap;
|
}
|
|
//获取初始化list
|
private List getQueryTypeList(Map queryMap){
|
String focus = ConvertUtil.obj2StrBlank(queryMap.get("focus"));
|
List queryTypeList = new ArrayList<Map>();
|
if(focus.equals("QUESTION_SOURCE")||focus.equals("QUESTION_CLOSE")||focus.equals("QUESTION_PRI")||focus.equals("QUESTION_EFFECT")){
|
queryTypeList = dataDictionaryService.getDataDictionaryByCategoryKey(queryMap.get("dicCategoryKey").toString());
|
}else if(focus.equals("QUESTION_SERVER_CATALOG")){
|
if(StringUtil.isBlank(queryMap.get("lv1Id").toString())){
|
String sql = "select ID DATAKEY,CATEGORY_NAME DATAVALUE from sc_servce_category where level=1 ";
|
queryTypeList = baseDao.queryForList(sql);
|
}else if(StringUtil.isNotBlank(queryMap.get("lv1Id").toString())&&StringUtil.isBlank(queryMap.get("lv2Id").toString())){
|
String sql = "select ID DATAKEY,CATEGORY_NAME DATAVALUE from sc_servce_category where level=2 and p_id=:lv1Id ";
|
queryTypeList = baseDao.queryForList(sql,new SqlParameter().addValue("lv1Id", queryMap.get("lv1Id").toString()));
|
}else if(StringUtil.isNotBlank(queryMap.get("lv2Id").toString())) {
|
String sql = "select ID DATAKEY,CATEGORY_NAME DATAVALUE from sc_servce_category where level=3 and p_id=:lv2Id ";
|
queryTypeList = baseDao.queryForList(sql,new SqlParameter().addValue("lv2Id", queryMap.get("lv2Id").toString()));
|
}
|
}
|
return queryTypeList;
|
}
|
|
@Override
|
public List questionTypePie(HttpServletRequest request) {
|
|
String cusId = request.getParameter("cusId");
|
String frequency = request.getParameter("frequency");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
if(!StringUtil.notEmptyNum(frequency)) {
|
frequency = "2";
|
}
|
if(frequency.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("JSON---------------------->"+JsonUtil.list2Json(listResult));
|
return listResult;
|
}
|
|
@Override
|
public Map questionTypeLine(HttpServletRequest request) {
|
|
String frequency = request.getParameter("frequency");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
|
if(!StringUtil.notEmptyNum(frequency)) {
|
frequency = "2";
|
}
|
List<String> categories = new ArrayList<String>();//获取X轴数据
|
if(frequency.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.ceil(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 frequency = request.getParameter("frequency");
|
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_QUESTION C WHERE state!=7 ");
|
if(StringUtil.notEmpty(queryType)){
|
buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
|
}
|
if(StringUtil.notEmpty(cusId) ) {
|
buffer.append(" AND CUSTOMER_ID = :cusId ");
|
paramMap.put("cusId", cusId);
|
}
|
if(StringUtil.notEmpty(frequency)) {
|
buffer.append(" AND C.CREATE_TIME >= :beginTime AND C.CREATE_TIME <= :endTime ");
|
if(frequency.equals("1")) {
|
paramMap.put("beginTime", beginTime+"01000000");
|
paramMap.put("endTime", endTime+"31240000");
|
} else {
|
paramMap.put("beginTime", beginTime+"000000");
|
paramMap.put("endTime", endTime+"240000");
|
}
|
|
}
|
if(ConvertUtil.obj2StrBlank(queryMap.get("focus")).equals("QUESTION_CLOSE")){
|
buffer.append(" and state = 7 ");
|
}
|
buffer.append(" and $KEY is not null ");
|
buffer.append(" GROUP BY $KEY ");
|
|
String sql = buffer.toString().replace("$KEY", ConvertUtil.obj2StrBlank(queryMap.get("col")));
|
List<Map> dataList = baseDao.queryForList(sql, paramMap);
|
return dataList;
|
}
|
|
//获取线形图查询结果
|
private List getQueryList_Line(HttpServletRequest request,Map queryMap){
|
|
String cusId = request.getParameter("cusId");
|
String frequency = request.getParameter("frequency");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String queryType = request.getParameter("queryType");
|
Map paramMap = new HashMap();
|
StringBuilder builder = new StringBuilder("");
|
if(frequency.equals("2")) {
|
builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,to_char(C.CREATE_TIME,\n" +
|
" 'yyyy-MM-dd') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1");
|
} else {
|
builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,to_char(C.CREATE_TIME,\n" +
|
" 'yyyy-MM') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
|
}
|
if(StringUtil.isBlank(queryType)){
|
builder.append(" AND state!=7 ");
|
}
|
|
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(frequency.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(frequency.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", ConvertUtil.obj2StrBlank(queryMap.get("col")));
|
return baseDao.queryForList(sql,paramMap);
|
}
|
|
/**
|
* 获取组装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,GROUP_CONCAT(num order by ordernum asc) "+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 DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate from SC_WORKFLOW_INCIDENT_question group by mdate ) 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_question ");
|
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 DATE_FORMAT(i.HAPPEN_TIME,'%Y%m%d')=b.mdate ");
|
sql.append(" group by b.mdate,b.datakey ");
|
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_question c ");
|
sql.append(" inner join workflow_base b on c.flow_id = b.id ");
|
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 ";
|
Map param = new HashMap();
|
param.put("categoryKey",categoryKey);
|
return baseDao.queryForList(sql,param);
|
}
|
|
public List getCiCategoryList(String lv1Id){
|
StringBuilder sql = new StringBuilder();
|
Map param = new HashMap();
|
param.put("lv1Id", lv1Id);
|
|
sql.append(" select id,CATEGORY_NAME name from sc_servce_category ");
|
sql.append(" where STATE=1 ");
|
if(StringUtil.isBlank(lv1Id)){
|
sql.append(" and LEVEL =1 ");
|
}else{
|
sql.append(" and LEVEL =2 and P_ID = :lv1Id ");
|
}
|
sql.append(" order by SERIAL ");
|
return baseDao.queryForList(sql.toString(),param);
|
}
|
|
@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_QUESTION 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_QUESTION 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 List<Map> queryQuestiontDclStatis(String cusId,String subCustomerId,String beginTime,String endTime) {
|
|
Map paramMap = new HashMap();
|
|
//通过项目编号，查询该项目的所有的服务目录
|
StringBuilder builder1 = new StringBuilder("SELECT COUNT(ID) AS NUM,THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME FROM SC_WORKFLOW_QUESTION WHERE state!=7 and CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId AND CREATE_TIME > :beginTime AND CREATE_TIME <= :endTime " );
|
|
StringBuilder builder2 = new StringBuilder("SELECT THIRD_CATEGORY_ID,RESOLVE_TIME,WANT_DEAL_TIME FROM SC_WORKFLOW_QUESTION WHERE state!=7 and CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId AND CREATE_TIME > :beginTime AND CREATE_TIME <= :endTime ");
|
if(StringUtil.notEmpty(subCustomerId)) {
|
builder1.append(" AND SUB_CUSTOMER_ID = :subCustomerId ");
|
builder2.append(" AND SUB_CUSTOMER_ID = :subCustomerId ");
|
paramMap.put("subCustomerId", subCustomerId);
|
}
|
|
String endSql = " GROUP BY THIRD_CATEGORY_ID ,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME ";
|
|
builder1.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);
|
|
|
|
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 time = ConvertUtil.obj2StrBlank(map.get("RESOLVE_TIME"));
|
if(!StringUtil.notEmpty(time)) {
|
time = DateUtil.getToday("yyyyMMdd");
|
} else {
|
time = DateUtil.format("yyyyMMdd", time);
|
}
|
|
String requestTime = ConvertUtil.obj2StrBlank(map.get("WANT_DEAL_TIME"));
|
|
|
if(ConvertUtil.obj2Int(requestTime) < ConvertUtil.obj2Int(time)) {
|
if(map2.containsKey(third_category_id)) {
|
int num = ConvertUtil.obj2Int(map2.get(third_category_id));
|
map2.put(third_category_id, num+1);
|
} else {
|
map2.put(third_category_id, 1);
|
}
|
}
|
|
|
|
}
|
|
|
for(Map map : list1) {
|
String thirdCategoryId = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
|
int num = 0;
|
if(map2.containsKey(thirdCategoryId)) {
|
num = ConvertUtil.obj2Int(map2.get(thirdCategoryId));
|
}
|
map.put("cs", num);
|
|
|
|
|
int total = ConvertUtil.obj2Int(map.get("NUM"));
|
|
|
double jjl = NumberUtil.div(ConvertUtil.obj2Double((total-num)), ConvertUtil.obj2Double(total), 3);
|
|
jjl = NumberUtil.mul(jjl, ConvertUtil.obj2Double(100));
|
|
map.put("jjl", jjl);
|
|
}
|
|
}
|
|
System.out.println(JsonUtil.list2Json(list1));
|
return list1;
|
}
|
|
|
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
|
@Override
|
public Map queryDetailReport(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>>();
|
//查询数据
|
StringBuilder builder = new StringBuilder();
|
builder.append(" SELECT q.NAME,q.DESCRIP,q.CONTACT_NAME,q.CONTACT_PHONE,q.SOURCE_NAME,q.THIRD_CATEGORY_NAME,q.SECOND_CATEGORY_NAME,q.FIRST_CATEGORY_NAME,q.WANT_DEAL_TIME WANT_DEAL_TIME, ");
|
builder.append(" q.PRIORITY_NAME,q.INFLUENCE_NAME,q.STATE,q.RESOLVE_TYPE_NAME,q.RESOLVE,t.CREATE_USER_NAME,t.CREATE_TIME CREATE_TIME,IFNULL(t.deal_time,0) deal_time ");
|
builder.append(" from SC_WORKFLOW_QUESTION q ");
|
builder.append(" LEFT JOIN ( ");
|
builder.append(" select z.CREATE_USER_NAME,z.CREATE_TIME,b.ID,round((\n" +
|
" b.ENDTIME - b.CREATETIME\n" +
|
" )*1000,\n" +
|
" 0) deal_time from QUESTION_ZD_HISTORY z,workflow_base b where b.ID = z.FLOWID ");
|
builder.append(" and z.ISTHEEND =1 group by b.ID,z.CREATE_USER_NAME,z.CREATE_TIME,round(( b.ENDTIME - b.CREATETIME )*1000,0) ");
|
builder.append(" ) t on q.FLOW_ID = t.ID where q.CREATE_TIME >:beginDay and q.CREATE_TIME < :endDay ");
|
if(StringUtil.notEmpty(cusId)){
|
builder.append(" and q.CUSTOMER_ID = :cusId ");
|
}
|
|
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("问题状态");
|
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("NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("DESCRIP")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("CONTACT_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("CONTACT_PHONE")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("SOURCE_NAME")));
|
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("WANT_DEAL_TIME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("PRIORITY_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("INFLUENCE_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("STATE")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("RESOLVE_TYPE_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("RESOLVE")));
|
//计算诊断时长
|
if(base.get("CREATE_USER_NAME")!=null){
|
tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("deal_time"))));
|
}else{
|
tempList.add("");
|
}
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("CREATE_USER_NAME")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("CREATE_TIME")));
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
|
@Override
|
public Map queryDealStatuReport(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>>();
|
//查询数据
|
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(sleep_num,0) sleep_num, ");
|
builder.append(" IFNULL(avg_deal_time,0) avg_deal_time,truncate(IFNULL(major_num,0)*100/total_num,2) major_rate,truncate(IFNULL(finish_num, 0)*100/total_num,2) solve_rate,truncate(IFNULL(close_num, 0)*100/total_num,2) close_rate, ");
|
builder.append(" truncate(IFNULL(repeat_num, 0)*100/total_num,2) repeat_rate,truncate(IFNULL(nosolve_num, 0)*100/total_num,2) nosolve_rate from ");
|
builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(i.id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID 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,i.SECOND_CATEGORY_NAME,i.FIRST_CATEGORY_NAME) mtotal ");
|
builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b,(SELECT FLOWID FROM workflow_node WHERE FLOWSTATE = 1 GROUP BY FLOWID) n where b.BUSINESS_ID = i.ID AND b.id = n.FLOWID 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_QUESTION i,workflow_base b where b.BUSINESS_ID = i.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_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=2 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(i.id) sleep_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=6 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) msleep ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = msleep.THIRD_CATEGORY_ID ");
|
builder.append(" left join (select truncate(avg(TIMESTAMPDIFF(SECOND,b.CREATETIME,b.ENDTIME))*1000,0) avg_deal_time,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION 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) mdeal ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
|
builder.append(" left join (select count(id) major_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i where i.PRI_LEVEL='LEVEL1' and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mmajor ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mmajor.THIRD_CATEGORY_ID ");
|
builder.append(" left join (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.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 ");
|
builder.append(" left join (select count(id) repeat_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i where i. RESOLVE_TYPE_ID='WTCF' and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mrepeat ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mrepeat.THIRD_CATEGORY_ID ");
|
builder.append(" left join (select count(id) nosolve_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i where i. RESOLVE_TYPE_ID='WFJJ' and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mnosolve ");
|
builder.append(" on mtotal.THIRD_CATEGORY_ID = mnosolve.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("问题重复率");
|
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("sleep_num")));
|
tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("avg_deal_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("major_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("solve_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("close_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("repeat_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("nosolve_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");
|
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select mtotal.user_name,IFNULL(create_num,0) create_num,IFNULL(redis_num,0) redis_num,IFNULL(activi_num,0) activi_num, ");
|
builder.append(" IFNULL(finish_num,0) finish_num,IFNULL(close_num,0) close_num,IFNULL(avg_deal_time,0) avg_deal_time,TRUNCATE(IFNULL(finish_num,0)*100/total_num,2) deal_rate, ");
|
//查询总数
|
builder.append(" TRUNCATE(IFNULL(ontime_num,0)*100/total_num,2) ontime_rate,IFNULL(major_num,0) major_num from ( ");
|
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 n,( ");
|
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 group by u.USER_ID ");
|
builder.append(" ) u WHERE n.current_dealer_id = u.USER_ID GROUP BY USER_ID,FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mtotal left join ( ");
|
///查询创建数
|
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 group by u.USER_ID ");
|
builder.append(" ) t where b.CREATERID = t.user_id and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" 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(" select count(b.ID) redis_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 n,( ");
|
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 group by u.USER_ID ");
|
builder.append(" ) u WHERE n.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=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mredis on mtotal.user_id = mredis.user_id left join ( ");
|
//查询进行中工单
|
builder.append(" select count(b.ID) activi_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 n,(");
|
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 group by u.USER_ID ");
|
builder.append(" ) u WHERE n.current_dealer_id = u.USER_ID and n.FLOWSTATE=2 GROUP BY FLOWID ");
|
builder.append(" ) t where b.ID = t.FLOWID and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" 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(" 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 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=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" 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(" select count(b.ID) close_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 group by u.USER_ID ");
|
builder.append(" ) t where b.CURRENT_DEALER_ID = t.USER_ID and b.WFSTATE=3 and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mclose on mtotal.user_id = mclose.user_id left join ( ");
|
//查询平均处理时间
|
builder.append(" select truncate(avg(TIMESTAMPDIFF(SECOND, b.CREATETIME, b.ENDTIME)*1000),0) avg_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 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=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mavg on mtotal.user_id = mavg.user_id left join ( ");
|
//查询解决个数
|
builder.append(" select count(b.id) ontime_num,user_id from workflow_base b , SC_WORKFLOW_QUESTION i , ");
|
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 group by u.USER_ID ");
|
builder.append(" ) t where b.CURRENT_DEALER_ID = t.USER_ID and b.BUSINESS_ID = i.ID and i.DEAL_TIMEOUT=2 and (b.WFSTATE=2 or b.WFSTATE=4) ");
|
builder.append(" and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) montime on mtotal.user_id = montime.user_id left join ( ");
|
//查询重大事件
|
builder.append(" select count(b.ID) major_num,user_name,user_id from workflow_base b ,SC_WORKFLOW_QUESTION i , ");
|
builder.append(" ( ");
|
builder.append(" SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
|
builder.append(" FROM workflow_node n,( ");
|
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 group by u.USER_ID ");
|
builder.append(" ) u WHERE n.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.PRI_LEVEL='LEVEL1' and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
|
builder.append(" and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
|
builder.append(" ) mmajor on mtotal.user_id = mmajor.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("解决及时率");
|
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("redis_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("close_num")));
|
tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("avg_deal_time"))));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("deal_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("ontime_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("major_num")));
|
dataList.add(tempList);
|
}
|
}
|
Map res = new HashMap();
|
res.put("lebalList",lebalList);
|
res.put("dataList",dataList);
|
return res;
|
}
|
|
@Override
|
public Map queryStatusReport(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>>();
|
//查询数据
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(sleep_num,0) sleep_num, ");
|
builder.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from ( ");
|
builder.append(" select count(b.id) total_num,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
builder.append(" where b.BUSINESS_ID = i.ID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
|
builder.append(" GROUP BY I.THIRD_CATEGORY_ID ,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME ");
|
builder.append(" ) mtotal left join ( ");
|
builder.append(" select count(b.id) answer_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i,( ");
|
builder.append(" select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");
|
builder.append(" ) n where b.BUSINESS_ID = i.ID and b.id = n.FLOWID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
|
builder.append(" GROUP BY I.THIRD_CATEGORY_ID ");
|
builder.append(" ) manswer on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID left join ( ");
|
builder.append(" select count(b.id) activi_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
builder.append(" where b.BUSINESS_ID = i.ID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
|
builder.append(" and b.WFSTATE=1 GROUP BY I.THIRD_CATEGORY_ID ");
|
builder.append(" ) mactivi on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID left join ( ");
|
builder.append(" select count(b.id) sleep_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
builder.append(" where b.BUSINESS_ID = i.ID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
|
builder.append(" and b.WFSTATE=6 GROUP BY I.THIRD_CATEGORY_ID ");
|
builder.append(" ) msleep on mtotal.THIRD_CATEGORY_ID = msleep.THIRD_CATEGORY_ID left join ( ");
|
builder.append(" select count(b.id) finish_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
builder.append(" where b.BUSINESS_ID = i.ID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
|
builder.append(" and (b.WFSTATE=2 or b.WFSTATE=4) GROUP BY I.THIRD_CATEGORY_ID ");
|
builder.append(" ) mfinish on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID left join ( ");
|
builder.append(" select count(b.id) close_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i ");
|
builder.append(" where b.BUSINESS_ID = i.ID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
|
builder.append(" and b.WFSTATE=3 GROUP BY I.THIRD_CATEGORY_ID ");
|
builder.append(" ) mclose on mtotal.THIRD_CATEGORY_ID = mclose.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("已取消");
|
//组织数据
|
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("sleep_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("close_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 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 questionSourcePie(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.QUESTION_SOURCE);
|
formatDicList(categoryList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,source_id category from sc_workflow_question 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 questionCloseCol(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.QUESTIONCLOSE_RESOLVE_TYPE);
|
Map dataKeyMap = dicListToDataKeyMap(dicList);
|
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select count(id) val,resolve_type_id datakey,1 as category from sc_workflow_question 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 questionPriPie(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_question 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 questionInfluenceCol(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_question 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);
|
}
|
|
|
|
|
|
}
|
