package cn.ksource.web.facade.tj;

import cn.ksource.beans.GG_USER;
import cn.ksource.core.dao.BaseDao;
import cn.ksource.core.dao.SqlParameter;
import cn.ksource.core.util.*;
import cn.ksource.web.Constants;
import cn.ksource.web.entity.LoginEntity;
import cn.ksource.web.service.DataDictionaryService;
import cn.ksource.web.util.ChartUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.text.DecimalFormat;
import java.util.*;

@Service("incidentStatisFacade")
public class IncidentStatisFacadeImpl implements IncidentStatisFacade {

	@Autowired
	private BaseDao baseDao;
	
	@Autowired
	private DataDictionaryService dataDictionaryService;

	//获取查询Map
	private Map getQueryMap(HttpServletRequest request){
		String queryType = request.getParameter("queryType");
		Map queryMap = new HashMap();
		if(queryType.equals("1")){//按事件类型查询
			queryMap.put("col","TYPE_ID");
			queryMap.put("queryType",queryType);
			queryMap.put("dicCategoryKey",Constants.INCIDENT_TYPE);
		}else if(queryType.equals("2")){//按事件来源
			queryMap.put("col","SOURCE_ID");
			queryMap.put("queryType",queryType);
			queryMap.put("dicCategoryKey",Constants.INCIDENT_SOURCE);
		}else if(queryType.equals("3")){//关闭原因
			queryMap.put("col","RESOLVE_TYPE_ID");
			queryMap.put("queryType",queryType);
			queryMap.put("dicCategoryKey",Constants.CLOSE_REASON);
		}else if(queryType.equals("4")){//优先级
			queryMap.put("col","PRIORITY_ID");
			queryMap.put("queryType",queryType);
			queryMap.put("dicCategoryKey",Constants.INCIDENT_EVENT_PRI);
		}else if(queryType.equals("5")){//影响度
			queryMap.put("col","INFLUENCE_ID");
			queryMap.put("queryType",queryType);
			queryMap.put("dicCategoryKey",Constants.EVENT_EFFECT_DG);
		}else if(queryType.equals("6")){//服务级别
			queryMap.put("col","SLA_ID");
			queryMap.put("queryType",queryType);
		}else if(queryType.equals("7")){//响应超时
			queryMap.put("col","ANSWER_TIMEOUT");
			queryMap.put("queryType",queryType);
		}else if(queryType.equals("8")){//处理超时
			queryMap.put("col","DEAL_TIMEOUT");
			queryMap.put("queryType",queryType);
		}
		
		return queryMap;
	}
	
	//获取初始化list
	private List getQueryTypeList(Map queryMap){
		String queryType = queryMap.get("queryType").toString();
		List queryTypeList = new ArrayList<Map>();
		if(queryType.equals("1")||queryType.equals("2")||queryType.equals("3")||queryType.equals("4")
				||queryType.equals("5")){
			queryTypeList = dataDictionaryService.getDataDictionaryByCategoryKey(queryMap.get("dicCategoryKey").toString());
		}else if(queryType.equals("6")){
			String sql = " select id DATAKEY,level_name DATAVALUE from SC_SLA where STATE=1 order by serial asc ";
			queryTypeList = baseDao.queryForList(sql,queryMap);
		}else if(queryType.equals("7")){
			String sql = " select 1 DATAKEY,'响应超时' DATAVALUE union select 2 DATAKEY,'响应及时' DATAVALUE ";
			queryTypeList = baseDao.queryForList(sql);
		}else if(queryType.equals("8")){
			String sql = " select 1 DATAKEY,'处理超时' DATAVALUE union select 2 DATAKEY,'处理及时' DATAVALUE ";
			queryTypeList = baseDao.queryForList(sql);
		}
		return queryTypeList;
	}
	
	
	
	
	@Override
	public List incidentTypePie(HttpServletRequest request) {
		String cusId = request.getParameter("cusId");
		String type = request.getParameter("type");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		if(!StringUtil.notEmptyNum(type)) {
			type = "2";
		}
		if(type.equals("1")) {
			beginTime = beginTime.replace("-", "")+"01";
			endTime = endTime.replace("-", "")+"31";
		} else {
			beginTime = beginTime.replace("-", "");
			endTime = endTime.replace("-", "");
		}
		//获取初始化LIST
		Map queryMap = getQueryMap(request);
		List<Map> list = getQueryTypeList(queryMap);
		List<Map> dataList = getQueryList_Pie(request,queryMap);
		
		List listResult = new ArrayList();
		if(list!=null&&list.size() > 0){
			for(Map resMap:list){
				List pieDate = new ArrayList();
				resMap.put("num", 0);
				if(dataList!=null&&dataList.size()>0){
					for(Map tempMap:dataList){
						if(tempMap.get("COLKEY").toString().equals(resMap.get("DATAKEY").toString())){
							resMap.put("num", tempMap.get("NUM"));
							break;
						}
					}
				}
				pieDate.add(resMap.get("DATAVALUE"));
				pieDate.add(resMap.get("num"));
				listResult.add(pieDate);
			}
		}
        System.out.println("事件类型---------------------->"+JsonUtil.list2Json(listResult));
		return listResult;
	}

	@Override
	public Map incidentTypeLine(HttpServletRequest request) {
		
		String type = request.getParameter("type");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		
		if(!StringUtil.notEmptyNum(type)) {
			type = "2";
		}
		List<String> categories = new ArrayList<String>();//获取X轴数据
		if(type.equals("1")) {
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
		} else {
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
		}
		
		
		Map queryMap = getQueryMap(request);
		List<Map> types = getQueryTypeList(queryMap);
		List<Map> typeMsg = getQueryList_Line(request,queryMap);
		
		Map cacheMap = new HashMap();
		if(null!=typeMsg && typeMsg.size()>0) {
			for(Map  msg : typeMsg) {
				String typeId = ConvertUtil.obj2StrBlank(msg.get("COLKEY"));
				int num = ConvertUtil.obj2Int(msg.get("NUM"));
				String time = ConvertUtil.obj2StrBlank(msg.get("DC_TIME"));
				String key = time + "-" + typeId;
				cacheMap.put(key, num);
			}
		}
		
		
		List resultList = new ArrayList();
		
		if(null!=types && types.size()>0) {
			for(Map t : types) {
				Map newMap = new HashMap();
				String key = ConvertUtil.obj2StrBlank(t.get("DATAKEY"));
				String name = ConvertUtil.obj2StrBlank(t.get("DATAVALUE"));
				
				newMap.put("name", name);
				List list = new ArrayList();
				for(String cate : categories) {
					String unitKey = cate + "-" + key;
					if(cacheMap.containsKey(unitKey)) {
						list.add(cacheMap.get(unitKey));
					} else {
						list.add(0);
					}
				}
				newMap.put("data", list);
				resultList.add(newMap);
			}
		}
		
		//System.out.println(JsonUtil.list2Json(resultList));
		Map resultMap = new HashMap();
		resultMap.put("cate", categories);
		resultMap.put("data", resultList);
		resultMap.put("step", Math.round(categories.size()/8));
		
		System.out.println("line-------------->"+JsonUtil.map2Json(resultMap));
		return resultMap;
	}
	
	//获取饼状图查询结果
	private List getQueryList_Pie(HttpServletRequest request,Map queryMap){
		String cusId = request.getParameter("cusId");
		String type = request.getParameter("type");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		List<Map> queryList = new ArrayList<Map>();
		Map paramMap = new HashMap();
		StringBuilder buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_INCIDENT C WHERE 1=1 ");
		if(StringUtil.notEmpty(cusId) ) {
			buffer.append(" AND CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId ");
			paramMap.put("cusId", cusId);
			paramMap.put("cusId", cusId);
		} 
		if(StringUtil.notEmpty(type)) {
			buffer.append(" AND C.CREATE_TIME >= :beginTime AND C.CREATE_TIME <= :endTime ");
			
			if(type.equals("1")) {
				paramMap.put("beginTime", beginTime+"01000000");
				paramMap.put("endTime", endTime+"31240000");
			} else {
				paramMap.put("beginTime", beginTime+"000000");
				paramMap.put("endTime", endTime+"240000");
			}
			
		}
		buffer.append(" and $KEY is not null ");
		buffer.append(" GROUP BY $KEY ");
		
		String sql = buffer.toString().replace("$KEY", queryMap.get("col").toString());
		List<Map> dataList = baseDao.queryForList(sql, paramMap);
		return dataList;
	}
	
