package cn.ksource.web.facade.tj;
|
|
import cn.ksource.core.dao.BaseDao;
|
import cn.ksource.core.dao.SqlParameter;
|
import cn.ksource.core.util.*;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
import java.text.ParseException;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
@Service
|
public class SatisfactionStatisFacadeImpl implements SatisfactionStatisFacade{
|
@Resource
|
private BaseDao baseDao;
|
|
@Override
|
public Map satisfactionDetailChart(HttpServletRequest request) {
|
//获取查询参数
|
Map param = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String busType = request.getParameter("busType");
|
|
|
//查询参数初始化
|
param.put("cusId", cusId);
|
param.put("cusId", cusId);
|
param.put("beginTime", beginTime);
|
param.put("endTime", endTime);
|
param.put("busType", busType);
|
|
//查询最新模板id
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select id from CUSTOMER_SATIS_TEMPLATE where state=1 and template_type = :busType ");
|
String templateId = baseDao.queryForString(sql.toString(),param);
|
param.put("templateId", templateId);
|
|
//组织表头
|
sql.setLength(0);
|
sql.append(" select id,item_name text,FATHER_ID parentId,ORDERNUM sortId from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where TEMPLATE_ID = :templateId ");
|
sql.append(" union ");
|
sql.append(" select id,text,parentId,sortId from ( ");
|
sql.append(" select i1.id,i1.ITEM_ID parentId,i1.ORDERNUM sortId,i1.EVA_NAME text from satis_eva_item i1 ");
|
sql.append(" inner join CUSTOMER_SATIS_TEMPLATE_ITEM i2 on i1.ITEM_ID = i2.ID ");
|
sql.append(" where i2.TEMPLATE_ID = :templateId ) t ");
|
//将查询结果配装为树
|
TreeUtil treeUtil = new TreeUtil();
|
Map root = treeUtil.createTree(baseDao.queryForList(sql.toString(),param));
|
|
//查询每个日期下对应的各三级满意度调查项的满意度数量
|
sql.setLength(0);
|
//三级满意度调查项与时间外连接
|
sql.append(" select t.$COLUMN,GROUP_CONCAT(IFNULL(i.num,'-') order by ordernum1,ordernum2,ordernum3) total_num from ( ");
|
sql.append(" select t2.$COLUMN,t1.resultid,ordernum1,ordernum2,ordernum3 from ");
|
sql.append(" ( ");
|
sql.append(" select v3.id resultid,v1.ORDERNUM ordernum1,v2.ORDERNUM ordernum2,v3.ordernum ordernum3 from ");
|
sql.append(" ( ");
|
sql.append(" select ID,ITEM_NAME,FATHER_ID,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where state=1 and item_level =1 and TEMPLATE_ID = :templateId ");
|
sql.append(" ) v1 ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select ID,ITEM_NAME,FATHER_ID,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where state=1 and item_level =2 and TEMPLATE_ID = :templateId ");
|
sql.append(" ) v2 ");
|
sql.append(" on v1.ID = v2.FATHER_ID ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select id,item_id,eva_name,ordernum from satis_eva_item ");
|
sql.append(" ) v3 ");
|
sql.append(" on v2.id = v3.item_id ");
|
sql.append(" ) t1 , ");
|
sql.append(" ( ");
|
sql.append(" select $COLUMN from SATIS_INFO_DETAIL d inner join $TABLE i ");
|
sql.append(" on d.BUSINESS_ID = i.ID ");
|
sql.append(" where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime ");
|
sql.append(" and i.TEMPLATE_ID =:templateId ");
|
if(StringUtil.notEmpty(cusId)){
|
sql.append(" and i.customer_id = :cusId ");
|
}
|
if(StringUtil.notEmpty(cusId) ) {
|
sql.append(" AND i.CUSTOMER_ID = :cusId ");
|
}
|
sql.append(" group by $COLUMN ");
|
sql.append(" ) t2 ");
|
sql.append(" ) t ");
|
//将满意度调查项根据调查时间，三级调查项目分组
|
sql.append(" left join ");
|
sql.append(" ( ");
|
sql.append(" select count(i.id) num,d.RESULTID,i.$COLUMN from SATIS_INFO_DETAIL d inner join $TABLE i ");
|
sql.append(" on d.BUSINESS_ID = i.ID ");
|
sql.append(" where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime ");
|
sql.append(" and i.customer_id = :cusId and i.TEMPLATE_ID =:templateId ");
|
if(StringUtil.notEmpty(cusId) ) {
|
sql.append(" AND i.CUSTOMER_ID = :cusId ");
|
}
|
sql.append(" group by i.$COLUMN,d.RESULTID ");
|
sql.append(" ) i on t.resultid = i.RESULTID and t.$COLUMN = i.$COLUMN ");
|
sql.append(" group by $COLUMN ");
|
|
Map resMap = new HashMap();
|
String finalSql = sql.toString();
|
if(busType.equals("1")||busType.equals("2")){
|
finalSql = finalSql.replace("$COLUMN","INVEST_TIME");
|
finalSql = finalSql.replace("$TABLE","ORDER_SATIS_INFO");
|
}else if(busType.equals("3")||busType.equals("4")){
|
finalSql = finalSql.replace("$COLUMN","CARD_MONTH");
|
finalSql = finalSql.replace("$TABLE","AUDITING_SATIS_INFO");
|
}
|
List<Map> resList = baseDao.queryForList(finalSql,param);
|
resMap.put("resList", resList);
|
resMap.put("root", root);
|
return resMap;
|
}
|
|
@Override
|
public Map satisfactionBigItemChart(HttpServletRequest request) {
|
//获取查询参数
|
Map param = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String showType = request.getParameter("showType");
|
String busType = request.getParameter("busType");
|
//查询参数初始化
|
param.put("cusId", cusId);
|
param.put("beginTime", beginTime);
|
param.put("endTime", endTime);
|
param.put("busType", busType);
|
//查询最新模板id
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select id from CUSTOMER_SATIS_TEMPLATE where state=1 and template_type = :busType ");
|
String templateId = baseDao.queryForString(sql.toString(),param);
|
param.put("templateId", templateId);
|
|
//获取LegendList
|
sql.setLength(0);
|
sql.append(" select id,item_name name from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where item_level = 1 and TEMPLATE_ID = :templateId ");
|
sql.append(" order by ORDERNUM ");
|
List<Map> legendList = baseDao.queryForList(sql.toString(),param);
|
//获取xAxis
|
List<String> categories = new ArrayList<String>();
|
//查询数据
|
sql.setLength(0);
|
sql.append(" select CONCAT(id1,'_',$COLUMN) mname,SUM(score) score from ");
|
sql.append(" ( ");
|
sql.append(" select i.$COLUMN,t.id1,t.id2,truncate((t.weight*AVG(d.RESULTSCORE))/1000,1) score from ");
|
sql.append(" SATIS_INFO_DETAIL d ");
|
sql.append(" inner join $TABLE i ");
|
sql.append(" on d.BUSINESS_ID = i.ID ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select lv1.id id1,lv1.ITEM_NAME name1,lv2.id id2,lv2.ITEM_NAME name2,weight from ");
|
sql.append(" ( ");
|
sql.append(" select id,ITEM_NAME,FATHER_ID,TEMPLATE_WEIGHT weight from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 2 ");
|
sql.append(" ) lv2 ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select id,ITEM_NAME from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 1 ");
|
sql.append(" ) lv1 ");
|
sql.append(" on lv2.FATHER_ID = lv1.id ");
|
sql.append(" ) t ");
|
sql.append(" on t.id2 = d.CONFIGID ");
|
sql.append(" where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime ");
|
sql.append(" and i.TEMPLATE_ID =:templateId ");
|
if(StringUtil.notEmpty(cusId)){
|
sql.append(" and i.customer_id = :cusId ");
|
}
|
if(StringUtil.notEmpty(cusId) ) {
|
sql.append(" AND i.CUSTOMER_ID = :cusId ");
|
}
|
sql.append(" group by i.$COLUMN,t.id1,t.id2 ");
|
sql.append(" )t group by id1,$COLUMN ");
|
|
String finalSql = sql.toString();
|
if(busType.equals("1")||busType.equals("2")){
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
|
finalSql = finalSql.replace("$COLUMN","INVEST_TIME");
|
finalSql = finalSql.replace("$TABLE","ORDER_SATIS_INFO");
|
}else if(busType.equals("3")||busType.equals("4")){
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
|
finalSql = finalSql.replace("$COLUMN","CARD_MONTH");
|
finalSql = finalSql.replace("$TABLE","AUDITING_SATIS_INFO");
|
}
|
List<Map> queryList = baseDao.queryForList(finalSql,param);
|
//hightchart series
|
List<Map> series = new ArrayList<Map>();
|
List<List> tableData = new ArrayList<List>();
|
|
Map queryMap = new HashMap();
|
if(queryList!=null&&queryList.size()>0){
|
for(Map map:queryList){
|
queryMap.put(map.get("mname").toString(), map.get("score"));
|
}
|
}
|
|
if(showType.equals("chart")){
|
//组装 hightchart series
|
for(Map legendMap:legendList){
|
Map tempMap = new HashMap();
|
tempMap.put("name", legendMap.get("name").toString());
|
List tempList = new ArrayList();
|
for(String category:categories){
|
String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
|
if(queryMap.get(name)!=null){
|
double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
|
tempList.add(score);
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
}
|
//table数据组装
|
if(showType.equals("table")){
|
for(String category:categories ){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList<String>();
|
tempList.add(category);
|
for(Map legendMap:legendList){
|
String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
|
if(queryMap.get(name)!=null){
|
double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
|
tempList.add(score);
|
}else{
|
tempList.add("-");
|
}
|
}
|
tableData.add(tempList);
|
}
|
}
|
//System.out.println("table------------------"+JsonUtil.list2Json(tableData));
|
//返回结果
|
Map resMap = new HashMap();
|
resMap.put("categories", categories);
|
resMap.put("series", series);
|
resMap.put("step", Math.round(categories.size()/9));
|
|
resMap.put("tableData", tableData);
|
resMap.put("tableLabel", legendList);
|
|
return resMap;
|
}
|
|
|
@Override
|
public Map satisfactionSmallItemChart(HttpServletRequest request) {
|
//获取查询参数
|
Map param = new HashMap();
|
String cusId = request.getParameter("cusId");
|
String beginTime = request.getParameter("beginTime");
|
String endTime = request.getParameter("endTime");
|
String showType = request.getParameter("showType");
|
String busType = request.getParameter("busType");
|
//查询参数初始化
|
param.put("cusId", cusId);
|
param.put("beginTime", beginTime);
|
param.put("endTime", endTime);
|
param.put("busType", busType);
|
//查询最新模板id
|
StringBuilder sql = new StringBuilder();
|
sql.append(" select id from CUSTOMER_SATIS_TEMPLATE where state=1 and template_type = :busType ");
|
String templateId = baseDao.queryForString(sql.toString(),param);
|
param.put("templateId", templateId);
|
|
//获取LegendList
|
sql.setLength(0);
|
sql.append(" select v2.id id,v2.name name from ");
|
sql.append(" ( ");
|
sql.append(" select id,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where item_level = 1 and TEMPLATE_ID = :templateId ");
|
sql.append(" ) v1 ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select id,item_name name,FATHER_ID pid,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where item_level = 2 and TEMPLATE_ID = :templateId ");
|
sql.append(" ) v2 ");
|
sql.append(" on v1.id = v2.pid ");
|
sql.append(" order by v1.ORDERNUM,v2.ORDERNUM ");
|
List<Map> legendList = baseDao.queryForList(sql.toString(),param);
|
//获取xAxis
|
List<String> categories = new ArrayList<String>();
|
|
//查询数据
|
sql.setLength(0);
|
sql.append(" select CONCAT(t.id2,'_',i.$COLUMN) mname, truncate(AVG(d.RESULTSCORE)/10,1) score from ");
|
sql.append(" SATIS_INFO_DETAIL d ");
|
sql.append(" inner join $TABLE i ");
|
sql.append(" on d.BUSINESS_ID = i.ID ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select lv1.id id1,lv1.ITEM_NAME name1,lv2.id id2,lv2.ITEM_NAME name2,weight from ");
|
sql.append(" ( ");
|
sql.append(" select id,ITEM_NAME,FATHER_ID,TEMPLATE_WEIGHT weight from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 2 and TEMPLATE_ID = :templateId ");
|
sql.append(" ) lv2 ");
|
sql.append(" inner join ");
|
sql.append(" ( ");
|
sql.append(" select id,ITEM_NAME from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 1 and TEMPLATE_ID = :templateId ");
|
sql.append(" ) lv1 ");
|
sql.append(" on lv2.FATHER_ID = lv1.id ");
|
sql.append(" ) t ");
|
sql.append(" on t.id2 = d.CONFIGID ");
|
sql.append(" where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime ");
|
sql.append(" and i.TEMPLATE_ID =:templateId ");
|
if(StringUtil.notEmpty(cusId)){
|
sql.append(" and i.customer_id = :cusId ");
|
}
|
if(StringUtil.notEmpty(cusId)) {
|
sql.append(" AND i.CUSTOMER_ID = :cusId ");
|
}
|
sql.append(" group by i.$COLUMN,t.id1,t.id2 ");
|
|
|
String finalSql = sql.toString();
|
if(busType.equals("1")||busType.equals("2")){
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
|
finalSql = finalSql.replace("$COLUMN","INVEST_TIME");
|
finalSql = finalSql.replace("$TABLE","ORDER_SATIS_INFO");
|
}else if(busType.equals("3")||busType.equals("4")){
|
categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
|
finalSql = finalSql.replace("$COLUMN","CARD_MONTH");
|
finalSql = finalSql.replace("$TABLE","AUDITING_SATIS_INFO");
|
}
|
List<Map> queryList = baseDao.queryForList(finalSql,param);
|
//hightchart series
|
List<Map> series = new ArrayList<Map>();
|
List<List> tableData = new ArrayList<List>();
|
Map queryMap = new HashMap();
|
if(queryList!=null&&queryList.size()>0){
|
for(Map map:queryList){
|
queryMap.put(map.get("mname").toString(), map.get("score"));
|
}
|
}
|
//组装 hightchart series
|
if(showType.equals("chart")){
|
|
for(Map legendMap:legendList){
|
Map tempMap = new HashMap();
|
tempMap.put("name", legendMap.get("name").toString());
|
List tempList = new ArrayList();
|
for(String category:categories){
|
String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
|
if(queryMap.get(name)!=null){
|
double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
|
tempList.add(score);
|
}else{
|
tempList.add(0);
|
}
|
}
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
}
|
}
|
//组装talbe数据
|
if(showType.equals("table")){
|
for(String category:categories ){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList<String>();
|
tempList.add(category);
|
for(Map legendMap:legendList){
|
String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
|
if(queryMap.get(name)!=null){
|
double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
|
tempList.add(score);
|
}else{
|
tempList.add("-");
|
}
|
}
|
tableData.add(tempList);
|
}
|
}
|
System.out.println("tableData--------------------"+JsonUtil.list2Json(tableData));
|
sql.setLength(0);
|
sql.append(" select id,item_name text,FATHER_ID parentId,ORDERNUM sortId from CUSTOMER_SATIS_TEMPLATE_ITEM ");
|
sql.append(" where TEMPLATE_ID = :templateId and (ITEM_LEVEL =1 or ITEM_LEVEL =2) ");
|
TreeUtil treeUtil = new TreeUtil();
|
Map root = treeUtil.createTree(baseDao.queryForList(sql.toString(),param));
|
|
//返回结果
|
Map resMap = new HashMap();
|
|
resMap.put("categories", categories);
|
resMap.put("series", series);
|
resMap.put("step", Math.round(categories.size()/9));
|
resMap.put("tableData", tableData);
|
resMap.put("tableLabel", root);
|
return resMap;
|
}
|
|
|
@Override
|
public Map satisfactionMonthChart(HttpServletRequest request) {
|
String cusId = request.getParameter("cusId");
|
String showType = request.getParameter("showType");
|
|
String selMonth = request.getParameter("selMonth");
|
|
StringBuilder sql = new StringBuilder();
|
Map param = new HashMap();
|
param.put("selMonth", selMonth);
|
param.put("cusId", cusId);
|
List<String> categories = new ArrayList<String>();
|
categories.add("事件响应类得分");
|
categories.add("驻场服务得分");
|
categories.add("驻场响应类得分");
|
categories.add("总得分");
|
|
sql.setLength(0);
|
sql.append(" select i.month,i.all_score,d.satis_score from totle_satis_info i, totle_satis_detail d ");
|
sql.append(" where i.id = d.bus_id and i.month = :selMonth and i.customer_id = :cusId order by d.satis_type asc ");
|
List<Map> queryList = baseDao.queryForList(sql.toString(),param);
|
Map resMap = new HashMap();
|
Map queryMap = new HashMap();
|
if(queryList!=null&&queryList.size()>0){
|
for(int i=0;i<queryList.size();i++){
|
if(i==0){
|
queryMap.put("XYL_SCORE",queryList.get(i).get("satis_score"));
|
}else if(i==1){
|
queryMap.put("ZC_SCORE",queryList.get(i).get("satis_score"));
|
queryMap.put("ALL_SCORE",queryList.get(i).get("all_score"));
|
}else if(i==2){
|
queryMap.put("ZCXYL_SCORE",queryList.get(i).get("satis_score"));
|
}
|
}
|
}
|
|
if(showType.equals("chart")){
|
Map tempMap = new HashMap();
|
List<Map> series = new ArrayList<Map>();
|
List tempList = new ArrayList();
|
tempMap.put("name", DateUtil.format("yyyy-MM", selMonth));
|
tempList.add(ConvertUtil.obj2Double(queryMap.get("XYL_SCORE")));
|
tempList.add(ConvertUtil.obj2Double(queryMap.get("ZC_SCORE")));
|
tempList.add(ConvertUtil.obj2Double(queryMap.get("ALL_SCORE")));
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
resMap.put("series", series);
|
resMap.put("categories", categories);
|
}else{
|
queryMap.put("MONTH", DateUtil.format("yyyy-MM", selMonth));
|
resMap.put("tableLabel", categories);
|
resMap.put("tableData",queryMap);
|
}
|
return resMap;
|
}
|
|
@Override
|
public Map querySatisReport(String cusId,String beginDay,String endDay) {
|
|
Map paramMap = new HashMap();
|
paramMap.put("cusId", cusId);
|
paramMap.put("beginDay", beginDay);
|
paramMap.put("endDay", endDay);
|
//定义报表变量
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
//查询数据
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select d.SATIS_TYPE,d.REQUEST_FG_RATE,d.SATIS_QZ,d.REQUEST_SATIS_SCORE,sum(d.ALL_COUNT) total_num,SUM(d.SATIS_COUNT) do_num,sum(d.ALL_COUNT-SATIS_COUNT) undo_num, ");
|
builder.append(" truncate(avg(d.FG_RATE),2) cover_rate,truncate(avg(d.SATIS_SCORE),2) score ");
|
builder.append(" from TOTLE_SATIS_INFO i ,TOTLE_SATIS_DETAIL d ");
|
builder.append(" where i.ID = d.BUS_ID and i.MONTH >= DATE_FORMAT(:beginDay,'%Y%m') and i.MONTH<=DATE_FORMAT(:endDay,'%Y%m') ");
|
if(StringUtil.notEmpty(cusId)){
|
builder.append(" and i.CUSTOMER_ID = :cusId ");
|
}
|
builder.append(" group by d.SATIS_TYPE order by d.SATIS_TYPE ");
|
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>();
|
if(ConvertUtil.obj2StrBlank(base.get("SATIS_TYPE")).equals("1")){
|
tempList.add("事件响应支持类");
|
}else if(ConvertUtil.obj2StrBlank(base.get("SATIS_TYPE")).equals("3")){
|
tempList.add("驻场服务类");
|
}else if(ConvertUtil.obj2StrBlank(base.get("SATIS_TYPE")).equals("6")){
|
tempList.add("驻场响应支持类");
|
}
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
|
|
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("do_num")));
|
if(ConvertUtil.obj2StrBlank(base.get("undo_num")).indexOf("-")>=0){
|
tempList.add("0");
|
}else{
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("undo_num")));
|
}
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("REQUEST_FG_RATE"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("cover_rate"))+"%");
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("SATIS_QZ")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("REQUEST_SATIS_SCORE")));
|
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 queryEngineerSatisReport(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);
|
paramMap.put("endDay", endDay);
|
paramMap.put("beginTime", beginDay+"666666");
|
paramMap.put("endTime", endDay+"666666");
|
|
String sql = " select c.SATIS_BUSTYPE from KPI_SATIS_TEMPLATE t,KPI_CONFIG c "
|
+ " where t.ID = c.TEMPLATE_ID and t.STATE=1 and c.SATIS_TYPE = 2 ";
|
String businessTypes = baseDao.queryForString(sql,paramMap);
|
if(StringUtil.isNotBlank(businessTypes)){
|
businessTypes = businessTypes + ",'8'";
|
}else{
|
businessTypes = "'8'";
|
}
|
|
//定义报表变量
|
List<String> lebalList = new ArrayList<String>();
|
List<List<String>> dataList = new ArrayList<List<String>>();
|
//查询数据
|
StringBuilder builder = new StringBuilder();
|
builder.append(" select user_name,total_num,IFNULL(invest_num,0) invest_num, ");
|
builder.append(" truncate(IFNULL(invest_num,0)*100/total_num,2) cover_rate,truncate(score,2) score from ( ");
|
builder.append(" select count(b.ID) total_num,user_name,user_id from workflow_base b,( ");
|
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 PROJECT_ID = :cusId AND GROUP_TYPE in (3,4,5) 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.PROJECT_ID=:cusId and b.BUSINESSTYPE in ("+businessTypes+") ");
|
builder.append(" and b.CREATETIME>:beginTime and b.CREATETIME<:endTime GROUP BY t.user_id ");
|
builder.append(" ) mtotal left join ( ");
|
builder.append(" select count(id) invest_num,INVEST_USER_ID,avg(SCORE) score from ORDER_SATIS_INFO ");
|
builder.append(" where INVEST_TIME >:beginDay and INVEST_TIME<:endDay and PROJECT_ID = :cusId group by INVEST_USER_ID ");
|
builder.append(" ) minvest on mtotal.user_id=minvest.INVEST_USER_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){
|
List<String> tempList = new ArrayList<String>();
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("user_name")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("invest_num")));
|
tempList.add(ConvertUtil.obj2StrBlank(base.get("cover_rate"))+"%");
|
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 satisTrendLine(String cusId,String startDate, String endDate) {
|
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
|
Calendar cal = Calendar.getInstance();
|
try {
|
cal.setTime(df.parse(startDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
cal.add(Calendar.MONTH, -1);
|
startDate = df.format(cal.getTime());
|
|
Map chartMap = new HashMap();
|
StringBuilder builder = new StringBuilder();
|
builder.append(" SELECT I.ALL_SCORE,D.SATIS_SCORE,D.SATIS_TYPE,I.MONTH FROM TOTLE_SATIS_DETAIL D ,TOTLE_SATIS_INFO I ");
|
builder.append(" WHERE D.BUS_ID = I.ID AND I.MONTH>=:startDate AND I.MONTH<=:endDate ");
|
if(StringUtil.isNotBlank(cusId)){
|
builder.append(" and I.CUSTOMER_ID=:cusId ");
|
}
|
builder.append(" GROUP BY D.SATIS_TYPE,I.MONTH ORDER BY SATIS_TYPE,MONTH ");
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
|
List<Map> queryList = baseDao.queryForList(builder.toString(), param);
|
builder.setLength(0);
|
Map<String,Object> queryMap = new HashMap<String,Object>();
|
|
if(queryList!=null&&queryList.size()>0){
|
for(Map map:queryList){
|
String key = map.get("SATIS_TYPE").toString()+"_"+map.get("MONTH").toString();
|
queryMap.put(key, map);
|
}
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = DateUtil.getIntervalMonths(Long.valueOf(startDate),Long.valueOf(endDate));
|
List<String> categories_format = new ArrayList<String>();
|
List<String> typeList = new ArrayList();
|
typeList.add("1");
|
typeList.add("2");
|
typeList.add("3");
|
typeList.add("4");
|
|
Map tempWholeMap = new HashMap();
|
List tempWholeList = new ArrayList();
|
tempWholeMap.put("name", "整体得分");
|
tempWholeMap.put("type", "spline");
|
//组装数据
|
for(String type:typeList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(type.equals("1")){
|
tempMap.put("name", "响应支持类");
|
}else if(type.equals("2")){
|
tempMap.put("name", "预防性维护类");
|
}else if(type.equals("3")){
|
tempMap.put("name", "驻场服务类");
|
}else if(type.equals("4")){
|
tempMap.put("name", "综合类满意度");
|
}
|
for(String cate:categories){
|
if(type.equals("1")){
|
categories_format.add(DateUtil.format("yyyy-MM", cate));
|
}
|
|
String key = type + "_" + cate;
|
if(queryMap.get(key)!=null){
|
tempList.add(((Map)queryMap.get(key)).get("SATIS_SCORE"));
|
//整体得分
|
if(type.equals("1")){
|
tempWholeList.add(((Map)queryMap.get(key)).get("ALL_SCORE"));
|
}
|
}else{
|
tempList.add(0);
|
//整体得分
|
if(type.equals("1")){
|
tempWholeList.add(0);
|
}
|
}
|
}
|
|
tempMap.put("data", tempList);
|
tempMap.put("type", "column");
|
series.add(tempMap);
|
if(type.equals("4")){
|
tempWholeMap.put("data", tempWholeList);
|
series.add(tempWholeMap);
|
}
|
chartMap.put("series", series);
|
chartMap.put("categories", categories_format);
|
}
|
chartMap.put("nodata", false);
|
}else{
|
chartMap.put("nodata", true);
|
}
|
return chartMap;
|
}
|
|
|
@Override
|
public Map satisCoverLine(String cusId,String startDate, String endDate) {
|
SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
|
Calendar cal = Calendar.getInstance();
|
try {
|
cal.setTime(df.parse(startDate));
|
} catch (ParseException e) {
|
e.printStackTrace();
|
}
|
cal.add(Calendar.MONTH, -1);
|
startDate = df.format(cal.getTime());
|
|
Map chartMap = new HashMap();
|
StringBuilder builder = new StringBuilder();
|
builder.append(" SELECT D.FG_RATE,D.SATIS_TYPE,I.MONTH FROM TOTLE_SATIS_DETAIL D ,TOTLE_SATIS_INFO I ");
|
builder.append(" WHERE D.BUS_ID = I.ID AND I.MONTH>=:startDate AND I.MONTH<=:endDate ");
|
if(StringUtil.isNotBlank(cusId)){
|
builder.append(" and I.CUSTOMER_ID=:cusId ");
|
}
|
builder.append(" GROUP BY D.SATIS_TYPE,I.MONTH ORDER BY SATIS_TYPE,MONTH");
|
|
SqlParameter param = new SqlParameter();
|
param.addValue("cusId", cusId)
|
.addValue("startDate", startDate)
|
.addValue("endDate", endDate);
|
List<Map> queryList = baseDao.queryForList(builder.toString(), param);
|
builder.setLength(0);
|
Map<String,Object> queryMap = new HashMap<String,Object>();
|
|
if(queryList!=null&&queryList.size()>0){
|
for(Map map:queryList){
|
String key = map.get("SATIS_TYPE").toString()+"_"+map.get("MONTH").toString();
|
queryMap.put(key, map);
|
}
|
List<Map> series = new ArrayList<Map>();
|
List<String> categories = DateUtil.getIntervalMonths(Long.valueOf(startDate), Long.valueOf(endDate));
|
List<String> categories_format = new ArrayList<String>();
|
List<String> typeList = new ArrayList();
|
typeList.add("1");
|
typeList.add("2");
|
typeList.add("3");
|
typeList.add("4");
|
|
//组装数据
|
for(String type:typeList){
|
Map tempMap = new HashMap();
|
List tempList = new ArrayList();
|
if(type.equals("1")){
|
tempMap.put("name", "响应支持类");
|
}else if(type.equals("2")){
|
tempMap.put("name", "预防性维护类");
|
}else if(type.equals("3")){
|
tempMap.put("name", "驻场服务类");
|
}else if(type.equals("4")){
|
tempMap.put("name", "综合类满意度");
|
}
|
for(String cate:categories){
|
if(type.equals("1")){
|
categories_format.add(DateUtil.format("yyyy-MM", cate));
|
}
|
String key = type + "_" + cate;
|
if(queryMap.get(key)!=null){
|
tempList.add(((Map)queryMap.get(key)).get("FG_RATE"));
|
}else{
|
tempList.add(0);
|
}
|
}
|
|
tempMap.put("data", tempList);
|
series.add(tempMap);
|
chartMap.put("series", series);
|
chartMap.put("categories", categories_format);
|
}
|
}
|
return chartMap;
|
}
|
|
}
|
