package cn.ksource.web.facade.tj;

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

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class QuestionStatisFacadeImpl implements QuestionStatisFacade {

	@Autowired
	private BaseDao baseDao;
	
	@Autowired
	private DataDictionaryService dataDictionaryService;
	
	//获取查询Map
	private Map getQueryMap(HttpServletRequest request){
		String focus = request.getParameter("focus");
		
		Map queryMap = new HashMap();
		if(focus.equals("QUESTION_SOURCE")){//按问题来源
			queryMap.put("col","SOURCE_ID");
			queryMap.put("focus",focus);
			queryMap.put("dicCategoryKey",Constants.QUESTION_SOURCE);
		}else if(focus.equals("QUESTION_CLOSE")){//关闭原因
			queryMap.put("col","RESOLVE_TYPE_ID");
			queryMap.put("focus",focus);
			queryMap.put("dicCategoryKey",Constants.QUESTIONCLOSE_RESOLVE_TYPE);
		}else if(focus.equals("QUESTION_PRI")){//优先级
			queryMap.put("col","PRIORITY_ID");
			queryMap.put("focus",focus);
			queryMap.put("dicCategoryKey",Constants.INCIDENT_EVENT_PRI);
		}else if(focus.equals("QUESTION_EFFECT")){//影响度
			queryMap.put("col","INFLUENCE_ID");
			queryMap.put("focus",focus);
			queryMap.put("dicCategoryKey",Constants.EVENT_EFFECT_DG);
		}else if(focus.equals("QUESTION_SERVER_CATALOG")){//服务目录
			String lv1Id = request.getParameter("lv1Id");
			String lv2Id = request.getParameter("lv2Id");
			if(StringUtil.isBlank(lv1Id)){
				queryMap.put("col","FIRST_CATEGORY_ID");
			}else if(StringUtil.isNotBlank(lv1Id)&&StringUtil.isBlank(lv2Id)){
				queryMap.put("col","SECOND_CATEGORY_ID");
			}else if(StringUtil.isNotBlank(lv2Id)){
				queryMap.put("col","THIRD_CATEGORY_ID");
			}
			queryMap.put("lv1Id",lv1Id);
			queryMap.put("lv2Id",lv2Id);
			queryMap.put("focus",focus);
		}
		return queryMap;
	}
	
	//获取初始化list
	private List getQueryTypeList(Map queryMap){
		String focus = ConvertUtil.obj2StrBlank(queryMap.get("focus"));
		List queryTypeList = new ArrayList<Map>();
		if(focus.equals("QUESTION_SOURCE")||focus.equals("QUESTION_CLOSE")||focus.equals("QUESTION_PRI")||focus.equals("QUESTION_EFFECT")){
			queryTypeList = dataDictionaryService.getDataDictionaryByCategoryKey(queryMap.get("dicCategoryKey").toString());
		}else if(focus.equals("QUESTION_SERVER_CATALOG")){
			if(StringUtil.isBlank(queryMap.get("lv1Id").toString())){
				String sql = "select ID DATAKEY,CATEGORY_NAME DATAVALUE from sc_servce_category where level=1  ";
				queryTypeList = baseDao.queryForList(sql);
			}else if(StringUtil.isNotBlank(queryMap.get("lv1Id").toString())&&StringUtil.isBlank(queryMap.get("lv2Id").toString())){
				String sql = "select ID DATAKEY,CATEGORY_NAME DATAVALUE from sc_servce_category where level=2 and p_id=:lv1Id ";
				queryTypeList = baseDao.queryForList(sql,new SqlParameter().addValue("lv1Id", queryMap.get("lv1Id").toString()));
			}else if(StringUtil.isNotBlank(queryMap.get("lv2Id").toString())) {
				String sql = "select ID DATAKEY,CATEGORY_NAME DATAVALUE from sc_servce_category where level=3 and p_id=:lv2Id  ";
				queryTypeList = baseDao.queryForList(sql,new SqlParameter().addValue("lv2Id", queryMap.get("lv2Id").toString()));
			}
		}
		return queryTypeList;
	}
	
	@Override
	public List questionTypePie(HttpServletRequest request) {
		
		String cusId = request.getParameter("cusId");
		String frequency = request.getParameter("frequency");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		if(!StringUtil.notEmptyNum(frequency)) {
			frequency = "2";
		}
		if(frequency.equals("1")) {
			beginTime = beginTime.replace("-", "")+"01";
			endTime = endTime.replace("-", "")+"31";
		} else {
			beginTime = beginTime.replace("-", "");
			endTime = endTime.replace("-", "");
		}
		//获取初始化LIST
		Map queryMap = getQueryMap(request);
		List<Map> list = getQueryTypeList(queryMap);
		List<Map> dataList = getQueryList_Pie(request,queryMap);
		
		//组装数据
		List listResult = new ArrayList();
		if(list!=null&&list.size() > 0){
			for(Map resMap:list){
				List pieDate = new ArrayList();
				resMap.put("num", 0);
				if(dataList!=null&&dataList.size()>0){
					for(Map tempMap:dataList){
						if(tempMap.get("COLKEY").toString().equals(resMap.get("DATAKEY").toString())){
							resMap.put("num", tempMap.get("NUM"));
							break;
						}
					}
				}
				pieDate.add(resMap.get("DATAVALUE"));
				pieDate.add(resMap.get("num"));
				listResult.add(pieDate);
			}
		}
        System.out.println("JSON---------------------->"+JsonUtil.list2Json(listResult));
		return listResult;
	}

	@Override
	public Map questionTypeLine(HttpServletRequest request) {
		
		String frequency = request.getParameter("frequency");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		
		if(!StringUtil.notEmptyNum(frequency)) {
			frequency = "2";
		}
		List<String> categories = new ArrayList<String>();//获取X轴数据
		if(frequency.equals("1")) {
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
		} else {
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
		}
		
		
		Map queryMap = getQueryMap(request);
		List<Map> types = getQueryTypeList(queryMap);
		List<Map> typeMsg = getQueryList_Line(request,queryMap);
		
		Map cacheMap = new HashMap();
		if(null!=typeMsg && typeMsg.size()>0) {
			for(Map  msg : typeMsg) {
				String typeId = ConvertUtil.obj2StrBlank(msg.get("COLKEY"));
				int num = ConvertUtil.obj2Int(msg.get("NUM"));
				String time = ConvertUtil.obj2StrBlank(msg.get("DC_TIME"));
				String key = time + "-" + typeId;
				cacheMap.put(key, num);
			}
		}
		
		
		List resultList = new ArrayList();
		if(null!=types && types.size()>0) {
			for(Map t : types) {
				Map newMap = new HashMap();
				String key = ConvertUtil.obj2StrBlank(t.get("DATAKEY"));
				String name = ConvertUtil.obj2StrBlank(t.get("DATAVALUE"));
				newMap.put("name", name);
				List list = new ArrayList();
				for(String cate : categories) {
					String unitKey = cate + "-" + key;
					if(cacheMap.containsKey(unitKey)) {
						list.add(cacheMap.get(unitKey));
					} else {
						list.add(0);
					}
				}
				newMap.put("data", list);
				resultList.add(newMap);
			}
		}
		//System.out.println(JsonUtil.list2Json(resultList));
		Map resultMap = new HashMap();
		resultMap.put("cate", categories);
		resultMap.put("data", resultList);
		resultMap.put("step", Math.ceil(categories.size()/8));
		System.out.println("line-------------->"+JsonUtil.map2Json(resultMap));
		return resultMap;
	}
	
	//获取饼状图查询结果
	private List getQueryList_Pie(HttpServletRequest request,Map queryMap){
		
		String cusId = request.getParameter("cusId");
		String frequency = request.getParameter("frequency");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String queryType = request.getParameter("queryType");
		List<Map> queryList = new ArrayList<Map>();
		Map paramMap = new HashMap();
		StringBuilder buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_QUESTION C WHERE state!=7 ");
		if(StringUtil.notEmpty(queryType)){
			buffer = new StringBuilder("SELECT $KEY as COLKEY,COUNT(C.ID) AS NUM FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
		}
		if(StringUtil.notEmpty(cusId) ) {
			buffer.append(" AND CUSTOMER_ID = :cusId ");
			paramMap.put("cusId", cusId);
		} 
		if(StringUtil.notEmpty(frequency)) {
			buffer.append(" AND C.CREATE_TIME >= :beginTime AND C.CREATE_TIME <= :endTime ");
			if(frequency.equals("1")) {
				paramMap.put("beginTime", beginTime+"01000000");
				paramMap.put("endTime", endTime+"31240000");
			} else {
				paramMap.put("beginTime", beginTime+"000000");
				paramMap.put("endTime", endTime+"240000");
			}
			
		}
		if(ConvertUtil.obj2StrBlank(queryMap.get("focus")).equals("QUESTION_CLOSE")){
			buffer.append(" and state = 7 ");
		}
		buffer.append(" and $KEY is not null ");
		buffer.append(" GROUP BY $KEY ");
		
		String sql = buffer.toString().replace("$KEY", ConvertUtil.obj2StrBlank(queryMap.get("col")));
		List<Map> dataList = baseDao.queryForList(sql, paramMap);
		return dataList;
	}
	
	//获取线形图查询结果
	private List getQueryList_Line(HttpServletRequest request,Map queryMap){
		
		String cusId = request.getParameter("cusId");
		String frequency = request.getParameter("frequency");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String queryType = request.getParameter("queryType");
		Map paramMap = new HashMap();
		StringBuilder builder = new StringBuilder("");
		if(frequency.equals("2")) {
			builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m-%d') AS DC_TIME  FROM SC_WORKFLOW_QUESTION C WHERE 1=1");
		} else {
			builder = builder.append("SELECT $KEY COLKEY,COUNT(C.ID) AS NUM,DATE_FORMAT(C.CREATE_TIME,'%Y-%m') AS DC_TIME FROM SC_WORKFLOW_QUESTION C WHERE 1=1 ");
		}
		if(StringUtil.isBlank(queryType)){
			builder.append(" AND state!=7 ");
		}
		
		if(StringUtil.notEmpty(cusId)) {
			builder.append(" AND C.CUSTOMER_ID = :cusId AND C.CUSTOMER_ID = :cusId ");
			paramMap.put("cusId", cusId);
		} 
		if(StringUtil.notEmpty(beginTime) && StringUtil.notEmpty(endTime)) {
			beginTime = beginTime.replaceAll("-", "");
			endTime = endTime.replaceAll("-", "");
			builder.append(" AND C.CREATE_TIME>=:beginTime AND C.CREATE_TIME <=:endTime ");
			if(frequency.equals("1")) {
				paramMap.put("beginTime", beginTime+"01000000");
				paramMap.put("endTime", endTime+"31240000");
			} else {
				paramMap.put("beginTime", beginTime+"000000");
				paramMap.put("endTime", endTime+"240000");
			}
		}
		builder.append(" and $KEY is not null ");
		builder.append(" GROUP BY $KEY,DC_TIME ");
		String sql = builder.toString().replace("$KEY", ConvertUtil.obj2StrBlank(queryMap.get("col")));
		return baseDao.queryForList(sql,paramMap);
	}
	
	/**
	 * 获取组装sql
	 * @param sql
	 * @param categoryKey 数据字典分类key
	 * @param tempSql 临时sql
	 * @param column 列名称
	 * @param tempTableName 临时表名称
	 * @param flag 标示
	 * @return 组装后sql
	 */
	private StringBuilder getPartSql(StringBuilder sql,String numName,String categoryKey,String tempSql,
			String column,String tempTableName,String flag,String cusId){
		sql.append(" left join  ");
		sql.append(" (  ");
		sql.append("  select mdate,GROUP_CONCAT(num order by ordernum asc) "+numName+" from ");
		sql.append("  (	");
		sql.append("  	select count(i.ID)  num,mdate,datakey,ordernum from ");
		sql.append("  	( ");
		sql.append("  		select DATAKEY,mdate,ordernum from ");
		sql.append("  		(select DATE_FORMAT(HAPPEN_TIME,'%Y%m%d') mdate  from SC_WORKFLOW_INCIDENT_question group by mdate ) d,");
		sql.append("  		( ");
		if(StringUtil.isNotBlank(categoryKey)){
			sql = getDicSql(sql,categoryKey);
		}else{
			sql.append(tempSql);
		}
		sql.append("  		) c ");
		sql.append("  	) b ");
		sql.append("  	left join  ");
		if(flag.equals("1")){
			sql.append("( select * from SC_WORKFLOW_INCIDENT_question   ");
			if(StringUtil.isNotBlank(cusId)){
				sql.append(" where CUSTOMER_ID = :cusId  ");
			}
			sql.append(" ) ");
		}else{
			sql = getStateSql(sql,cusId);
		}
		sql.append(" i on i."+column+" = b.DATAKEY and DATE_FORMAT(i.HAPPEN_TIME,'%Y%m%d')=b.mdate ");
		sql.append(" 	group by b.mdate,b.datakey  ");
		sql.append(" ) t group by mdate ");
		sql.append(" ) "+tempTableName);
		sql.append(" on "+tempTableName+".mdate = mtotal.mdate ");
		return sql;
	}
	
	/**
	 * 获取数据字典查询sql
	 * @param sql
	 * @param categoryKey
	 * @return
	 */
	private StringBuilder getDicSql(StringBuilder sql,String categoryKey){
		sql.append(" select d.DATAKEY,d.ordernum from CONFIG_DATA_DICTIONARY d ");
		sql.append(" inner join CONFIG_DATA_DICTIONARY_CATEGORY cate  ");
		sql.append(" on d.CATEGORYID = cate.ID  ");
		sql.append(" where cate.CATEGORYKEY='"+categoryKey+"'");
		return sql;
	}
	
	/**
	 * 获取事件状态sql
	 * @param sql
	 * @return
	 */
	private StringBuilder getStateSql(StringBuilder sql,String cusId){
		sql.append(" (");
		sql.append("  select c.CUSTOMER_ID,c.id,n.FLOWSTATE,c.HAPPEN_TIME from SC_WORKFLOW_INCIDENT_question c  ");
		sql.append("  inner join workflow_base b on c.flow_id = b.id  ");
		sql.append("  inner JOIN workflow_node n on b.CURRENT_NODE_ID = n.id  ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append("  where c.CUSTOMER_ID = :cusId ");
		}
		sql.append(" )");
		return sql;
	}
	
	
	private List<Map> getDicList(String categoryKey){
		String sql = " select d.DATAVALUE labelName from CONFIG_DATA_DICTIONARY d  " +
					" inner join CONFIG_DATA_DICTIONARY_CATEGORY c on " +
					" d.CATEGORYID = c.ID where c.CATEGORYKEY=:categoryKey ";
		Map param = new HashMap();
		param.put("categoryKey",categoryKey);
		return baseDao.queryForList(sql,param);
	}
	
	public List getCiCategoryList(String lv1Id){
		StringBuilder sql = new StringBuilder();
		Map param = new HashMap();
		param.put("lv1Id", lv1Id);
		
		sql.append(" select id,CATEGORY_NAME name from sc_servce_category ");
		sql.append(" where STATE=1 ");
		if(StringUtil.isBlank(lv1Id)){
			sql.append(" and LEVEL =1 ");
		}else{
			sql.append(" and LEVEL =2 and P_ID = :lv1Id ");
		}
		sql.append(" order by SERIAL ");
		return baseDao.queryForList(sql.toString(),param);
	}

	@Override
	public Map queryOrderClStatis(HttpServletRequest request, int type) {
		Map resultMap = new HashMap();
		
		String cusId = request.getParameter("cusId");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String subCustomerId = request.getParameter("sub_customer_id");
		StringBuilder builder1 = new StringBuilder("SELECT COUNT(A.ID) AS NUM,B.THIRD_CATEGORY_ID,B.THIRD_CATEGORY_NAME FROM WORKFLOW_BASE A,SC_WORKFLOW_QUESTION B WHERE A.BUSINESS_ID = B.ID AND A.WFSTATE != 5 AND A.BUSINESSTYPE = :businessType AND A.CUSTOMER_ID = :cusId AND A.CUSTOMER_ID = :cusId AND B.CREATE_TIME > :beginTime AND B.CREATE_TIME <= :endTime ");
		StringBuilder builder2 = new StringBuilder("SELECT COUNT(A.ID) AS NUM,B.THIRD_CATEGORY_ID,A.WFSTATE FROM WORKFLOW_BASE A,SC_WORKFLOW_QUESTION B WHERE A.BUSINESS_ID = B.ID AND A.WFSTATE != 5 AND A.BUSINESSTYPE = :businessType AND A.CUSTOMER_ID = :cusId AND A.CUSTOMER_ID = :cusId AND B.CREATE_TIME > :beginTime AND B.CREATE_TIME <= :endTime ");
		
		Map paramMap = new HashMap();
		
		if(StringUtil.notEmpty(subCustomerId)) {
			builder1.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
			builder2.append(" AND A.SUB_CUSTOMER_ID = :subCustomerId ");
			paramMap.put("subCustomerId", subCustomerId);
		}
		
		builder1.append(" GROUP BY B.THIRD_CATEGORY_ID ");
		builder2.append(" GROUP BY B.THIRD_CATEGORY_ID,A.WFSTATE ");
		
		paramMap.put("cusId", cusId);
		paramMap.put("cusId", cusId);
		paramMap.put("beginTime", beginTime+"000000");
		paramMap.put("endTime", endTime+"240000");
		paramMap.put("businessType", type);
		
		
		List<Map> list1 = baseDao.queryForList(builder1.toString(),paramMap);
		
		List<Map> list2 = baseDao.queryForList(builder2.toString(),paramMap);
		
		int total = 0;
		int totaljxz = 0;
		int totalywc = 0;
		int totalygb = 0;
		int totalypj = 0;
		int totalygq = 0;
		
		if(null!=list1 && list1.size()>0) {
			Map map2 = new HashMap();
			for(Map map : list2) {
				String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				String wfstate = ConvertUtil.obj2StrBlank(map.get("WFSTATE"));
				String unitKey = third_category_id+"-"+wfstate;
				String num = ConvertUtil.obj2StrBlank(map.get("NUM"));
				map2.put(unitKey, num);
			}
			
			
			for(Map map : list1) {
				int num = ConvertUtil.obj2Int(map.get("NUM"));
				total += num;
				
				
				String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				String unitKey1 = third_category_id+"-1";
				
				int jxz = 0;
				if(map2.containsKey(unitKey1)) {
					jxz = ConvertUtil.obj2Int(map2.get(unitKey1));
				}
				map.put("jxz", jxz);
				
				totaljxz+=jxz;
				
				int ywc = 0;
				String unitKey2 = third_category_id+"-2";
				if(map2.containsKey(unitKey2)) {
					ywc = ConvertUtil.obj2Int(map2.get(unitKey2));
				}
				map.put("ywc", ywc);
				
				totalywc += ywc;
				
				
				int ygb = 0;
				String unitKey3 = third_category_id+"-3";
				if(map2.containsKey(unitKey3)) {
					ygb = ConvertUtil.obj2Int(map2.get(unitKey3));
				}
				map.put("ygb", ygb);
				
				totalygb += ygb;
				
				int ypj = 0;
				String unitKey4 = third_category_id+"-4";
				if(map2.containsKey(unitKey4)) {
					ypj = ConvertUtil.obj2Int(map2.get(unitKey4));
				}
				map.put("ypj", ypj);
				
				totalypj += ypj;
				
				int ygq = 0;
				String unitKey6 = third_category_id+"-6";
				if(map2.containsKey(unitKey6)) {
					ygq = ConvertUtil.obj2Int(map2.get(unitKey6));
				}
				map.put("ygq", ygq);
				
				totalygq += ygq;
				
			}
		}
		
		resultMap.put("reports", list1);
		resultMap.put("totaljxz", totaljxz);
		resultMap.put("totalywc", totalywc);
		resultMap.put("totalygb", totalygb);
		resultMap.put("totalypj", totalypj);
		resultMap.put("totalygq", totalygq);
		resultMap.put("total", total);
		System.out.println(JsonUtil.map2Json(resultMap));
		return resultMap;
	}

	@Override
	public List<Map> queryQuestiontDclStatis(String cusId,String subCustomerId,String beginTime,String endTime) {
		
		Map paramMap = new HashMap();
		
		//通过项目编号，查询该项目的所有的服务目录
		StringBuilder builder1 = new StringBuilder("SELECT COUNT(ID) AS NUM,THIRD_CATEGORY_ID,THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME FROM SC_WORKFLOW_QUESTION WHERE  state!=7 and CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId AND CREATE_TIME > :beginTime AND CREATE_TIME <= :endTime " );
		
		StringBuilder builder2 = new StringBuilder("SELECT THIRD_CATEGORY_ID,RESOLVE_TIME,WANT_DEAL_TIME FROM SC_WORKFLOW_QUESTION WHERE state!=7 and  CUSTOMER_ID = :cusId AND CUSTOMER_ID = :cusId AND CREATE_TIME > :beginTime AND CREATE_TIME <= :endTime  ");
		if(StringUtil.notEmpty(subCustomerId)) {
			builder1.append(" AND SUB_CUSTOMER_ID = :subCustomerId ");
			builder2.append(" AND SUB_CUSTOMER_ID = :subCustomerId ");
			paramMap.put("subCustomerId", subCustomerId);
		}
		
		String endSql = " GROUP BY THIRD_CATEGORY_ID ";
		
		builder1.append(endSql);
		
		
		
		paramMap.put("cusId", cusId);
		paramMap.put("cusId", cusId);
		paramMap.put("beginTime", beginTime+"000000");
		paramMap.put("endTime", endTime+"240000");
		
		
		List<Map> list1 = baseDao.queryForList(builder1.toString(),paramMap);
		
		List<Map> list2 = baseDao.queryForList(builder2.toString(),paramMap);
		
		
		
		if(null!=list1 && list1.size()>0) {
			Map map2 = new HashMap();
			for(Map map : list2) {
				
				String third_category_id = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				
				String time = ConvertUtil.obj2StrBlank(map.get("RESOLVE_TIME"));
				if(!StringUtil.notEmpty(time)) {
					time = DateUtil.getToday("yyyyMMdd");
				} else {
					time = DateUtil.format("yyyyMMdd", time);
				}
				
				String requestTime = ConvertUtil.obj2StrBlank(map.get("WANT_DEAL_TIME"));
				
				
				if(ConvertUtil.obj2Int(requestTime) < ConvertUtil.obj2Int(time)) {
					if(map2.containsKey(third_category_id)) {
						int num = ConvertUtil.obj2Int(map2.get(third_category_id));
						map2.put(third_category_id, num+1);
					} else {
						map2.put(third_category_id, 1);
					}
				}
				
				
				
			}
			
			
			for(Map map : list1) {
				String thirdCategoryId = ConvertUtil.obj2StrBlank(map.get("THIRD_CATEGORY_ID"));
				int num = 0;
				if(map2.containsKey(thirdCategoryId)) {
					num = ConvertUtil.obj2Int(map2.get(thirdCategoryId));
				}
				map.put("cs", num);
				
				
				
				
				int total = ConvertUtil.obj2Int(map.get("NUM"));
				
				
				double jjl = NumberUtil.div(ConvertUtil.obj2Double((total-num)), ConvertUtil.obj2Double(total), 3);
				
				jjl = NumberUtil.mul(jjl, ConvertUtil.obj2Double(100));
				
				map.put("jjl", jjl);
				
			}
			
		}
		
		System.out.println(JsonUtil.list2Json(list1));
		return list1;
	}

	
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	@Override
	public Map queryDetailReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		//定义报表变量
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		//查询数据
		StringBuilder builder = new StringBuilder();
		builder.append(" SELECT q.NAME,q.DESCRIP,q.CONTACT_NAME,q.CONTACT_PHONE,q.SOURCE_NAME,q.THIRD_CATEGORY_NAME,q.SECOND_CATEGORY_NAME,q.FIRST_CATEGORY_NAME,DATE_FORMAT(q.WANT_DEAL_TIME,'%Y-%m-%d') WANT_DEAL_TIME, ");
		builder.append(" q.PRIORITY_NAME,q.INFLUENCE_NAME,q.STATE,q.RESOLVE_TYPE_NAME,q.RESOLVE,t.CREATE_USER_NAME,DATE_FORMAT(t.CREATE_TIME,'%Y-%m-%d') CREATE_TIME,IFNULL(t.deal_time,0) deal_time  ");
		builder.append(" from SC_WORKFLOW_QUESTION q  ");
		builder.append(" LEFT JOIN ( ");
		builder.append(" 	select z.CREATE_USER_NAME,z.CREATE_TIME,b.ID,truncate(TIMESTAMPDIFF(SECOND, b.CREATETIME, b.ENDTIME)*1000,0) deal_time from QUESTION_ZD_HISTORY z,workflow_base b where b.ID = z.FLOWID ");
		builder.append(" 	and z.ISTHEEND =1 group by b.ID ");
		builder.append(" ) t on q.FLOW_ID = t.ID where q.CREATE_TIME >:beginDay and q.CREATE_TIME < :endDay  ");
		if(StringUtil.notEmpty(cusId)){
			builder.append(" and q.CUSTOMER_ID = :cusId ");
		}
		
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		//组织表头
		lebalList.add("序号");
		lebalList.add("问题名称");
		lebalList.add("问题描述");
		lebalList.add("联系人");
		lebalList.add("联系方式");
		lebalList.add("问题来源");
		lebalList.add("服务目录");
		lebalList.add("期望完成时间");
		lebalList.add("优先级");
		lebalList.add("影响程度");
		lebalList.add("问题状态");
		lebalList.add("解决结果");
		lebalList.add("解决方案");
		lebalList.add("诊断时长");
		lebalList.add("诊断人");
		lebalList.add("诊断时间");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("DESCRIP")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("CONTACT_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("CONTACT_PHONE")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("SOURCE_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("WANT_DEAL_TIME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("PRIORITY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("INFLUENCE_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("STATE")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("RESOLVE_TYPE_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("RESOLVE")));
				//计算诊断时长
				if(base.get("CREATE_USER_NAME")!=null){
					tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("deal_time"))));
				}else{
					tempList.add("");
				}
				tempList.add(ConvertUtil.obj2StrBlank(base.get("CREATE_USER_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("CREATE_TIME")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	
	@Override
	public Map queryDealStatuReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		//定义报表变量
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		//查询数据
		StringBuilder builder = new StringBuilder();
		builder.append(" select mtotal.THIRD_CATEGORY_NAME,mtotal.SECOND_CATEGORY_NAME,mtotal.FIRST_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(finish_num,0) finish_num,IFNULL(sleep_num,0) sleep_num,  ");
		builder.append(" IFNULL(avg_deal_time,0) avg_deal_time,truncate(IFNULL(major_num,0)*100/total_num,2) major_rate,truncate(IFNULL(finish_num, 0)*100/total_num,2) solve_rate,truncate(IFNULL(close_num, 0)*100/total_num,2) close_rate,  ");
		builder.append(" truncate(IFNULL(repeat_num, 0)*100/total_num,2) repeat_rate,truncate(IFNULL(nosolve_num, 0)*100/total_num,2) nosolve_rate   from  ");
		builder.append(" (select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,count(i.id) total_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mtotal   ");
		builder.append(" left join (select count(i.id) answer_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b,(SELECT FLOWID FROM workflow_node WHERE FLOWSTATE = 1 GROUP BY FLOWID) n where b.BUSINESS_ID = i.ID AND b.id = n.FLOWID  and i.CUSTOMER_ID=:cusId  and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) manswer  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) activi_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=1 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mactivi  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) finish_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=2 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mfinish  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) sleep_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=6 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) msleep  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = msleep.THIRD_CATEGORY_ID ");
		builder.append(" left join (select truncate(avg(TIMESTAMPDIFF(SECOND,b.CREATETIME,b.ENDTIME))*1000,0) avg_deal_time,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where i.FLOW_ID=b.ID and (b.WFSTATE=2 or b.WFSTATE=4) and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mdeal  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mdeal.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(id) major_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i where i.PRI_LEVEL='LEVEL1' and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mmajor  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mmajor.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(i.id) close_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i,workflow_base b where b.BUSINESS_ID = i.ID and b.WFSTATE=3 and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mclose  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(id) repeat_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i where i. RESOLVE_TYPE_ID='WTCF' and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mrepeat  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mrepeat.THIRD_CATEGORY_ID ");
		builder.append(" left join (select count(id) nosolve_num,THIRD_CATEGORY_ID from SC_WORKFLOW_QUESTION i where i. RESOLVE_TYPE_ID='WFJJ' and i.CUSTOMER_ID=:cusId and i.CREATE_TIME>=:beginDay and i.CREATE_TIME<=:endDay group by i.THIRD_CATEGORY_ID) mnosolve  ");
		builder.append(" on  mtotal.THIRD_CATEGORY_ID = mnosolve.THIRD_CATEGORY_ID ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		//组织表头
		lebalList.add("服务目录");
		lebalList.add("问题数");
		lebalList.add("待响应");
		lebalList.add("进行中");
		lebalList.add("已完成");
		lebalList.add("挂起数");
		lebalList.add("平均诊断时间");
		lebalList.add("重大问题比率");
		lebalList.add("成功解决率");
		lebalList.add("问题取消率");
		lebalList.add("问题重复率");
		lebalList.add("无法解决率");
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("answer_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("sleep_num")));
				tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("avg_deal_time"))));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("major_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("solve_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("close_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("repeat_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("nosolve_rate"))+"%");
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryEngineerDealReport(HttpServletRequest request) {
		String cusId = request.getParameter("cusId");
		String beginDay = request.getParameter("beginDay");
		String endDay = request.getParameter("endDay");
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		
		StringBuilder builder = new StringBuilder();
		builder.append(" select mtotal.user_name,IFNULL(create_num,0) create_num,IFNULL(redis_num,0) redis_num,IFNULL(activi_num,0) activi_num, ");
		builder.append(" IFNULL(finish_num,0) finish_num,IFNULL(close_num,0) close_num,IFNULL(avg_deal_time,0) avg_deal_time,TRUNCATE(IFNULL(finish_num,0)*100/total_num,2) deal_rate, ");
		//查询总数
		builder.append(" TRUNCATE(IFNULL(ontime_num,0)*100/total_num,2) ontime_rate,IFNULL(major_num,0) major_num from ( ");
		builder.append(" 	select count(b.ID) total_num,user_name,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node n,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 		) u WHERE n.current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mtotal left join ( ");
		///查询创建数
		builder.append(" 	select count(b.ID) create_num,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 	) t where b.CREATERID = t.user_id and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mcreate on mtotal.user_id = mcreate.user_id left join ( ");
		//查询分发数
		builder.append(" 	select count(b.ID) redis_num,user_name,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node n,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 		) u WHERE n.current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.CURRENT_DEALER_ID<>t.user_id and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mredis on mtotal.user_id = mredis.user_id left join ( ");
		//查询进行中工单
		builder.append(" 	select count(b.ID) activi_num,user_name,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name ");
		builder.append(" 		FROM workflow_node n,(");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 		) u WHERE n.current_dealer_id  = u.USER_ID and n.FLOWSTATE=2  GROUP BY FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mactivi on mtotal.user_id = mactivi.user_id left join ( ");
		//查询结束工单
		builder.append(" 	select count(b.ID) finish_num,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mfinish on mtotal.user_id = mfinish.user_id left join ( ");
		//查询关闭工单
		builder.append(" 	select count(b.ID) close_num,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 		) t where b.CURRENT_DEALER_ID = t.USER_ID and b.WFSTATE=3 and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 		and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mclose on mtotal.user_id = mclose.user_id left join ( ");
		//查询平均处理时间
		builder.append(" 	select truncate(avg(TIMESTAMPDIFF(SECOND, b.CREATETIME, b.ENDTIME)*1000),0) avg_deal_time,user_id from workflow_base b , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and (b.WFSTATE=2 or b.WFSTATE=4) and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) mavg on mtotal.user_id = mavg.user_id left join ( ");
		//查询解决个数
		builder.append(" 	select count(b.id) ontime_num,user_id from workflow_base b , SC_WORKFLOW_QUESTION i , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 		WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID ");
		builder.append(" 	) t where b.CURRENT_DEALER_ID = t.USER_ID and b.BUSINESS_ID = i.ID and i.DEAL_TIMEOUT=2 and (b.WFSTATE=2 or b.WFSTATE=4)  ");
		builder.append(" 	and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id ");
		builder.append(" ) montime on mtotal.user_id = montime.user_id left join ( ");
		//查询重大事件
		builder.append(" 	select count(b.ID) major_num,user_name,user_id from workflow_base b ,SC_WORKFLOW_QUESTION i , ");
		builder.append(" 	( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name  ");
		builder.append(" 		FROM workflow_node n,(  ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u  ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND CUSTOMER_ID = :cusId group by u.USER_ID  ");
		builder.append(" 		) u WHERE n.current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID  ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.ID=i.FLOW_ID and i.PRI_LEVEL='LEVEL1' and b.BUSINESSTYPE=9 and b.CUSTOMER_ID=:cusId  ");
		builder.append(" 	and b.CREATETIME>=:beginDay and b.CREATETIME<=:endDay GROUP BY t.user_id  ");
		builder.append(" ) mmajor on mtotal.user_id = mmajor.user_id  ");
		
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		
		//组织表头
		lebalList.add("人员");
		lebalList.add("创建问题数");
		lebalList.add("分配问题数");
		lebalList.add("处理中");
		lebalList.add("已解决问题");
		lebalList.add("已关闭问题");
		lebalList.add("平均问题解决时间");
		lebalList.add("解决成功率");
		lebalList.add("解决及时率");
		lebalList.add("重大问题数");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("user_name")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("create_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("redis_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("close_num")));
				tempList.add(DateUtil.secToTime(ConvertUtil.obj2Long(base.get("avg_deal_time"))));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("deal_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("ontime_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("major_num")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

	@Override
	public Map queryStatusReport(String cusId,String beginDay,String endDay) {

		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay+"000000");
		paramMap.put("endDay", endDay+"666666");
		//定义报表变量
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		//查询数据
		StringBuilder builder = new StringBuilder();
		builder.append(" select THIRD_CATEGORY_NAME,SECOND_CATEGORY_NAME,FIRST_CATEGORY_NAME,total_num,IFNULL(answer_num,0) answer_num,IFNULL(activi_num,0) activi_num,IFNULL(sleep_num,0) sleep_num,   ");
		builder.append(" IFNULL(finish_num,0) finish_num, IFNULL(close_num,0) close_num from (  ");
		builder.append(" 	select count(b.id) total_num,i.THIRD_CATEGORY_NAME,i.FIRST_CATEGORY_NAME,i.SECOND_CATEGORY_NAME,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i 	 ");
		builder.append(" 	where b.BUSINESS_ID = i.ID  and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId ");
		builder.append(" 	GROUP BY I.THIRD_CATEGORY_ID  ");
		builder.append(" ) mtotal left join (  ");
		builder.append(" 	select count(b.id) answer_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i,(  ");
		builder.append("   		select FLOWID from workflow_node where FLOWSTATE = 1 group by FLOWID ");
		builder.append(" 	) n	where b.BUSINESS_ID = i.ID and b.id = n.FLOWID and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId  ");
		builder.append(" 	 GROUP BY I.THIRD_CATEGORY_ID  ");
		builder.append(" ) manswer on mtotal.THIRD_CATEGORY_ID = manswer.THIRD_CATEGORY_ID left join (  ");
		builder.append(" 	select count(b.id) activi_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i  ");
		builder.append(" 	where b.BUSINESS_ID = i.ID  and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId  ");
		builder.append(" 	and b.WFSTATE=1 GROUP BY I.THIRD_CATEGORY_ID  ");
		builder.append(" ) mactivi on mtotal.THIRD_CATEGORY_ID = mactivi.THIRD_CATEGORY_ID left join (  ");
		builder.append(" 	select count(b.id) sleep_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i  ");
		builder.append(" 	where b.BUSINESS_ID = i.ID  and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId  ");
		builder.append(" 	and b.WFSTATE=6 GROUP BY I.THIRD_CATEGORY_ID  ");
		builder.append(" ) msleep on mtotal.THIRD_CATEGORY_ID = msleep.THIRD_CATEGORY_ID left join (  ");
		builder.append(" 	select count(b.id) finish_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i  ");
		builder.append(" 	where b.BUSINESS_ID = i.ID  and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId  ");
		builder.append(" 	and (b.WFSTATE=2 or b.WFSTATE=4) GROUP BY I.THIRD_CATEGORY_ID  ");
		builder.append(" ) mfinish on mtotal.THIRD_CATEGORY_ID = mfinish.THIRD_CATEGORY_ID left join (  ");
		builder.append(" 	select count(b.id) close_num,i.THIRD_CATEGORY_ID from workflow_base b ,SC_WORKFLOW_QUESTION i	 ");
		builder.append(" 	where b.BUSINESS_ID = i.ID  and b.createtime >:beginDay and b.createtime <:endDay and b.BUSINESSTYPE = 9 and b.CUSTOMER_ID = :cusId  ");
		builder.append(" 	and b.WFSTATE=3 GROUP BY I.THIRD_CATEGORY_ID  ");
		builder.append(" ) mclose on mtotal.THIRD_CATEGORY_ID = mclose.THIRD_CATEGORY_ID  ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		//组织表头
		lebalList.add("服务目录");
		lebalList.add("问题总数");
		lebalList.add("待响应");
		lebalList.add("进行中");
		lebalList.add("已挂起");
		lebalList.add("已解决");
		lebalList.add("已取消");
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("FIRST_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("SECOND_CATEGORY_NAME"))+"->"+ConvertUtil.obj2StrBlank(base.get("THIRD_CATEGORY_NAME")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("answer_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("activi_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("sleep_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("finish_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("close_num")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}
	
////////////////////////////////////////////////////////////////////////////////////////////////////////////
	/**
	 * 将数据字典转化成DataKeyMap
	 * @author chenlong
	 * @param dicList
	 * @return
	 */
	private Map dicListToDataKeyMap(List<Map> dicList){
		Map dataKeyMap = new HashMap();
		for(Map dic:dicList){
			dataKeyMap.put(ConvertUtil.obj2StrBlank(dic.get("DATAKEY")),ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
		}
		return dataKeyMap;
	}
	
	/**
	 * 格式化数据字典
	 * @author chenlong
	 * @param dicList
	 */
	private void formatDicList(List<Map> dicList){
		for(Map dic:dicList){
			dic.put("key", ConvertUtil.obj2StrBlank(dic.get("DATAKEY")));
			dic.put("val", ConvertUtil.obj2StrBlank(dic.get("DATAVALUE")));
		}
	}
	
	/**
	 * 组织时间查询条件
	 * @author chenlong
	 * @param frequency
	 * @param sql
	 * @param colName
	 */
	private void getDateWhereSql(String frequency,StringBuilder sql,String colName){
		if(frequency.equals("day")||frequency==null){
			sql.append(" and date_format("+colName+",'%Y%m%d')>=:startDate and date_format("+colName+",'%Y%m%d')<=:endDate  ");
		}else if(frequency.equals("month")){
			sql.append(" and date_format("+colName+",'%Y%m')>=:startDate and date_format("+colName+",'%Y%m')<=:endDate  ");
		}
	}
	
	@Override
	public Map questionSourcePie(String frequency, String cusId,String startDate, String endDate) {
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List categoryList = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.QUESTION_SOURCE);
		formatDicList(categoryList);
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select count(id) val,source_id category  from sc_workflow_question where 1=1 ");
		if(StringUtil.isNotBlank(cusId)){
			sql.append(" and customer_id = :cusId ");
		}
		getDateWhereSql(frequency,sql,"create_time");
		sql.append(" group by source_id ");
		List datas = baseDao.queryForList(sql.toString(),param);
		return ChartUtil.createHchartPieMap(datas,categoryList,"数量");
	}

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

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

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