package cn.ksource.web.facade.tj;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.ksource.core.dao.BaseDao;
import cn.ksource.core.dao.SqlParameter;
import cn.ksource.core.util.ConvertUtil;
import cn.ksource.core.util.DateUtil;
import cn.ksource.core.util.JsonUtil;
import cn.ksource.core.util.NumberUtil;
import cn.ksource.core.util.StringUtil;
import cn.ksource.web.Constants;
import cn.ksource.web.service.DataDictionaryService;
import cn.ksource.web.util.ChartUtil;

@Service
public class LocalStatisFacadeImpl implements LocalStatisFacade {

	@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 localTypePie(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 localTypeLine(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);

		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");
		String queryType = request.getParameter("queryType");
		List<Map> queryList = new ArrayList<Map>();
		Map paramMap = new HashMap();
		StringBuilder buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE state!=5 ");
		if(queryType.equals("3")){
			buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_INCIDENT_LOCAL C WHERE 1=1 ");
		}
		if(StringUtil.notEmpty(cusId)) {
			buffer.append(" AND CUSTOMER_ID = :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");
		String queryType = request.getParameter("queryType");
		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_LOCAL 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_LOCAL C WHERE 1=1 ");
		}
		if(!queryType.equals("3")){
			builder.append(" and state!=5 ");
		}
		if(StringUtil.notEmpty(cusId)) {
			builder.append(" 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 localTimeLine(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_LOCAL C");
		sql.append(" where  c.state!=5 and 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);
		}
		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 localTotalChart(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,DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate from SC_WORKFLOW_INCIDENT_LOCAL  "); 
		sql.append("	where  HAPPEN_TIME>=:beginTime and HAPPEN_TIME<=:endTime  "); 
		if(StringUtil.isNotBlank(cusId)){
			sql.append("and CUSTOMER_ID=:cusId  "); 
		}
		sql.append("	group by DATE_FORMAT(HAPPEN_TIME,'%Y%m%d')  "); 
		
		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,GROUP_CONCAT(num order by ordernum asc) "+numName+" from ");
		sql.append("  (	");
		sql.append("  	select count(i.ID)  num,mdate,datakey,ordernum from ");
		sql.append("  	( ");
		sql.append("  		select DATAKEY,mdate,ordernum from ");
		sql.append("  		(select DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate  from SC_WORKFLOW_INCIDENT_LOCAL group by mdate ) d,");
		sql.append("  		( ");
		if(StringUtil.isNotBlank(categoryKey)){
			sql = getDicSql(sql,categoryKey);
		}else{
			sql.append(tempSql);
		}
		sql.append("  		) c ");
		sql.append("  	) b ");
		sql.append("  	left join  ");
		if(flag.equals("1")){
			sql.append("( select * from SC_WORKFLOW_INCIDENT_LOCAL   ");
			if(StringUtil.isNotBlank(cusId)){
				sql.append(" where CUSTOMER_ID = :cusId  ");
			}
			sql.append(" ) ");
		}else{
			sql = getStateSql(sql,cusId);
		}
		sql.append(" i on i."+column+" = b.DATAKEY and DATE_FORMAT(i.HAPPEN_TIME,'%Y%m%d')=b.mdate ");
		sql.append(" 	group by b.mdate,b.datakey  ");
		sql.append(" ) t group by mdate ");
		sql.append(" ) "+tempTableName);
		sql.append(" on "+tempTableName+".mdate = mtotal.mdate ");
		return sql;
	}
	
	/**
	 * 获取数据字典查询sql
	 * @param sql
	 * @param categoryKey
	 * @return
	 */
	private StringBuilder getDicSql(StringBuilder sql,String categoryKey){
		sql.append(" select d.DATAKEY,d.ordernum from CONFIG_DATA_DICTIONARY d ");
		sql.append(" inner join CONFIG_DATA_DICTIONARY_CATEGORY cate  ");
		sql.append(" on d.CATEGORYID = cate.ID  ");
		sql.append(" where cate.CATEGORYKEY='"+categoryKey+"'");
		return sql;
	}
	
	/**
	 * 获取事件状态sql
	 * @param sql
	 * @return
	 */
	private StringBuilder getStateSql(StringBuilder sql,String cusId){
		sql.append(" (");
		sql.append("  select c.CUSTOMER_ID,c.id,n.FLOWSTATE,c.HAPPEN_TIME from SC_WORKFLOW_INCIDENT_LOCAL 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 ");
		}
		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<Map> queryLocalIncidentStatis(String cusId,String subCustomerId,String beginTime,String endTime,String wfstate,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 FROM SC_WORKFLOW_INCIDENT_LOCAL A,workflow_base B WHERE 1=1  ");
		if(StringUtil.notEmpty(cusId)){
			sql.append(" AND A.CUSTOMER_ID=:cusId ");
		}
		sql.append(" AND A.ID=B.BUSINESS_ID ");
		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(wfstate)){
			sql.append(" AND B.WFSTATE=:wfstate");
			param.put("wfstate", wfstate);
		}
		if(StringUtil.notEmpty(jjrId)){
			sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
		}
		List localList=baseDao.queryForList(sql.toString(), param);
		return localList;
	}
	@Override
	public List<Map> queryLocalIncidentStatisTwo(String cusId,String subCustomerId,String beginTime,String endTime,String wfstate,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 FROM SC_WORKFLOW_INCIDENT_LOCAL A,workflow_base B WHERE 1=1  ");
		if(StringUtil.notEmpty(cusId)){
			sql.append(" AND A.CUSTOMER_ID=:cusId ");
		}
		sql.append(" AND A.ID=B.BUSINESS_ID ");
		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(type_id)){
			sql.append(" AND A.TYPE_ID=:type_id");
		}
		if(StringUtil.notEmpty(wfstate)){
			sql.append(" AND B.WFSTATE=:wfstate");
			param.put("wfstate", wfstate);
		}
		if(StringUtil.notEmpty(jjrId)){
			sql.append(" AND A.RESOLVE_USER_ID=:jjrId");
		}
		List localList=baseDao.queryForList(sql.toString(), param);
		return localList;
	}
	
////////////////////////////////////////////////////////////////////////////////////////////////////
	/**
	 * 将数据字典转化成DataKeyMap
	 * @author chenlong
	 * @param dicList
	 * @return
	 */
	private Map dicListToDataKeyMap(List<Map> dicList){
		Map dataKeyMap = new HashMap();
		for(Map dic:dicList){
			dataKeyMap.put(ConvertUtil.obj2StrBlank(dic.get("DATAKEY")),ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
		}
		return dataKeyMap;
	}
	
	/**
	 * 格式化数据字典
	 * @author chenlong
	 * @param dicList
	 */
	private void formatDicList(List<Map> dicList){
		for(Map dic:dicList){
			dic.put("key", ConvertUtil.obj2StrBlank(dic.get("DATAKEY")));
			dic.put("val", ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
		}
	}
	
	/**
	 * 组织时间查询条件
	 * @author chenlong
	 * @param frequency
	 * @param sql
	 * @param colName
	 */
	private void getDateWhereSql(String frequency,StringBuilder sql,String colName){
		if(frequency.equals("day")||frequency==null){
			sql.append(" and date_format("+colName+",'%Y%m%d')>=:startDate and date_format("+colName+",'%Y%m%d')<=:endDate  ");
		}else if(frequency.equals("month")){
			sql.append(" and date_format("+colName+",'%Y%m')>=:startDate and date_format("+colName+",'%Y%m')<=:endDate  ");
		}
	}

	@Override
	public Map localTypePie(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_local where 1=1  ");
		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 localSourcePie(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_local where 1=1 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by source_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

	@Override
	public Map localCloseCol(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_local 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 localPriPie(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_local where 1=1 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by priority_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

	@Override
	public Map localInfluenceCol(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_local where 1=1 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by influence_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		
		Map categoryMap = new HashMap();
		categoryMap.put("key", "1");
		categoryMap.put("val", "数量");
		List<Map> categoryList = new ArrayList<Map>();
		categoryList.add(categoryMap);
		return ChartUtil.createHchartLineMap(datas,categoryList,dataKeyMap,0.0);
	}
	
	
	@Override
	public Map localLvPie(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_local where 1=1 ");
		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 localResponsePie(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_local where 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 localDealPie(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_local where 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 localHappendTimeLine(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 hour(happen_time) mhour,count(id) num from sc_workflow_incident_local c");
		sql.append(" where hour(happen_time)>=7  and hour(happen_time)<=18  ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId  ");
		}
		
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by mhour ");
		
		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 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_LOCAL 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 ";
		
		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 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.SECOND_CATEGORY_NAME,mtotal.FIRST_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_LOCAL where CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by THIRD_CATEGORY_ID) mtotal left join  ");
		builder.append(" (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL 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_LOCAL   ");
		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.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num from  ");
		builder.append(" (select THIRD_CATEGORY_NAME,FIRST_CATEGORY_NAME,SECOND_CATEGORY_NAME,count(id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL where state!=10 and  CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by THIRD_CATEGORY_ID) 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 from SC_WORKFLOW_INCIDENT_LOCAL   ");
		builder.append(" where CUSTOMER_ID=:cusId and state!=10 and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by 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("服务目录");
		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("THIRD_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_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.SECOND_CATEGORY_NAME,mtotal.FIRST_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,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(a.id) total_num,a.THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL 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) mtotal left join  ");
		builder.append(" (select THIRD_CATEGORY_NAME,count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL 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) 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.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(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_LOCAL where state!=10 and CUSTOMER_ID=:cusId and CREATE_TIME>=:beginDay and CREATE_TIME<=:endDay  group by THIRD_CATEGORY_ID) mtotal   ");
		builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_INCIDENT_LOCAL 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_LOCAL 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_LOCAL 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_LOCAL 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_LOCAL 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_LOCAL 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_LOCAL 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_LOCAL 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 queryDealDayReport(Map param) {
		String sql="select a.ID,a.zsxm from AC_USER_REF_ROLE b,ac_role c,gg_user a where a.zt=1 and a.id=b.YHBH and b.JSBH=c.ID and c.IDENTIFY='LOCATION' ";
		
		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 count(*) num,RESOLVE_USER_ID,DATE_FORMAT(RESOLVE_TIME,'%Y%m%d') day from sc_workflow_incident_local ";
		
		sql+=" where DATE_FORMAT(RESOLVE_TIME,'%Y%m%d')>=:beginDay and DATE_FORMAT(RESOLVE_TIME,'%Y%m%d')<=:endDay  ";
		if(StringUtil.notEmpty(ConvertUtil.obj2Str(param.get("cusId")))){
			sql+=" and CUSTOMER_ID=:cusId ";
		}
		sql+=" GROUP BY RESOLVE_USER_ID,DATE_FORMAT(RESOLVE_TIME,'%Y%m%d')";
		List<Map> numList=baseDao.queryForList(sql, param);
		Long beginDay=ConvertUtil.obj2Long(param.get("beginDay"));
    	Long endDay=ConvertUtil.obj2Long(param.get("endDay"));
    	beginDay=DateUtil.getDateAdd(beginDay, -1, 8);
    	endDay=DateUtil.getDateAdd(endDay, 1, 8);
		List<String> categories = DateUtil.getDates(beginDay, endDay);
		if(categories.size()==0){
			categories.add(ConvertUtil.obj2Str(DateUtil.getCurrentDate8()));
		}
		List<Map> dayList=new LinkedList<Map>();
		if(categories.size()>0){
			for(String s:categories){
				Map mapa=new HashMap();
				mapa.put("day", s);
				mapa.put("num", 0);
				dayList.add(mapa);
			}
		}
		if(userList.size()>0){
			for(Map mapb:userList){
				mapb.put("dayList", dayList);
			}
		}
		if(userList.size()>0){
			for(Map map:userList){
				String userId=ConvertUtil.obj2Str(map.get("id"));
				List<Map> list2=new LinkedList<Map>();
				for(Map mm:dayList){
					Map mn=new HashMap();
					mn.putAll(mm);
					list2.add(mn);
				}
				if(list2.size()>0){
					for(Map mapc:list2){
						String day=ConvertUtil.obj2Str(mapc.get("day"));
						if(numList.size()>0){
							for(Map mapd:numList){
								String RESOLVE_USER_ID=ConvertUtil.obj2Str(mapd.get("RESOLVE_USER_ID"));
								String relove_day=ConvertUtil.obj2Str(mapd.get("day"));
								if(userId.equals(RESOLVE_USER_ID) && day.equals(relove_day)){
									mapc.put("num", mapd.get("num"));
								}
							}
						}
					}
				}
				map.put("dayList", list2);
			}
		}
		Map result=new HashMap();
		result.put("dayList", dayList);
		result.put("userList", userList);
		return result;
	}

	
}