	//获取线形图查询结果
	private List getQueryList_Line(HttpServletRequest request,Map queryMap){
		String cusId = request.getParameter("cusId");
		String type = request.getParameter("type");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		
		Map paramMap = new HashMap();
		StringBuilder builder = new StringBuilder("");
		if(type.equals("2")) {
			builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m-%d') AS DC_TIME  FROM SC_WORKFLOW_INCIDENT C WHERE 1=1 ");
		} else {
			builder = builder.append("SELECT $KEY COLKEY,COUNT(C.TYPE_ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m') AS DC_TIME FROM SC_WORKFLOW_INCIDENT C WHERE 1=1 ");
		}
		
		if(StringUtil.notEmpty(cusId) ) {
			builder.append(" AND C.CUSTOMER_ID = :cusId AND C.CUSTOMER_ID = :cusId ");
			paramMap.put("cusId", cusId);
		} 
		if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)) {
			beginTime = beginTime.replaceAll("-", "");
			endTime = endTime.replaceAll("-", "");
			builder.append(" AND C.CREATE_TIME>=:beginTime AND C.CREATE_TIME <=:endTime ");
			if(type.equals("1")) {
				paramMap.put("beginTime", beginTime+"01000000");
				paramMap.put("endTime", endTime+"31240000");
			} else {
				paramMap.put("beginTime", beginTime+"000000");
				paramMap.put("endTime", endTime+"240000");
			}
		}
		builder.append(" and $KEY is not null ");
		builder.append(" GROUP BY $KEY,DC_TIME ");
		String sql = builder.toString().replace("$KEY", queryMap.get("col").toString());
		return baseDao.queryForList(sql,paramMap);
	}
	
	@Override
	public Map incidentTimeLine(HttpServletRequest request){
		String selDay = request.getParameter("selDay");
		String cusId = request.getParameter("cusId");
		
		StringBuilder sql = new StringBuilder();
		Map paramMap = new HashMap();
		sql.append(" SELECT HOUR(HAPPEN_TIME)  MHOUR,count(ID) NUM FROM SC_WORKFLOW_INCIDENT C");
		sql.append(" where DATE_FORMAT(HAPPEN_TIME,'%Y%m%d')=:selDay and HOUR(HAPPEN_TIME)>=7  and HOUR(HAPPEN_TIME)<=18   ");
		if(StringUtil.notEmpty(cusId) ) {
			sql.append(" AND CUSTOMER_ID = :cusId  ");
			paramMap.put("cusId", cusId);
			paramMap.put("cusId", cusId);
		} 
		sql.append(" GROUP BY MHOUR ");
		
		paramMap.put("selDay", selDay);
		List<Map> list = baseDao.queryForList(sql.toString(),paramMap);
		Map categoriesMap = new HashMap();
		categoriesMap.put("7:00-9:00", 0);
		categoriesMap.put("9:00-11:00", 0);
		categoriesMap.put("11:00-13:00", 0);
		categoriesMap.put("13:00-15:00", 0);
		categoriesMap.put("15:00-17:00", 0);
		categoriesMap.put("17:00-18:00", 0);
		//x轴
		List<String> categoriesList = new ArrayList<String>();
		categoriesList.add("7:00-9:00");
		categoriesList.add("9:00-11:00");
		categoriesList.add("11:00-13:00");
		categoriesList.add("13:00-15:00");
		categoriesList.add("15:00-17:00");
		categoriesList.add("17:00-18:00");
		
		
		if(list!=null&&list.size()>0){
			for(Map map:list){
				String hour = map.get("MHOUR").toString();
				if(hour.equals("7")||hour.equals("8")){
					Integer sum = Integer.valueOf(categoriesMap.get("7:00-9:00").toString())+Integer.valueOf(map.get("NUM").toString());
					categoriesMap.put("7:00-9:00", sum);
				}else if(hour.equals("9")||hour.equals("10")){
					Integer sum = Integer.valueOf(categoriesMap.get("9:00-11:00").toString())+Integer.valueOf(map.get("NUM").toString());
					categoriesMap.put("9:00-11:00", sum);
				}else if(hour.equals("11")||hour.equals("12")){
					Integer sum = Integer.valueOf(categoriesMap.get("11:00-13:00").toString())+Integer.valueOf(map.get("NUM").toString());
					categoriesMap.put("11:00-13:00", sum);
				}else if(hour.equals("13")||hour.equals("14")){
					Integer sum = Integer.valueOf(categoriesMap.get("13:00-15:00").toString())+Integer.valueOf(map.get("NUM").toString());
					categoriesMap.put("13:00-15:00", sum);
				}else if(hour.equals("15")||hour.equals("16")){
					Integer sum = Integer.valueOf(categoriesMap.get("15:00-17:00").toString())+Integer.valueOf(map.get("NUM").toString());
					categoriesMap.put("15:00-17:00", sum);
				}else if(hour.equals("17")||hour.equals("18")){
					Integer sum = Integer.valueOf(categoriesMap.get("17:00-18:00").toString())+Integer.valueOf(map.get("NUM").toString());
					categoriesMap.put("17:00-18:00", sum);
				}
			}
		}
		List dataList = new ArrayList();
		dataList.add(categoriesMap.get("7:00-9:00"));
		dataList.add(categoriesMap.get("9:00-11:00"));
		dataList.add(categoriesMap.get("11:00-13:00"));
		dataList.add(categoriesMap.get("13:00-15:00"));
		dataList.add(categoriesMap.get("15:00-17:00"));
		dataList.add(categoriesMap.get("17:00-18:00"));
		List seriesList = new ArrayList();
		Map seriesMap = new HashMap();
		seriesMap.put("name","数量");
		seriesMap.put("data",dataList);
		seriesList.add(seriesMap);
		Map resMap = new HashMap();
		resMap.put("categories", categoriesList);
		resMap.put("series", seriesList);
		
		System.out.println("发生时间统计-------------->"+JsonUtil.map2Json(resMap));
		return resMap;
	}
	
	
	@Override
	public Map incidentDealRateColumn(HttpServletRequest request){
		String selDay = request.getParameter("selDay");
		String cusId = request.getParameter("cusId");
		String queryType = request.getParameter("queryType");

		StringBuilder sql = new StringBuilder();
		Map paramMap = new HashMap();
		sql.append(" select CUSTOMER_ID,floor((count(ID)/(select count(ID) from workflow_base c where businesstype = 8 ))*100) RATE ");
		sql.append(" from workflow_base C where businesstype = 8  and wfstate=2 and CUSTOMER_ID is not null  ");
		sql.append(" and CURRENT_NODE_ID = :nodeId  ");
		if(queryType.equals("frontLine")){
			paramMap.put("nodeId", Constants.KFRY);
		}else{
			paramMap.put("nodeId", Constants.YXCL);
		}
		if(StringUtil.notEmpty(cusId)) {
			sql.append(" AND CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId ");
			paramMap.put("cusId", cusId);
			
		}
		sql.append(" GROUP BY CUSTOMER_ID "); 
		List<Map> queryList = baseDao.queryForList(sql.toString(), paramMap);
		List<String> categories = new ArrayList<String>();//X轴类别
		List series = new ArrayList();
		Map seriesMap = new HashMap();
		List dataList = new ArrayList();
		seriesMap.put("name", "解决率");
		seriesMap.put("data", dataList);
		series.add(seriesMap);
		Map resMap = new HashMap();
		resMap.put("categories", categories);
		resMap.put("series", series);
		return resMap;
	}

	@Override
	public Map incidentDealRateLine(HttpServletRequest request) {
		String selDay = request.getParameter("selDay");
		String cusId = request.getParameter("cusId");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		
		List<String> categories = new ArrayList<String>();//获取X轴数据
		categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
		
		StringBuilder sql = new StringBuilder();
		Map paramMap = new HashMap();
		sql.append(" select DATE_FORMAT(CREATETIME,'%Y%m%d') MDAY,CURRENT_NODE_ID,floor((count(ID)/(select count(ID) from workflow_base c where businesstype = 8 ))*100) RATE ");
		sql.append(" from workflow_base C where businesstype = 8  and wfstate=2 and CUSTOMER_ID is not null  ");
		sql.append(" and CURRENT_NODE_ID in ('"+Constants.KFRY+"','"+Constants.YXCL+"')  ");
		if(StringUtil.notEmpty(cusId) ) {
			sql.append(" AND CUSTOMER_ID = :cusId ");
			paramMap.put("cusId", cusId);
		} 
		sql.append(" GROUP BY CURRENT_NODE_ID,MDAY "); 
		System.out.println("sql is ---------------"+sql.toString());
		List<Map> queryList = baseDao.queryForList(sql.toString(), paramMap);
		
		List series = new ArrayList();//最终数据集合
		
		Map tempMap = new HashMap();
		tempMap.put(Constants.KFRY, "服务台解决率");
		tempMap.put(Constants.YXCL, "一线解决率");
		Iterator it=tempMap.keySet().iterator(); 
		while(it.hasNext()){
			String key = it.next().toString() ;    
		    String value = tempMap.get(key).toString(); 
		    Map seriesMap = new HashMap();
		    List dataList = new ArrayList();
		    seriesMap.put("name", value);
			for(String categorie:categories){
				Integer rate = 0;
				for(Map queryMap:queryList){
					if(queryMap.get("CURRENT_NODE_ID").toString().equals(key)&&categorie.equals(queryMap.get("MDAY").toString())){
						rate = Integer.valueOf(queryMap.get("RATE").toString());
						break;
					}
				}
				dataList.add(rate);
			}
			seriesMap.put("data", dataList);
			series.add(seriesMap);
		}
		
		Map resMap = new HashMap();
		resMap.put("categories", categories);
		resMap.put("series", series);
		resMap.put("step", Math.round(categories.size()/8));
		return resMap;
	}

	@Override
	public Map incidentTotalChart(String cusId,String cusName,String beginTime,String endTime) {
		//获取参数
		beginTime = beginTime + "000000";
		endTime = endTime + "666666";
		
		List<Map> typeList = getDicList(Constants.INCIDENT_TYPE);
		List<Map> sourceList = getDicList(Constants.INCIDENT_SOURCE);
		List<Map> closeList = getDicList(Constants.CLOSE_REASON);
		List<Map> priList = getDicList(Constants.INCIDENT_EVENT_PRI);
		List<Map> effectList = getDicList(Constants.EVENT_EFFECT_DG);
		List<Map> levelList = baseDao.queryForList(" select LEVEL_NAME labelName from SC_SLA where state=1 order by SERIAL asc  ");
		List<Map> timeoutList = baseDao.queryForList(" select '超时' labelName union select '正常' labelName ");
		List<Map> resolveList = getDicList(Constants.RESOLVE_WAY);
		List<Map> stateList = baseDao.queryForList(" select '待响应' labelName union select '处理中' labelName union select '已完成' labelName ");
		String blankKey = "";
		String blankSql = "";
		String tempSql = "";
		StringBuilder sql = new StringBuilder();
		sql.append(" select mtotal.total_num,mtotal.mdate,mtype.type_num,msource.source_num,mclose.close_num,mpri.pri_num,meffect.effect_num, ");
		sql.append(" mlevel.level_num,manswer.answer_num,mdeal.deal_num,mresolve.resolve_num,mstate.state_num ");
		sql.append(" from "); 
		sql.append(" ( "); 
		sql.append("	select count(ID) total_num,substr(HAPPEN_TIME||'',1,8)  mdate from SC_WORKFLOW_INCIDENT  ");
		sql.append("	where  HAPPEN_TIME>=:beginTime and HAPPEN_TIME<=:endTime  "); 
		if(StringUtil.isNotBlank(cusId)){
			sql.append("and CUSTOMER_ID=:cusId  "); 
		}
		sql.append("	group by substr(HAPPEN_TIME||'',1,8)  ");
		
		sql.append(" ) mtotal "); 
		//事件类型
		sql = getPartSql(sql,"type_num",Constants.INCIDENT_TYPE,blankSql,"TYPE_ID","mtype","1",cusId);
		//事件来源
		sql = getPartSql(sql,"source_num",Constants.INCIDENT_SOURCE,blankSql,"SOURCE_ID","msource","1",cusId);
		//关闭原因
		sql = getPartSql(sql,"close_num",Constants.CLOSE_REASON,blankSql,"RESOLVE_TYPE_ID","mclose","1",cusId);
		//优先级
		sql = getPartSql(sql,"pri_num",Constants.INCIDENT_EVENT_PRI,blankSql,"PRIORITY_ID","mpri","1",cusId);
		//影响度
		sql = getPartSql(sql,"effect_num",Constants.EVENT_EFFECT_DG,blankSql,"INFLUENCE_ID","meffect","1",cusId);
		//服务级别
		tempSql = " select id DATAKEY,SERIAL ordernum from SC_SLA where STATE=1  ";
		sql = getPartSql(sql,"level_num",blankKey,tempSql,"SLA_ID","mlevel","1",cusId);
		//响应时间
		tempSql = " select 1 DATAKEY,1 ordernum union select 2 DATAKEY,2 ordernum  ";
		sql = getPartSql(sql,"answer_num",blankKey,tempSql,"ANSWER_TIMEOUT","manswer","1",cusId);
		//解决时间
		sql = getPartSql(sql,"deal_num",blankKey,tempSql,"DEAL_TIMEOUT","mdeal","1",cusId);
		//解决途径
		sql = getPartSql(sql,"resolve_num",Constants.RESOLVE_WAY,blankSql,"RESOLVE_TYPE_ID","mresolve","1",cusId);
		//事件状态
		tempSql = " select 1 DATAKEY,1 ordernum union select 2 DATAKEY,2 ordernum  union select 3 DATAKEY,3 ordernum ";
		sql = getPartSql(sql,"state_num",blankKey,tempSql,"FLOWSTATE","mstate","2",cusId);
		
		Map param = new HashMap();
		param.put("cusId", cusId);
		param.put("beginTime", beginTime);
		param.put("endTime", endTime);
		List<Map> resList = baseDao.queryForList(sql.toString(),param); 
		Map resMap = new HashMap();
		resMap.put("typeList", typeList);
		resMap.put("sourceList", sourceList);
		resMap.put("closeList", closeList);
		resMap.put("priList", priList);
		resMap.put("effectList", effectList);
		resMap.put("levelList", levelList);
		resMap.put("timeoutList", timeoutList);
		resMap.put("resolveList", resolveList);
		resMap.put("stateList", stateList);
		resMap.put("resList", resList);
		resMap.put("cusName", cusName);
		
		return resMap;
	}
	
	/**
	 * 获取组装sql
	 * @param sql
	 * @param categoryKey 数据字典分类key
	 * @param tempSql 临时sql
	 * @param column 列名称
	 * @param tempTableName 临时表名称
	 * @param flag 标示
	 * @return 组装后sql
	 */
	private StringBuilder getPartSql(StringBuilder sql,String numName,String categoryKey,String tempSql,
			String column,String tempTableName,String flag,String cusId){
		sql.append(" left join  ");
		sql.append(" (  ");
		sql.append("  select mdate,WM_CONCAT(num) "+numName+" from ");
		sql.append("  (	");
		sql.append("  	select count(i.ID)  num,mdate,datakey,ordernum from ");
		sql.append("  	( ");
		sql.append("  		select DATAKEY,mdate,ordernum from ");
		sql.append("  		(select substr(HAPPEN_TIME||'',1,8) mdate  from SC_WORKFLOW_INCIDENT group by substr(HAPPEN_TIME||'',1,8) ) d,");
		sql.append("  		( ");
		if(StringUtil.isNotBlank(categoryKey)){
			sql = getDicSql(sql,categoryKey);
		}else{
			sql.append(tempSql);
		}
		sql.append("  		) c ");
		sql.append("  	) b ");
		sql.append("  	left join  ");
		if(flag.equals("1")){
			sql.append("( select * from SC_WORKFLOW_INCIDENT   ");
			if(StringUtil.isNotBlank(cusId)){
				sql.append(" where CUSTOMER_ID = :cusId  ");
			}
			sql.append(" ) ");
		}else{
			sql = getStateSql(sql,cusId);
		}
		sql.append(" i on i."+column+" = b.DATAKEY and substr(HAPPEN_TIME||'',1,8)=b.mdate ");
		sql.append(" 	group by b.mdate,b.datakey,b.ordernum order by b.ordernum");
		sql.append(" ) t group by t.mdate ");
		sql.append(" ) "+tempTableName);
		sql.append(" on "+tempTableName+".mdate = mtotal.mdate ");
		return sql;
	}
	
	/**
	 * 获取数据字典查询sql
	 * @param sql
	 * @param categoryKey
	 * @return
	 */
	private StringBuilder getDicSql(StringBuilder sql,String categoryKey){
		sql.append(" select d.DATAKEY,d.ordernum from CONFIG_DATA_DICTIONARY d ");
		sql.append(" inner join CONFIG_DATA_DICTIONARY_CATEGORY cate  ");
		sql.append(" on d.CATEGORYID = cate.ID  ");
		sql.append(" where cate.CATEGORYKEY='"+categoryKey+"' order by ordernum");
		return sql;
	}
	
	/**
	 * 获取事件状态sql
	 * @param sql
	 * @return
	 */
	private StringBuilder getStateSql(StringBuilder sql,String cusId){
		sql.append(" (");
		sql.append("  select c.CUSTOMER_ID,c.id,n.FLOWSTATE,c.HAPPEN_TIME,b.WFSTATE from SC_WORKFLOW_INCIDENT c  ");
		sql.append("  inner join workflow_base b on c.flow_id = b.id and b.wfstate<>3  ");
		sql.append("  inner JOIN workflow_node n on b.CURRENT_NODE_ID = n.id  ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append("  where c.CUSTOMER_ID = :cusId and c.STATE !=10");
		}
		sql.append(" )");
		return sql;
	}
	
	
	private List<Map> getDicList(String categoryKey){
		String sql = " select d.DATAVALUE labelName from CONFIG_DATA_DICTIONARY d  " +
					" inner join CONFIG_DATA_DICTIONARY_CATEGORY c on " +
					" d.CATEGORYID = c.ID where c.CATEGORYKEY=:categoryKey order by d.ordernum ASC";
		Map param = new HashMap();
		param.put("categoryKey",categoryKey);
		return baseDao.queryForList(sql,param);
	}

	@Override
	public List queryIncidentStatis(String cusId,String subCustomerId,String beginTime,String endTime,String jjState,String csState,String jjrId) {
		beginTime = beginTime + "000000";
		endTime = endTime + "240000";
		Map param= new HashMap();
		param.put("cusId", cusId);
		param.put("cusId", cusId);
		param.put("beginTime", beginTime);
		param.put("endTime", endTime);
		param.put("subCustomerId", subCustomerId);
		param.put("jjrId", jjrId);
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT A.*,B.WFSTATE,B.CURRENT_DEALER_NAME FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B WHERE 1=1 AND A.ID=B.BUSINESS_ID");
		if(StringUtil.notEmpty(cusId) ){
			sql.append(" AND A.CUSTOMER_ID=:cusId ");
		}
		if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)){
			sql.append(" AND A.CREATE_TIME>:beginTime AND A.CREATE_TIME<:endTime");
		}
		if(StringUtil.notEmpty(subCustomerId)){
			sql.append(" AND A.SUB_CUSTOMER_ID=:subCustomerId");
		}
		if(StringUtil.notEmpty(jjrId)){
			sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
		}
		if(StringUtil.notEmpty(csState)){
			if(csState.equals("1")){
				sql.append(" AND (A.ANSWER_TIMEOUT=1 or A.DEAL_TIMEOUT=1) ");
			}else if(csState.equals("2")){
				sql.append(" AND A.ANSWER_TIMEOUT=2 AND A.DEAL_TIMEOUT=2 ");
			}
		}
		if(StringUtil.notEmpty(jjState)){
			if(jjState.equals("1")){
				sql.append(" AND B.WFSTATE in(2,4) ");
			}else if(jjState.equals("2")){
				sql.append(" AND B.WFSTATE=1 ");
			}
		}
		List incidentList=baseDao.queryForList(sql.toString(), param);
		return incidentList;
	}
	@Override
	public List queryIncidentStatisTwo(String cusId,String subCustomerId,String beginTime,String endTime,String jjState,String csState,String jjrId,String type_id) {
		beginTime = beginTime + "000000";
		endTime = endTime + "240000";
		Map param= new HashMap();
		param.put("cusId", cusId);
		param.put("cusId", cusId);
		param.put("beginTime", beginTime);
		param.put("endTime", endTime);
		param.put("subCustomerId", subCustomerId);
		param.put("jjrId", jjrId);
		param.put("type_id", type_id);
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT A.*,B.WFSTATE,B.CURRENT_DEALER_NAME FROM SC_WORKFLOW_INCIDENT A,WORKFLOW_BASE B WHERE 1=1 AND A.ID=B.BUSINESS_ID");
		if(StringUtil.notEmpty(cusId) ){
			sql.append(" AND A.CUSTOMER_ID=:cusId ");
		}
		if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)){
			sql.append(" AND A.CREATE_TIME>:beginTime AND A.CREATE_TIME<:endTime");
		}
		if(StringUtil.notEmpty(subCustomerId)){
			sql.append(" AND A.SUB_CUSTOMER_ID=:subCustomerId");
		}
		if(StringUtil.notEmpty(jjrId)){
			sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
		}
		if(StringUtil.notEmpty(type_id)){
			sql.append(" AND A.TYPE_ID=:type_id");
		}
		if(StringUtil.notEmpty(csState)){
			if(csState.equals("1")){
				sql.append(" AND (A.ANSWER_TIMEOUT=1 or A.DEAL_TIMEOUT=1) ");
			}else if(csState.equals("2")){
				sql.append(" AND A.ANSWER_TIMEOUT=2 AND A.DEAL_TIMEOUT=2 ");
			}
		}
		if(StringUtil.notEmpty(jjState)){
			if(jjState.equals("1")){
				sql.append(" AND B.WFSTATE in(2,4) ");
			}else if(jjState.equals("2")){
				sql.append(" AND B.WFSTATE=1 ");
			}
		}
		List incidentList=baseDao.queryForList(sql.toString(), param);
		return incidentList;
	}

	/**
	 * 事件达成率统计
	 */
	@Override
	public List<Map> queryIncidentDclStatis(String cusId,String subCustomerId,String beginTime,String endTime) {
		
		Map paramMap = new HashMap();
		
		//通过项目编号，查询该项目的所有的服务目录
		String sql = "SELECT COUNT(ID) AS NUM,THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME FROM SC_WORKFLOW_INCIDENT WHERE state!=10 and CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId AND CREATE_TIME > :beginTime AND CREATE_TIME <= :endTime ";
		if(StringUtil.notEmpty(subCustomerId)) {
			sql+= " AND SUB_CUSTOMER_ID = :subCustomerId ";
			paramMap.put("subCustomerId", subCustomerId);
		}
		
		StringBuilder builder1 = new StringBuilder(sql);
		StringBuilder builder2 = new StringBuilder(sql);
		StringBuilder builder3 = new StringBuilder(sql);
		StringBuilder builder4 = new StringBuilder(sql);
		
		
		String endSql = " GROUP BY THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME ";
		
		builder1.append(endSql);
		
		builder2.append(" AND ANSWER_TIMEOUT = 1 ").append(endSql);
		
		builder3.append(" AND DEAL_TIMEOUT = 1 ").append(endSql);
		
		builder4.append(" AND ANSWER_TIMEOUT = 2 AND DEAL_TIMEOUT = 2 ").append(endSql);
		
		
		
		paramMap.put("cusId", cusId);
		paramMap.put("cusId", cusId);
		paramMap.put("beginTime", beginTime+"000000");
		paramMap.put("endTime", endTime+"240000");
		
		
		List<Map> list1 = baseDao.queryForList(builder1.toString(),paramMap);
		
		List<Map> list2 = baseDao.queryForList(builder2.toString(),paramMap);
		
		List<Map> list3 = baseDao.queryForList(builder3.toString(),paramMap);
		
		List<Map> list4 = baseDao.queryForList(builder4.toString(),paramMap);
		
		
		if(null!=list1 && list1.size()>0) {
			Map map2 = new HashMap();
			for(Map map : list2) {
				map2.put(map.get("THIRD_CATEGORY_ID"), map);
			}
			
			Map map3 = new HashMap();
			for(Map map : list3) {
				map3.put(map.get("THIRD_CATEGORY_ID"), map);
			}
			
			Map map4 = new HashMap();
			for(Map map : list4) {
				map4.put(map.get("THIRD_CATEGORY_ID"), map);
			}
			
			for(Map map : list1) {
				String thirdCategoryId = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				if(map2.containsKey(thirdCategoryId)) {
					Map m = (Map)map2.get(thirdCategoryId);
					int num = ConvertUtil.obj2Int(m.get("NUM"));
					map.put("xy", num);
				} else {
					map.put("xy", 0);
				}
				
				if(map3.containsKey(thirdCategoryId)) {
					Map m = (Map)map3.get(thirdCategoryId);
					int num = ConvertUtil.obj2Int(m.get("NUM"));
					System.out.println("num:"+num);
					map.put("cl", num);
				} else {
					map.put("cl", 0);
				}
				
				double jj = 0;
				
				if(map4.containsKey(thirdCategoryId)) {
					Map m = (Map)map4.get(thirdCategoryId);
					int num = ConvertUtil.obj2Int(m.get("NUM"));
					
					jj = ConvertUtil.obj2Double(m.get("NUM"));
					
					map.put("jj", num);
				} else {
					map.put("jj", 0);
				}
				
				
				double total = ConvertUtil.obj2Double(map.get("NUM"));
				
				
				double jjl = NumberUtil.div(jj, total, 3);
				
				//jjl = NumberUtil.mul(jjl, ConvertUtil.obj2Double(100));
				
				map.put("jjl", jjl);
				
			}
			
		}
		
		System.out.println(JsonUtil.list2Json(list1));
		return list1;
	}

	@Override
	public Map queryOrderClStatis(HttpServletRequest request,int type) {
		Map resultMap = new HashMap();
		String cusId = request.getParameter("cusId");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String subCustomerId = request.getParameter("sub_customer_id");
		StringBuilder builder1 = new StringBuilder("SELECT COUNT(A.ID) AS NUM,B.THIRD_CATEGORY_ID,B.THIRD_CATEGORY_NAME FROM WORKFLOW_BASE A,SC_WORKFLOW_INCIDENT B WHERE A.BUSINESS_ID = B.ID AND A.WFSTATE != 5 AND A.BUSINESSTYPE = :businessType AND A.CUSTOMER_ID = :cusId AND A.CUSTOMER_ID = :cusId AND B.CREATE_TIME > :beginTime AND B.CREATE_TIME <= :endTime ");
		StringBuilder builder2 = new StringBuilder("SELECT COUNT(A.ID) AS NUM,B.THIRD_CATEGORY_ID,A.WFSTATE FROM WORKFLOW_BASE A,SC_WORKFLOW_INCIDENT B WHERE A.BUSINESS_ID = B.ID AND A.WFSTATE != 5 AND A.BUSINESSTYPE = :businessType AND A.CUSTOMER_ID = :cusId AND A.CUSTOMER_ID = :cusId AND B.CREATE_TIME > :beginTime AND B.CREATE_TIME <= :endTime ");
		
		Map paramMap = new HashMap();
		
		if(StringUtil.notEmpty(subCustomerId)) {
			builder1.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
			builder2.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
			paramMap.put("subCustomerId", subCustomerId);
		}
		
		builder1.append(" GROUP BY B.THIRD_CATEGORY_ID ");
		builder2.append(" GROUP BY B.THIRD_CATEGORY_ID,A.WFSTATE ");
		
		paramMap.put("cusId", cusId);
		paramMap.put("cusId", cusId);
		paramMap.put("beginTime", beginTime+"000000");
		paramMap.put("endTime", endTime+"240000");
		paramMap.put("businessType", type);
		
		
		List<Map> list1 = baseDao.queryForList(builder1.toString(),paramMap);
		
		List<Map> list2 = baseDao.queryForList(builder2.toString(),paramMap);
		
		int total = 0;
		int totaljxz = 0;
		int totalywc = 0;
		int totalygb = 0;
		int totalypj = 0;
		int totalygq = 0;
		
		if(null!=list1 && list1.size()>0) {
			Map map2 = new HashMap();
			for(Map map : list2) {
				String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				String wfstate = ConvertUtil.obj2StrBlank(map.get("WFSTATE"));
				String unitKey = third_category_id+"-"+wfstate;
				String num = ConvertUtil.obj2StrBlank(map.get("NUM"));
				map2.put(unitKey, num);
			}
			
			
			for(Map map : list1) {
				int num = ConvertUtil.obj2Int(map.get("NUM"));
				total += num;
				
				
				String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				String unitKey1 = third_category_id+"-1";
				
				int jxz = 0;
				if(map2.containsKey(unitKey1)) {
					jxz = ConvertUtil.obj2Int(map2.get(unitKey1));
				}
				map.put("jxz", jxz);
				
				totaljxz+=jxz;
				
				int ywc = 0;
				String unitKey2 = third_category_id+"-2";
				if(map2.containsKey(unitKey2)) {
					ywc = ConvertUtil.obj2Int(map2.get(unitKey2));
				}
				map.put("ywc", ywc);
				
				totalywc += ywc;
				
				
				int ygb = 0;
				String unitKey3 = third_category_id+"-3";
				if(map2.containsKey(unitKey3)) {
					ygb = ConvertUtil.obj2Int(map2.get(unitKey3));
				}
				map.put("ygb", ygb);
				
				totalygb += ygb;
				
				int ypj = 0;
				String unitKey4 = third_category_id+"-4";
				if(map2.containsKey(unitKey4)) {
					ypj = ConvertUtil.obj2Int(map2.get(unitKey4));
				}
				map.put("ypj", ypj);
				
				totalypj += ypj;
				
				int ygq = 0;
				String unitKey6 = third_category_id+"-6";
				if(map2.containsKey(unitKey6)) {
					ygq = ConvertUtil.obj2Int(map2.get(unitKey6));
				}
				map.put("ygq", ygq);
				
				totalygq += ygq;
				
			}
		}
		
		resultMap.put("reports", list1);
		resultMap.put("totaljxz", totaljxz);
		resultMap.put("totalywc", totalywc);
		resultMap.put("totalygb", totalygb);
		resultMap.put("totalypj", totalypj);
		resultMap.put("totalygq", totalygq);
		resultMap.put("total", total);
		System.out.println(JsonUtil.map2Json(resultMap));
		return resultMap;
	}
	
	
	//////////////////////////////////////////////////////////////////////////////////////////////////////
	@Override
	public Map queryCloseReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		Map extendMap = new HashMap();
		
		StringBuilder builder = new StringBuilder();
		builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num,IFNULL(close_num,0) close_num from  ");
		builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,\n" +
				"        SECOND_CATEGORY_NAME,\n" +
				"        FIRST_CATEGORY_NAME) mtotal left join  ");
		builder.append(" (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=3 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mclose  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//long startTime = System.currentTimeMillis();
		//查询数据字典
		List<Map> resolveList = dataDictionaryService.getDataDictionaryByCategoryKey("RESOLVE_WAY");
		List<Map> closeList = dataDictionaryService.getDataDictionaryByCategoryKey("CLOSE_REASON");
		//long endTime = System.currentTimeMillis();
		//System.out.println("程序运行时间："+(endTime-startTime)+"ms");
		//查询解决方式,关闭原因
		builder.setLength(0);
		builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',RESOLVE_TYPE_ID) m_key from SC_WORKFLOW_INCIDENT   ");
		builder.append(" where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by RESOLVE_TYPE_ID,THIRD_CATEGORY_ID   ");
		List<Map> extendList = baseDao.queryForList(builder.toString(),paramMap);
		if(extendList!=null&&extendList.size()>0){
			for(Map extend : extendList){
				extendMap.put(extend.get("m_key"), extend.get("m_num"));
			}
		}
		
		//组织表头
		lebalList.add("服务目录");
		lebalList.add("事件数");
		lebalList.add("已取消事件");
		for(Map map:resolveList){
			lebalList.add(map.get("DATAVALUE").toString());
		}
		for(Map map:closeList){
			lebalList.add(map.get("DATAVALUE").toString());
		}
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("close_num")));
				//组织解决方式数据
				for(Map map:resolveList){
					String key = base.get("THIRD_CATEGORY_ID")+"_"+map.get("DATAKEY").toString();
					if(extendMap.get(key)!=null){
						tempList.add(ConvertUtil.obj2StrBlank(extendMap.get(key)));
					}else{
						tempList.add("0");
					}
				}
				//组织关闭原因数据
				for(Map map:closeList){
					String key = base.get("THIRD_CATEGORY_ID")+"_"+map.get("DATAKEY").toString();
					if(extendMap.get(key)!=null){
						tempList.add(ConvertUtil.obj2StrBlank(extendMap.get(key)));
					}else{
						tempList.add("0");
					}
				}
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryTypeReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		Map extendMap = new HashMap();
		
		StringBuilder builder = new StringBuilder();
		builder.append(" select mtotal.THIRD_CATEGORY_ID,mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num from  ");
		builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where state!=10 and  CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal   ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//查询数据字典
		List<Map> typeList = dataDictionaryService.getDataDictionaryByCategoryKey("INCIDENT_TYPE");
		//查询解决方式,关闭原因
		builder.setLength(0);
		builder.append(" select count(id) m_num,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID) m_key,THIRD_CATEGORY_NAME,THIRD_CATEGORY_ID,TYPE_ID from SC_WORKFLOW_INCIDENT   ");
		builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by TYPE_ID,THIRD_CATEGORY_ID,CONCAT(THIRD_CATEGORY_ID,'_',TYPE_ID),THIRD_CATEGORY_NAME   ");
		List<Map> extendList = baseDao.queryForList(builder.toString(),paramMap);
		if(extendList!=null&&extendList.size()>0){
			for(Map extend : extendList){
				extendMap.put(extend.get("m_key"), extend.get("m_num"));
			}
		}
		
		//组织表头
		lebalList.add("服务目录");
		for(Map map:typeList){
			lebalList.add(map.get("DATAVALUE").toString());
		}
		lebalList.add("合计");
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				for(Map map:typeList){
					String key = base.get("THIRD_CATEGORY_ID")+"_"+map.get("DATAKEY").toString();
					if(extendMap.get(key)!=null){
						tempList.add(ConvertUtil.obj2StrBlank(extendMap.get(key)));
					}else{
						tempList.add("0");
					}
				}
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryDealTimeReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		Map extendMap = new HashMap();
		
		StringBuilder builder = new StringBuilder();
		builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num,IFNULL(finish_num,0) finish_num,truncate(ifnull(avg_time,0)*3600*1000,0) avg_time,truncate(ifnull(sum_time,0)*3600*1000,0) sum_time from  ");
		builder.append(" (select AVG(a.DEAL_USE_TIME) avg_time,sum(a.DEAL_USE_TIME) sum_time,b.CATEGORY_NAME THIRD_CATEGORY_NAME,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT a,SC_SERVCE_CATEGORY b where a.state!=10 and  a.CUSTOMER_ID=:cusId and a.CREATE_TIME>=:beginDay and a.CREATE_TIME<=:endDay and a.THIRD_CATEGORY_ID=b.id  group by a.THIRD_CATEGORY_ID,b.CATEGORY_NAME ,a.SECOND_CATEGORY_NAME,a.FIRST_CATEGORY_NAME) mtotal left join  ");
		builder.append(" (select THIRD_CATEGORY_NAME,count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and (b.WFSTATE=2 or b.WFSTATE=4)  and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID,i.THIRD_CATEGORY_NAME) mfinish  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//组织表头
		lebalList.add("服务目录");
		lebalList.add("事件总数");
		lebalList.add("已完成数");
		lebalList.add("处理总时长");
		lebalList.add("平均处理时长");
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				DecimalFormat    df   = new DecimalFormat("######0.00");   
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(DateUtil.secToTime(Math.round((Double)base.get("sum_time")))));
				tempList.add(ConvertUtil.obj2StrBlank(DateUtil.secToTime(Math.round((Double)base.get("avg_time")))));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryDealSituationReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		paramMap.put("nodeTemplateId", Constants.YXCL);
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		Map extendMap = new HashMap();
		
		StringBuilder builder = new StringBuilder();
		builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(timeout_num,0) timeout_num,  ");
		builder.append(" IFNULL(pri_num,0) pri_num,IFNULL(ques_num,0) ques_num,IFNULL(avg_time,0) avg_time,truncate(IFNULL(first_num, 0)*100/total_num,2) first_rate,truncate((IFNULL(total_num,0)-IFNULL(timeout_num,0))*100/IFNULL(total_num,0),2) ontime_rate  from  ");
		builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME) mtotal   ");
		builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and n.FLOWSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) manswer  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mactivi  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b where i.FLOW_ID=b.ID and b.WFSTATE in (2,4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfinish  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(id) timeout_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mtimeout  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mtimeout.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(id) pri_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where SLA_NAME='LEVEL1' and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mpri  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mpri.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(id) ques_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where QUESTIONID is not null and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mques  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mques.THIRD_CATEGORY_ID ");
		builder.append(" left join (select truncate(avg(DEAL_USE_TIME)*3600*1000,0) avg_time,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay group by THIRD_CATEGORY_ID) mdeal  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) first_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT i,workflow_base b,workflow_node n where i.FLOW_ID=b.ID and b.CURRENT_NODE_ID=n.ID and (b.WFSTATE=2 or b.WFSTATE=4) and n.NODE_TEMPLATE_ID = :nodeTemplateId and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfirst  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mfirst.THIRD_CATEGORY_ID ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//组织表头
		lebalList.add("服务目录");
		lebalList.add("事件数");
		lebalList.add("待响应");
		lebalList.add("进行中");
		lebalList.add("已完成");
		lebalList.add("超时数");
		lebalList.add("紧急事件");
		lebalList.add("升级问题数");
		lebalList.add("平均处理时长");
		lebalList.add("一线解决率");
		lebalList.add("服务级别达成率");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("answer_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("timeout_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("pri_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("ques_num")));
				
				tempList.add(DateUtil.secToTime(Math.round((Double)base.get("avg_time"))));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("first_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("ontime_rate"))+"%");
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryEngineerDealReport(HttpServletRequest request) {
		String cusId = request.getParameter("cusId");
		String beginDay = request.getParameter("beginDay");
		String endDay = request.getParameter("endDay");
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		paramMap.put("nodeTemplateId", Constants.YXCL);
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		Map extendMap = new HashMap();
		
		StringBuilder builder = new StringBuilder();
		
		builder.append(" select mtotal.user_name,IFNULL(mactivi.num ,0)activi_num,IFNULL(mfinish.num ,0) finish_num,IFNULL(msend.num ,0) send_num, ");
		builder.append(" IFNULL(truncate(mfinish.num*100/mtotal.num,2),0) deal_rate,IFNULL(mtime.deal_time,0) deal_time,IFNULL(mscore.score ,0) score from  ");
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) num,user_name,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
		builder.append(" 		) u WHERE current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID  and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mtotal left join  ");
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) num,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and b.WFSTATE=1 and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mactivi on mtotal.user_id = mactivi.user_id left join  ");
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) num,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mfinish on mtotal.user_id = mfinish.user_id left join  ");
		builder.append(" ( ");
		builder.append(" 	select truncate(avg(TIMESTAMPDIFF(SECOND,b.CREATETIME,b.ENDTIME)*1000),0) deal_time,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mtime on mtotal.user_id = mtime.user_id left join  ");
		builder.append(" ( ");
		builder.append(" 	select truncate(avg(SCORE),2) score,i.user_id from AUDITING_SATIS_INFO i,(  ");
		builder.append(" 		select u.USER_ID from SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		where g.ID = u.GROUP_ID  and CUSTOMER_ID = :cusId  and g.state=1 and u.state=1 group by u.USER_ID ");
		builder.append(" 	) u where i.USER_ID=u.USER_ID ");
		builder.append(" 	and BUS_TYPE=8 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay and CUSTOMER_ID=:cusId ");
		builder.append(" ) mscore on mtotal.user_id = mscore.user_id left join  ");
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) num,user_name,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 group by u.USER_ID ");
		builder.append(" 		) u WHERE current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.CURRENT_DEALER_ID<>t.user_id and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) msend on mtotal.user_id = msend.user_id ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//组织表头
		lebalList.add("人员");
		lebalList.add("进行中");
		lebalList.add("已完成");
		lebalList.add("转发数");
		lebalList.add("解决率");
		lebalList.add("平均处理时长");
		lebalList.add("满意度得分");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("user_name")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("send_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("deal_rate"))+"%");
				tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("deal_time"))));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("score")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryServiceDealReport(HttpServletRequest request) {
		
		String cusId = request.getParameter("cusId");
		String beginDay = request.getParameter("beginDay");
		String endDay = request.getParameter("endDay");
		
		//String deskId = Constants.getDeskTop(Constants.ZFWT_DESK);
		String deskId = "";
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("cusId", cusId);
		paramMap.put("deskId", deskId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		Map extendMap = new HashMap();
		
		StringBuilder builder = new StringBuilder();
		
		builder.append(" select mtotal.user_name,IFNULL(create_num ,0) create_num,IFNULL(finish_num ,0) finish_num,truncate(IFNULL(finish_num ,0)*100/total_num,2) deal_rate, ");
		builder.append(" IFNULL(forward_num ,0) forward_num,IFNULL(repeat_num,0) repeat_num,IFNULL(error_num,0) error_num,IFNULL(invest_num ,0) invest_num from  ");
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) total_num,user_name,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
		builder.append(" 			union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 			union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 		) u WHERE current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mtotal left join  ");
		//创建事件数
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) create_num,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1 group by u.USER_ID ");
		builder.append(" 		union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 		union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 	) t where b.CREATERID = t.USER_ID and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mcreate on mtotal.user_id = mcreate.user_id left join  ");
		//完成事件数
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) finish_num,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1  group by u.USER_ID ");
		builder.append(" 		union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 		union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4)   and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mfinish on mtotal.user_id = mfinish.user_id left join  ");
		//转发数
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) forward_num,user_id from workflow_base b ,  ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1  group by u.USER_ID ");
		builder.append(" 			union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 			union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 		) u WHERE current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.CURRENT_DEALER_ID<>t.user_id and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mforward on mtotal.user_id = mforward.user_id left join  ");
		//重复事件数
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) repeat_num,user_id from workflow_base b , SC_WORKFLOW_INCIDENT i, ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1  group by u.USER_ID ");
		builder.append(" 			union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 			union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 		) u WHERE current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.id=i.FLOW_ID and i.RESOLVE_TYPE_ID ='SJCF' and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mrepeat on mtotal.user_id = mrepeat.user_id left join  ");	
		//消失，误报
		builder.append(" ( ");
		builder.append(" 	select count(b.ID) error_num,user_id from workflow_base b , SC_WORKFLOW_INCIDENT i, ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1  group by u.USER_ID ");
		builder.append(" 			union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 			union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 		) u WHERE current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.id=i.FLOW_ID and (i.RESOLVE_TYPE_ID ='XS' or i.RESOLVE_TYPE_ID ='WB') and b.BUSINESSTYPE=8 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) merror on mtotal.user_id = merror.user_id left join  ");	
		//调查
		builder.append(" ( ");
		builder.append(" 	select count(s.id) invest_num,u.user_id from AUDITING_SATIS_INFO s,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId and g.state=1 and u.state=1 and g.GROUP_TYPE=1  group by u.USER_ID ");
		builder.append(" 			union select id user_id from gg_user where DESKID = :deskId and ZT=1  ");
		builder.append(" 			union select USER_ID from SC_PARTNER_CUSTOMER_SERVICE where DEL_FLAG = 1 and CUSTOMER_ID = :cusId  ");
		builder.append(" 	) u where s.INVEST_USER_ID =u.USER_ID and CREATE_TIME>:beginDay and CREATE_TIME<:endDay and BUS_TYPE=8 and CUSTOMER_ID=:cusId ");
		builder.append(" ) minvest on mtotal.user_id = minvest.user_id  ");
		
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//组织表头
		lebalList.add("人员");
		lebalList.add("创建事件数");
		lebalList.add("处理完成");
		lebalList.add("解决率");
		lebalList.add("转发数");
		//lebalList.add("一次转发成功率");
		lebalList.add("重复事件数");
		lebalList.add("消失/误报");
		lebalList.add("满意度调查数");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("user_name")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("create_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("deal_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("forward_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("repeat_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("error_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("invest_num")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	/**
	 * 将数据字典转化成DataKeyMap
	 * @author chenlong
	 * @param dicList
	 * @return
	 */
	private Map dicListToDataKeyMap(List<Map> dicList){
		Map dataKeyMap = new HashMap();
		for(Map dic:dicList){
			dataKeyMap.put(ConvertUtil.obj2StrBlank(dic.get("DATAKEY")),ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
		}
		return dataKeyMap;
	}
	
	/**
	 * 格式化数据字典
	 * @author chenlong
	 * @param dicList
	 */
	private void formatDicList(List<Map> dicList){
		for(Map dic:dicList){
			dic.put("key", ConvertUtil.obj2StrBlank(dic.get("DATAKEY")));
			dic.put("val", ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
		}
	}
	
	
	
	/**
	 * 组织时间查询条件
	 * @author chenlong
	 * @param frequency
	 * @param sql
	 * @param colName
	 */
	private void getDateWhereSql(String frequency,StringBuilder sql,String colName){
		if(frequency.equals("day")||frequency==null){
			sql.append(" and substr("+colName+"||'',1,8)>=:startDate and substr("+colName+"||'',1,8)<=:endDate  ");
		}else if(frequency.equals("month")){
			sql.append(" and substr("+colName+"||'',1,6)>=:startDate and substr("+colName+"||'',1,6)<=:endDate  ");
		}
	}

	@Override
	public Map incidentTypePie(String frequency,String cusId,String startDate,String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.INCIDENT_TYPE);
		formatDicList(categoryList);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,type_id category  from sc_workflow_incident where 1=1 AND STATE!=10");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by type_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}
	
	@Override
	public Map incidentSourcePie(String frequency, String cusId,String startDate, String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.INCIDENT_SOURCE);
		formatDicList(categoryList);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,source_id category  from sc_workflow_incident where state!=10 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by source_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

	@Override
	public Map incidentCloseCol(String frequency, String cusId,String startDate, String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List dicList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.CLOSE_REASON);
		Map dataKeyMap = dicListToDataKeyMap(dicList);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,resolve_type_id datakey,1 as category  from sc_workflow_incident where resolve_type_id is not null ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by resolve_type_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		
		Map categoryMap = new HashMap();
		categoryMap.put("key", "1");
		categoryMap.put("val", "数量");
		List<Map> categoryList = new ArrayList<Map>();
		categoryList.add(categoryMap);
		return ChartUtil.createHchartLineMap(datas,categoryList,dataKeyMap,0.0);
	}

	@Override
	public Map incidentPriPie(String frequency, String cusId, String startDate,String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.INCIDENT_EVENT_PRI);
		formatDicList(categoryList);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,priority_id category  from sc_workflow_incident where state!=10 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by priority_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

	@Override
	public Map incidentInfluenceCol(String frequency, String cusId,String startDate, String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List dicList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.EVENT_EFFECT_DG);
		Map dataKeyMap = dicListToDataKeyMap(dicList);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,influence_id datakey,1 as category  from sc_workflow_incident where state!=10");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by influence_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		
		Map categoryMap = new HashMap();
		categoryMap.put("key", "1");
		categoryMap.put("val", "数量");
		List<Map> categoryList = new ArrayList<Map>();
		categoryList.add(categoryMap);
		return ChartUtil.createHchartLineMap(datas,categoryList,dataKeyMap,0.0);
	}
	
	
	@Override
	public Map incidentLvPie(String frequency, String cusId, String startDate,String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select id as key,level_name as val from sc_sla where state=1 order by serial asc");
		List<Map> categoryList = baseDao.queryForList(sql.toString(),param); 
		
		sql.setLength(0);
		sql.append(" select count(id) val,sla_id category  from sc_workflow_incident where state!=10 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by sla_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

	@Override
	public Map incidentResponsePie(String frequency, String cusId,String startDate, String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List<Map> categoryList = new ArrayList<Map>();
		for(int i=1;i<=2;i++){
			Map category = new HashMap();
			category.put("key", String.valueOf(i));
			if(i==1){
				category.put("val", "响应超时");
			}else{
				category.put("val", "响应及时");
			}
			categoryList.add(category);
		}
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,answer_timeout category  from sc_workflow_incident where state!=10 and answer_timeout is not null ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by answer_timeout ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

	@Override
	public Map incidentDealPie(String frequency, String cusId,String startDate, String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List<Map> categoryList = new ArrayList<Map>();
		for(int i=1;i<=2;i++){
			Map category = new HashMap();
			category.put("key", String.valueOf(i));
			if(i==1){
				category.put("val", "处理超时");
			}else{
				category.put("val", "处理及时");
			}
			categoryList.add(category);
		}
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,deal_timeout category  from sc_workflow_incident where state!=10 and deal_timeout is not null ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by deal_timeout ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}
	
	@Override
	public Map incidentHappendTimeLine(String frequency, String cusId,String startDate, String endDate) {
		Map chartMap = new HashMap();
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		StringBuilder sql = new StringBuilder();
		sql.append(" select to_number(substr(happen_time||'',9,2)) mhour,count(id) num from sc_workflow_incident c");
		sql.append(" where c.state!=10 and to_number(substr(happen_time||'',9,2))>=7  and to_number(substr(happen_time||'',9,2))<=18   ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by to_number(substr(happen_time||'',9,2)) ");
		
		List<Map> list = baseDao.queryForList(sql.toString(),param);
		Map categoriesMap = new HashMap();
		categoriesMap.put("7:00-9:00", 0);
		categoriesMap.put("9:00-11:00", 0);
		categoriesMap.put("11:00-13:00", 0);
		categoriesMap.put("13:00-15:00", 0);
		categoriesMap.put("15:00-17:00", 0);
		categoriesMap.put("17:00-18:00", 0);
		//x轴
		List<String> categoriesList = new ArrayList<String>();
		categoriesList.add("7:00-9:00");
		categoriesList.add("9:00-11:00");
		categoriesList.add("11:00-13:00");
		categoriesList.add("13:00-15:00");
		categoriesList.add("15:00-17:00");
		categoriesList.add("17:00-18:00");
		
		if(list!=null&&list.size()>0){
			for(Map map:list){
				String hour = map.get("mhour").toString();
				if(hour.equals("7")||hour.equals("8")){
					Integer sum = Integer.valueOf(categoriesMap.get("7:00-9:00").toString())+Integer.valueOf(map.get("num").toString());
					categoriesMap.put("7:00-9:00", sum);
				}else if(hour.equals("9")||hour.equals("10")){
					Integer sum = Integer.valueOf(categoriesMap.get("9:00-11:00").toString())+Integer.valueOf(map.get("num").toString());
					categoriesMap.put("9:00-11:00", sum);
				}else if(hour.equals("11")||hour.equals("12")){
					Integer sum = Integer.valueOf(categoriesMap.get("11:00-13:00").toString())+Integer.valueOf(map.get("num").toString());
					categoriesMap.put("11:00-13:00", sum);
				}else if(hour.equals("13")||hour.equals("14")){
					Integer sum = Integer.valueOf(categoriesMap.get("13:00-15:00").toString())+Integer.valueOf(map.get("num").toString());
					categoriesMap.put("13:00-15:00", sum);
				}else if(hour.equals("15")||hour.equals("16")){
					Integer sum = Integer.valueOf(categoriesMap.get("15:00-17:00").toString())+Integer.valueOf(map.get("num").toString());
					categoriesMap.put("15:00-17:00", sum);
				}else if(hour.equals("17")||hour.equals("18")){
					Integer sum = Integer.valueOf(categoriesMap.get("17:00-18:00").toString())+Integer.valueOf(map.get("num").toString());
					categoriesMap.put("17:00-18:00", sum);
				}
			}
			List dataList = new ArrayList();
			dataList.add(categoriesMap.get("7:00-9:00"));
			dataList.add(categoriesMap.get("9:00-11:00"));
			dataList.add(categoriesMap.get("11:00-13:00"));
			dataList.add(categoriesMap.get("13:00-15:00"));
			dataList.add(categoriesMap.get("15:00-17:00"));
			dataList.add(categoriesMap.get("17:00-18:00"));
			List seriesList = new ArrayList();
			Map seriesMap = new HashMap();
			seriesMap.put("name","数量");
			seriesMap.put("data",dataList);
			seriesList.add(seriesMap);
			chartMap.put("categories", categoriesList);
			chartMap.put("series", seriesList);
			chartMap.put("nodata", false);
		}else{
			chartMap.put("nodata", true);
		}
		return chartMap;
	}

	@Override
	public Map queryDealEngineerReportData(Map param) {
		String sql="SELECT id,ZSXM,IFNULL(b.score,0) score,IFNULL(b.num,0) mydnum,IFNULL(c.num,0) cynum,IFNULL(d.num,0) wcnum FROM" +
				" gg_user a " +
				"LEFT JOIN " +
				"( SELECT sum(SCORE) score,count(b.id) num,RESOLVE_USER_ID FROM order_satis_info a," +
				"workflow_base b,sc_workflow_incident c WHERE a.BUSINESS_ID = b.id ";
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
			sql+=" and to_number(substr(c.create_time||'',1,8))>=:beginDay ";
		}
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
			sql+=" and to_number(substr(c.create_time||'',1,8))<=:endDay ";
		}
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
			sql+=" and c.CUSTOMER_ID=:cusId ";
		}
		sql+=" AND c.id = b.BUSINESS_ID GROUP BY RESOLVE_USER_ID) b on a.id=b.RESOLVE_USER_ID " +
				"LEFT JOIN " +
				"(SELECT sum(d.num) num,d.CURRENT_DEALER_ID" +
				" from (SELECT count(*) num," +
				"c.CURRENT_DEALER_ID from sc_workflow_incident a,workflow_base b,workflow_node c" +
				" where a.id=b.BUSINESS_ID and b.id=c.FLOWID ";
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
			sql+=" and to_number(substr(a.create_time||'',1,8))>=:beginDay ";
		}
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
			sql+=" and to_number(substr(a.create_time||'',1,8))<=:endDay ";
		}
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
			sql+=" and a.CUSTOMER_ID=:cusId ";
		}
		sql+=" group by c.CURRENT_DEALER_ID,a.id) d" +
				" GROUP BY CURRENT_DEALER_ID) c" +
				" on c.CURRENT_DEALER_ID=a.id " +
				" LEFT JOIN " ;
		sql+=" (SELECT count(*) num,RESOLVE_USER_ID from sc_workflow_incident WHERE 1=1 ";
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){

			sql+=" and to_number(substr(create_time||'',1,8))>=:beginDay ";
		}
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
			sql+=" and to_number(substr(create_time||'',1,8))<=:endDay ";
		}
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
			sql+=" and CUSTOMER_ID=:cusId ";
		}
		sql+=" GROUP BY RESOLVE_USER_ID) d " +
				"on a.id=d.RESOLVE_USER_ID" +
				" WHERE a.zt = 1 ";
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("userName")))){
			param.put("userName", "%"+ConvertUtil.obj2Str(param.get("userName"))+"%");
			sql+=" and a.zsxm like :userName ";
		}
		List<Map> userList=baseDao.queryForList(sql, param);
		sql="SELECT *,0 num from sc_sla where state=1 ORDER BY SERIAL";
		List<Map> slaList=baseDao.queryForList(sql);
		if(userList.size()>0){
			for(Map ma:userList){
				ma.put("slaList", slaList);
			}
		}
		if(userList.size()>0){
			sql="SELECT RESOLVE_USER_ID,SLA_ID,COUNT(a.id) num from sc_workflow_incident a," +
					"workflow_base b where a.id=b.BUSINESS_ID and b.WFSTATE in(2,4) ";
			if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("beginDay")))){
				sql+=" and to_number(substr(a.create_time||'',1,8))>=:beginDay ";
			}
			if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("endDay")))){
				sql+=" and to_number(substr(a.create_time||'',1,8))<=:endDay ";
			}
			if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
				sql+=" and a.CUSTOMER_ID=:cusId ";
			}
			sql+=" GROUP BY RESOLVE_USER_ID,SLA_ID,SLA_NAME order by SLA_NAME";
			List<Map> lvList=baseDao.queryForList(sql, param);
			if(lvList.size()>0){
				for(Map mapa:userList){
					String userId=ConvertUtil.obj2Str(mapa.get("id"));
					List<Map> list2=new LinkedList<Map>();
					for(Map mm:slaList){
						Map mn=new HashMap();
						mn.putAll(mm);
						list2.add(mn);
					}
					if(list2.size()>0){
						for(Map mapb:list2){
							String slaId=ConvertUtil.obj2Str(mapb.get("ID"));
							for(Map mapc:lvList){
								String RESOLVE_USER_ID=ConvertUtil.obj2Str(mapc.get("RESOLVE_USER_ID"));
								String SLA_ID=ConvertUtil.obj2Str(mapc.get("SLA_ID"));
								if(RESOLVE_USER_ID.equals(userId) && SLA_ID.equals(slaId)){
									mapb.put("num", mapc.get("num"));
								}
							}
						}
						mapa.put("slaList", list2);
					}
				}
			}
			for(Map mape:userList){
				String score=ConvertUtil.obj2Str(mape.get("score"));
				String mydnum=ConvertUtil.obj2Str(mape.get("mydnum"));
				String cynum=ConvertUtil.obj2Str(mape.get("cynum"));
				String wcnum=ConvertUtil.obj2Str(mape.get("wcnum"));
				if(StringUtil.isEmpty(mydnum) || mydnum.equals("0") || StringUtil.isEmpty(score) || score.equals("0")){
					mape.put("myf", "0.00");
				}else{
					double myf=NumberUtil.div(ConvertUtil.obj2Double(score), ConvertUtil.obj2Double(mydnum),2);
					mape.put("myf", myf);
				}
				if(StringUtil.isEmpty(cynum) || cynum.equals("0") || StringUtil.isEmpty(wcnum) || wcnum.equals("0")){
					mape.put("jjl", "0");
				}else{
					double jjl=NumberUtil.div(ConvertUtil.obj2Double(wcnum), ConvertUtil.obj2Double(cynum),4);
					jjl=jjl*100;
					mape.put("jjl", jjl);
				}
				List<Map> list2=(List<Map>) mape.get("slaList");
				int sum=0;
				for(Map mapd:list2){
					if(StringUtil.isEmpty(ConvertUtil.obj2Str(mapd.get("num")))){
						sum+=0;
					}else{
						sum+=ConvertUtil.obj2Int(mapd.get("num"));
					}
				}
				mape.put("sum", sum);
			}
		}
		Map result=new HashMap();
		result.put("userList", userList);
		result.put("slaList", slaList);
		return result;
	}

	/* (non-Javadoc)
	 * @see cn.ksource.web.facade.tj.IncidentStatisFacade#getGcsList(java.util.Map)
	 */
	@Override
	public List<Map> getGcsList(Map param) {
		String sql="select ID,ZSXM from gg_user where zt=1";
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("type")))){
			sql=" select a.ID,a.ZSXM from gg_user a,ac_user_ref_role b,ac_role c where a.ID=B.YHBH and C.ID=B.JSBH and C.IDENTIFY='LOCATION'";
			
		}
		return baseDao.queryForList(sql, param);
	}
	
}