package cn.ksource.web.facade.desktop;
|
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.springframework.stereotype.Service;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.DateUtil;
|
import cn.ksource.core.util.JsonUtil;
|
import cn.ksource.core.util.NumberUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.web.Constants;
|
import cn.ksource.web.facade.customermanage.CustomerManageFacade;
|
|
@Service("desktopFacade")
|
public class DesktopFacadeImpl implements DesktopFacade {
|
|
@Resource
|
private BaseDao baseDao;
|
@Resource
|
private CustomerManageFacade customerFacade;
|
|
@Override
|
public Map getOperationDeskDetail(String cusId) {
|
String incidentAddNum = getIncidentAddNum(cusId);
|
String incidentDealNum = getIncidentDealNum(cusId);
|
String questionAddNum = getQuestionAddNum(cusId);
|
String questionDealNum = getQuestionDealNum(cusId);
|
String incidentLocalAddNum = getIncidentLocalAddNum(cusId);
|
String incidentLocalDealNum = getIncidentLocalDealNum(cusId);
|
String dailyPatrolAddNum = getDailyPatrolAddNum(cusId);
|
String dailyPatrolDealNum = getDailyPatrolDealNum(cusId);
|
String remindAddNum = getRemindAddNum(cusId);
|
String remindDealNum = getRemindDealNum(cusId);
|
String healthAddNum = getHealthAddNum(cusId);
|
String healthDealNum = getHealthDealNum(cusId);
|
String knowlegeAddNum = getKnowlegeAddNum(cusId);
|
String knowlegeDealNum = getKnowlegeDealNum(cusId);
|
String ciAddNum = getCiAddNum(cusId);
|
String ciDealNum = getCiDealNum(cusId);
|
String satifactionAddNum = getSatifactionAddNum(cusId);
|
Map satifactionScore = getSatifactionScore(cusId);
|
String satiScore = "0/0";
|
if(!StringUtil.isEmpty(ConvertUtil.obj2StrBlank(satifactionScore.get("ALL_SCORE")))){
|
double satifactionScore1 = NumberUtil.round(ConvertUtil.obj2Double(satifactionScore.get("ALL_SCORE")), 0);
|
double satifactionScore2 = NumberUtil.round(ConvertUtil.obj2Double(satifactionScore.get("REQUEST_SCORE")), 0);
|
satiScore = ConvertUtil.obj2StrBlank(satifactionScore1)+"/"+ConvertUtil.obj2StrBlank(satifactionScore2);
|
}
|
String fileAddNum = getFileAddNum();
|
String fileDealNum = getFileDealNum();
|
Map info = new HashMap();
|
info.put("incidentAddNum", incidentAddNum);
|
info.put("incidentDealNum", incidentDealNum);
|
info.put("questionAddNum", questionAddNum);
|
info.put("questionDealNum", questionDealNum);
|
info.put("incidentLocalAddNum", incidentLocalAddNum);
|
info.put("incidentLocalDealNum", incidentLocalDealNum);
|
info.put("dailyPatrolAddNum", dailyPatrolAddNum);
|
info.put("dailyPatrolDealNum", dailyPatrolDealNum);
|
info.put("remindAddNum", remindAddNum);
|
info.put("remindDealNum", remindDealNum);
|
info.put("healthAddNum", healthAddNum);
|
info.put("healthDealNum", healthDealNum);
|
info.put("knowlegeAddNum", knowlegeAddNum);
|
info.put("knowlegeDealNum", knowlegeDealNum);
|
info.put("ciAddNum", ciAddNum);
|
info.put("ciDealNum", ciDealNum);
|
info.put("satifactionAddNum", satifactionAddNum);
|
info.put("satifactionScore", satiScore);
|
info.put("fileAddNum", fileAddNum);
|
info.put("fileDealNum", fileDealNum);
|
return info;
|
}
|
|
/**
|
* 事件新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getIncidentAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from SC_WORKFLOW_INCIDENT where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_INCIDENT where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_INCIDENT where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 事件处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getIncidentDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" ");
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_INCIDENT c,workflow_base b,workflow_node n ");
|
builder.append(" where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and n.FLOWSTATE = 1 and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_INCIDENT c,workflow_base b ");
|
builder.append(" where c.FLOW_ID = b.id ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and b.wfstate=1 ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(ID) num from SC_WORKFLOW_INCIDENT where DATE_FORMAT(CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
|
builder.append(" and (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
|
/**
|
* 问题新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getQuestionAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from SC_WORKFLOW_QUESTION where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_QUESTION where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_QUESTION where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 问题处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getQuestionDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_QUESTION c,workflow_base b,workflow_node n ");
|
builder.append(" where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and n.DEAL_TYPE = 1 and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_QUESTION c,workflow_base b ");
|
builder.append(" where c.FLOW_ID = b.id ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and b.wfstate=1 ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_QUESTION c,workflow_base b,workflow_node n ");
|
builder.append(" where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and n.NODE_TEMPLATE_ID = :nodeTemplateId ");
|
builder.append(" and c.CUSTOMER_ID=:cusId and b.WFSTATE=1 ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId",cusId)
|
.addValue("nodeTemplateId",Constants.WTHG);
|
return baseDao.queryForString(builder.toString(),param);
|
}
|
|
|
/**
|
* 驻场运维新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getIncidentLocalAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from SC_WORKFLOW_INCIDENT_LOCAL where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_INCIDENT_LOCAL where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_INCIDENT_LOCAL where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
|
/**
|
* 驻场运维新处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getIncidentLocalDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" ");
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_INCIDENT_LOCAL c,workflow_base b,workflow_node n ");
|
builder.append(" where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and n.DEAL_TYPE = 1 and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_INCIDENT_LOCAL c,workflow_base b ");
|
builder.append(" where c.FLOW_ID = b.id ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and b.wfstate=1 ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(ID) num from SC_WORKFLOW_INCIDENT_LOCAL where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') ");
|
builder.append(" and (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
|
/**
|
* 日常巡检新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getDailyPatrolAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from CI_DAILY_PATROL where DATE_FORMAT(PATROL_DATE,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUS_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from CI_DAILY_PATROL where YEARWEEK(PATROL_DATE)=YEARWEEK(NOW()) and CUS_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from CI_DAILY_PATROL where DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUS_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 日常巡检处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getDailyPatrolDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from CI_DAILY_PATROL where DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') ");
|
builder.append(" and STATE=1 and CUS_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from CI_DAILY_PATROL where DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') ");
|
builder.append(" and STATE=2 and CUS_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from CI_DAILY_PATROL where DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') ");
|
builder.append(" and STATE=3 and CUS_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
|
/**
|
* 例行巡检新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getRemindAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from SC_WORKFLOW_CI_REMIND where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_CI_REMIND where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_CI_REMIND where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 例行巡检处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getRemindDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(t.ID) num from CI_REMIND_TIMERTASK t ,CI_REMIND_CUSTOMER_ITEM i ");
|
builder.append(" where t.CUSTOMER_ITEM_ID = i.ID and t.STATE=1 ");
|
builder.append(" and DATE_FORMAT(t.PLAN_EXECUTION_DATE,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and i.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_CI_REMIND c,workflow_base b ");
|
builder.append(" where c.FLOW_ID = b.id ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and b.WFSTATE=1 ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_CI_REMIND c,workflow_base b ");
|
builder.append(" where c.FLOW_ID = b.id ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and b.WFSTATE=2 ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 健康检查新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getHealthAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from SC_WORKFLOW_CI_HEALTH where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_CI_HEALTH where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from SC_WORKFLOW_CI_HEALTH where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
|
builder.append(" ) t ");
|
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 健康检查处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getHealthDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(t.ID) num from CI_HEALTH_PLAN_TIMER t ,CI_HEALTH_PLAN p ");
|
builder.append(" where t.PLAN_ID = p.ID and p.CUS_ID = :cusId ");
|
builder.append(" and t.STATE = 1 and DATE_FORMAT(t.PLAN_EXE_DATE,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_CI_HEALTH c,workflow_base b,workflow_node n ");
|
builder.append(" where c.FLOW_ID = b.id and b.wfstate=1 and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from SC_WORKFLOW_CI_HEALTH c,workflow_base b,workflow_node n ");
|
builder.append(" where c.FLOW_ID = b.id and b.wfstate=2 and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
|
builder.append(" and c.CUSTOMER_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 知识库新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getKnowlegeAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from KM_LIBRARY_TEMP where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from KM_LIBRARY_TEMP where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from KM_LIBRARY_TEMP where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 知识库处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getKnowlegeDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from KM_LIBRARY_TEMP c ");
|
builder.append(" where 1=1 ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') and c.audit_state=1 ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from KM_LIBRARY c ");
|
builder.append(" where 1=1 ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
|
|
|
|
|
/**
|
* 配置库新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getCiAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from CMDB_CI_BASE where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUS_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from CMDB_CI_BASE where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUS_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from CMDB_CI_BASE where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUS_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 配置库处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getCiDealNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from CMDB_CI_BASE c");
|
builder.append(" where 1=1 ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') and c.STATE=0 ");
|
builder.append(" and c.CUS_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(c.ID) num from CMDB_CI_BASE c ");
|
builder.append(" where 1=1 ");
|
builder.append(" and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') and c.STATE=1 ");
|
builder.append(" and c.CUS_ID=:cusId ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
|
/**
|
* 满意度调查新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getSatifactionAddNum(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from ORDER_SATIS_INFO where DATE_FORMAT(INVEST_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from ORDER_SATIS_INFO where YEARWEEK(INVEST_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from ORDER_SATIS_INFO where DATE_FORMAT(INVEST_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
/**
|
* 满意度调查得分
|
* @param cusId
|
* @return
|
*/
|
private Map getSatifactionScore(String cusId){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select ALL_SCORE,REQUEST_SCORE from TOTLE_SATIS_INFO ");
|
builder.append(" where month= DATE_FORMAT(now(),'%Y%m') and CUSTOMER_ID = :cusId ");
|
return baseDao.queryForMap(builder.toString(),new SqlParameter("cusId",cusId));
|
}
|
|
@Override
|
public Map getCusSatisfactionSurvey(HttpServletRequest request) {
|
List<Map> cusList = customerFacade.getCusList();
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select i.customer_id id,i.all_score score ");
|
builder.append(" from totle_satis_info i ");
|
builder.append(" where i.month = date_format(now(),'%Y%m') ");
|
builder.append(" group by customer_id ");
|
|
List<Map> dataList = baseDao.queryForList(builder.toString());
|
Map dataListSet = new HashMap();
|
if(dataList!=null&&dataList.size()>0){
|
for(Map map:dataList){
|
dataListSet.put(map.get("id").toString(), map.get("score"));
|
}
|
}
|
|
List<String> categories = new ArrayList<String>();
|
for(Map map:cusList){
|
String cusName = ConvertUtil.obj2StrBlank(map.get("name"));
|
categories.add(cusName);
|
}
|
|
List series = new ArrayList<Integer>();
|
for(Map cus:cusList){
|
String key = ConvertUtil.obj2StrBlank(cus.get("id"));
|
if(dataListSet.get(key)!=null){
|
series.add(ConvertUtil.obj2Double(dataListSet.get(key)));
|
}else{
|
series.add(0);
|
}
|
}
|
Map surveyMap = new HashMap();
|
surveyMap.put("series",series);
|
surveyMap.put("categories",categories);
|
return surveyMap;
|
}
|
|
@Override
|
public Map getCusSatisfactionDetail(String cusId) {
|
StringBuilder builder = new StringBuilder();
|
customerFacade.getCusById(cusId);
|
Map cusInfo = customerFacade.getCusById(cusId);
|
|
builder.setLength(0);
|
builder.append(" select CONCAT(d.SATIS_TYPE,'_',i.MONTH) ID,d.SATIS_SCORE SCORE ");
|
builder.append(" from TOTLE_SATIS_INFO i,TOTLE_SATIS_DETAIL d ");
|
builder.append(" where i.ID = d.BUS_ID ");
|
builder.append(" and left(i.MONTH,4) = DATE_FORMAT(now(),'%Y') ");
|
builder.append(" and i.CUSTOMER_ID = :cusId ");
|
builder.append(" group by i.MONTH,d.SATIS_TYPE ");
|
List<Map> dataList = baseDao.queryForList(builder.toString(),new SqlParameter("cusId",cusId));
|
Map dataListMap = new HashMap();
|
if(dataList!=null&&dataList.size()>0){
|
for(Map map:dataList){
|
dataListMap.put(map.get("ID").toString(), map.get("SCORE"));
|
}
|
}
|
//组合十二月
|
List<String> categs = new ArrayList<String>();
|
String curYear = ConvertUtil.obj2StrBlank(DateUtil.getYear());
|
List<String> categories = new ArrayList<String>();
|
for(int i=1;i<=12;i++){
|
if(i<10){
|
categs.add(curYear+"0"+i);
|
categories.add(curYear+"-0"+i);
|
}else{
|
categs.add(curYear+i);
|
categories.add(curYear+"-"+i);
|
}
|
}
|
//组装图表数据
|
List<String> labelList = new ArrayList<String>();
|
labelList.add("1");
|
labelList.add("3");
|
labelList.add("6");
|
|
List<Map> series = new ArrayList<Map>();
|
for(String label:labelList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
for(String categorie:categs){
|
String key = label + "_"+ categorie;
|
if(dataListMap.get(key)!=null){
|
tempList.add(dataListMap.get(key));
|
}else{
|
tempList.add(0);
|
}
|
}
|
if(label.equals("1")){
|
tempMap.put("name","事件响应支持类");
|
}else if(label.equals("3")){
|
tempMap.put("name","驻场服务类");
|
}else if(label.equals("6")){
|
tempMap.put("name","驻场响应支持类");
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
Map surveyMap = new HashMap();
|
surveyMap.put("series",series);
|
surveyMap.put("categories",categories);
|
surveyMap.put("cusInfo",cusInfo);
|
return surveyMap;
|
}
|
|
/**
|
* 文档新增数量
|
* @param cusId
|
* @return
|
*/
|
private String getFileAddNum(){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" select count(id) num from GG_FILES where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and (AUDIT_STATE = :shtg or AUDIT_STATE IS NULL) ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from GG_FILES where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and (AUDIT_STATE = :shtg or AUDIT_STATE IS NULL) ");
|
builder.append(" union all ");
|
builder.append(" select count(id) num from GG_FILES where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and (AUDIT_STATE = :shtg or AUDIT_STATE IS NULL) ");
|
builder.append(" ) t ");
|
return baseDao.queryForString(builder.toString(),new SqlParameter("shtg",Constants.FILE_STATE_SHTG));
|
}
|
|
/**
|
* 文档处理数量
|
* @param cusId
|
* @return
|
*/
|
private String getFileDealNum(){
|
StringBuilder builder = new StringBuilder();
|
builder.append(" ");
|
builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
|
builder.append(" ( ");
|
builder.append(" select count(ID) num from GG_FILES ");
|
builder.append(" where DATE_FORMAT(CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and AUDIT_STATE = :wsh ");
|
builder.append(" ) ");
|
builder.append(" union all ");
|
builder.append(" ( ");
|
builder.append(" select count(ID) num from GG_FILES where DATE_FORMAT(CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
|
builder.append(" and (AUDIT_STATE = :shtg or AUDIT_STATE = :shbtg or AUDIT_STATE IS NULL) ");
|
builder.append(" ) ");
|
builder.append(" ) t ");
|
Map param = new HashMap();
|
param.put("wsh", Constants.FILE_STATE_DSH);
|
param.put("shtg", Constants.FILE_STATE_SHTG);
|
param.put("shbtg", Constants.FILE_STATE_SHBTG);
|
return baseDao.queryForString(builder.toString(),param);
|
}
|
|
}
|
