package cn.ksource.web.facade.tj;

import cn.ksource.core.dao.BaseDao;
import cn.ksource.core.dao.SqlParameter;
import cn.ksource.core.util.*;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
@Service
public class SatisfactionStatisFacadeImpl implements SatisfactionStatisFacade{
	@Resource
	private BaseDao baseDao;
	
	@Override
	public Map satisfactionDetailChart(HttpServletRequest request) {
		//获取查询参数
		Map param = new HashMap();
		String cusId = request.getParameter("cusId");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String busType = request.getParameter("busType");
		
		
		//查询参数初始化
		param.put("cusId", cusId);
		param.put("cusId", cusId);
		param.put("beginTime", beginTime);
		param.put("endTime", endTime);
		param.put("busType", busType);
		
		//查询最新模板id
		StringBuilder sql = new StringBuilder();
		sql.append(" select id from CUSTOMER_SATIS_TEMPLATE where state=1 and template_type = :busType  ");
		String templateId = baseDao.queryForString(sql.toString(),param);
		param.put("templateId", templateId);

		//组织表头
		sql.setLength(0);
		sql.append(" select id,item_name text,FATHER_ID parentId,ORDERNUM sortId from CUSTOMER_SATIS_TEMPLATE_ITEM  ");
		sql.append(" where TEMPLATE_ID = :templateId  ");
		sql.append(" union ");
		sql.append(" select id,text,parentId,sortId from (   ");
		sql.append(" select i1.id,i1.ITEM_ID parentId,i1.ORDERNUM sortId,i1.EVA_NAME text from satis_eva_item i1   ");
		sql.append(" inner join CUSTOMER_SATIS_TEMPLATE_ITEM i2  on i1.ITEM_ID = i2.ID ");
		sql.append(" where i2.TEMPLATE_ID = :templateId ) t ");
		//将查询结果配装为树
		TreeUtil treeUtil = new TreeUtil();
		Map root = treeUtil.createTree(baseDao.queryForList(sql.toString(),param));
		
		//查询每个日期下对应的各三级满意度调查项的满意度数量
		sql.setLength(0);
		//三级满意度调查项与时间外连接
		sql.append("	select t.$COLUMN,GROUP_CONCAT(IFNULL(i.num,'-') order by ordernum1,ordernum2,ordernum3) total_num from ( ");
		sql.append("		select t2.$COLUMN,t1.resultid,ordernum1,ordernum2,ordernum3 from ");
		sql.append("		( ");
		sql.append("			select v3.id resultid,v1.ORDERNUM ordernum1,v2.ORDERNUM ordernum2,v3.ordernum ordernum3 from ");
		sql.append("			( ");
		sql.append("				select ID,ITEM_NAME,FATHER_ID,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM  ");
		sql.append("				where state=1 and item_level =1 and TEMPLATE_ID = :templateId ");
		sql.append("			) v1 ");
		sql.append("			inner join ");
		sql.append("			(  ");
		sql.append("				select ID,ITEM_NAME,FATHER_ID,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM  ");
		sql.append("				where state=1 and item_level =2 and TEMPLATE_ID = :templateId ");
		sql.append("			) v2 ");
		sql.append("			on v1.ID = v2.FATHER_ID ");
		sql.append("			inner join  ");
		sql.append("			( ");
		sql.append("				select id,item_id,eva_name,ordernum from  satis_eva_item "); 
		sql.append("			) v3  ");
		sql.append("			on v2.id = v3.item_id  ");
		sql.append("		) t1 , ");
		sql.append("		( ");
		sql.append("			select $COLUMN from SATIS_INFO_DETAIL d inner join $TABLE i  ");
		sql.append("			on d.BUSINESS_ID = i.ID ");
		sql.append(" 			where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime  ");
		sql.append(" 			  and i.TEMPLATE_ID =:templateId ");
		if(StringUtil.notEmpty(cusId)){
			sql.append(" and i.customer_id = :cusId ");
		}
		if(StringUtil.notEmpty(cusId) ) {
			sql.append(" AND i.CUSTOMER_ID = :cusId ");
		}
		sql.append("			group by $COLUMN ");
		sql.append("		) t2 ");
		sql.append("	) t  ");
		//将满意度调查项根据调查时间，三级调查项目分组
		sql.append("	left join ");
		sql.append("	( ");
		sql.append("		select count(i.id) num,d.RESULTID,i.$COLUMN from SATIS_INFO_DETAIL d inner join $TABLE i "); 
		sql.append("		on d.BUSINESS_ID = i.ID  ");
		sql.append(" 		where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime  ");
		sql.append(" 		and i.customer_id = :cusId  and i.TEMPLATE_ID =:templateId ");
		if(StringUtil.notEmpty(cusId) ) {
			sql.append(" AND i.CUSTOMER_ID = :cusId  ");
		}
		sql.append("		group by i.$COLUMN,d.RESULTID ");
		sql.append("	) i on t.resultid = i.RESULTID and t.$COLUMN = i.$COLUMN ");
		sql.append("	group by $COLUMN ");
		
		Map resMap = new HashMap();
		String finalSql = sql.toString();
		if(busType.equals("1")||busType.equals("2")){
			finalSql = finalSql.replace("$COLUMN","INVEST_TIME");
			finalSql = finalSql.replace("$TABLE","ORDER_SATIS_INFO");
		}else if(busType.equals("3")||busType.equals("4")){
			finalSql = finalSql.replace("$COLUMN","CARD_MONTH");
			finalSql = finalSql.replace("$TABLE","AUDITING_SATIS_INFO");
		}
		List<Map> resList = baseDao.queryForList(finalSql,param);
		resMap.put("resList", resList);
		resMap.put("root", root);
		return resMap;
	}

	@Override
	public Map satisfactionBigItemChart(HttpServletRequest request) {
		//获取查询参数
		Map param = new HashMap();
		String cusId = request.getParameter("cusId");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String showType = request.getParameter("showType");
		String busType = request.getParameter("busType");
		//查询参数初始化
		param.put("cusId", cusId);
		param.put("beginTime", beginTime);
		param.put("endTime", endTime);
		param.put("busType", busType);
		//查询最新模板id
		StringBuilder sql = new StringBuilder();
		sql.append(" select id from CUSTOMER_SATIS_TEMPLATE where state=1 and template_type = :busType  ");
		String templateId = baseDao.queryForString(sql.toString(),param);
		param.put("templateId", templateId);

		//获取LegendList
		sql.setLength(0);
		sql.append(" select id,item_name name from CUSTOMER_SATIS_TEMPLATE_ITEM ");
		sql.append(" where item_level = 1 and TEMPLATE_ID = :templateId ");
		sql.append(" order by ORDERNUM ");
		List<Map> legendList = baseDao.queryForList(sql.toString(),param);
		//获取xAxis
		List<String> categories = new ArrayList<String>();
		//查询数据
		sql.setLength(0);
		sql.append(" select CONCAT(id1,'_',$COLUMN) mname,SUM(score) score from ");
		sql.append(" ( ");
		sql.append(" 	select i.$COLUMN,t.id1,t.id2,truncate((t.weight*AVG(d.RESULTSCORE))/1000,1) score from  ");
		sql.append(" 	SATIS_INFO_DETAIL d  ");
		sql.append(" 	inner join $TABLE i  ");
		sql.append(" 	on d.BUSINESS_ID = i.ID ");
		sql.append(" 	inner join  ");
		sql.append(" 	( ");
		sql.append(" 		select lv1.id id1,lv1.ITEM_NAME name1,lv2.id id2,lv2.ITEM_NAME name2,weight from  ");
		sql.append(" 		( ");
		sql.append(" 		select id,ITEM_NAME,FATHER_ID,TEMPLATE_WEIGHT weight from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 2 ");
		sql.append(" 		) lv2 ");
		sql.append(" 		inner join  ");
		sql.append(" 		( ");
		sql.append(" 		select id,ITEM_NAME from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 1 ");
		sql.append("		) lv1  ");
		sql.append(" 		on lv2.FATHER_ID = lv1.id ");
		sql.append(" 	) t  ");
		sql.append(" 	on t.id2 = d.CONFIGID ");
		sql.append(" 	where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime  ");
		sql.append(" 	 and i.TEMPLATE_ID =:templateId  ");
		if(StringUtil.notEmpty(cusId)){
			sql.append(" and i.customer_id = :cusId  ");
		}
		if(StringUtil.notEmpty(cusId) ) {
			sql.append(" AND i.CUSTOMER_ID = :cusId  ");
		}
		sql.append(" 	group by i.$COLUMN,t.id1,t.id2,t.weight ");
		sql.append(" )t group by id1,$COLUMN ");
		
		String finalSql = sql.toString();
		if(busType.equals("1")||busType.equals("2")){
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
			finalSql = finalSql.replace("$COLUMN","INVEST_TIME");
			finalSql = finalSql.replace("$TABLE","ORDER_SATIS_INFO");
		}else if(busType.equals("3")||busType.equals("4")){
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
			finalSql = finalSql.replace("$COLUMN","CARD_MONTH");
			finalSql = finalSql.replace("$TABLE","AUDITING_SATIS_INFO");
		}
		List<Map> queryList = baseDao.queryForList(finalSql,param);
		//hightchart series
		List<Map> series = new ArrayList<Map>();
		List<List> tableData = new ArrayList<List>();
		
		Map queryMap = new HashMap();
		if(queryList!=null&&queryList.size()>0){
			for(Map map:queryList){
				queryMap.put(map.get("mname").toString(), map.get("score"));
			}
		}
		
		if(showType.equals("chart")){
			//组装 hightchart series
			for(Map legendMap:legendList){
				Map tempMap = new HashMap();
				tempMap.put("name", legendMap.get("name").toString());
				List tempList = new ArrayList();
				for(String category:categories){
					String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
					if(queryMap.get(name)!=null){
						double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
						tempList.add(score);
					}else{
						tempList.add(0);
					}
				}
				tempMap.put("data", tempList);
				series.add(tempMap);
			}
		}
		//table数据组装
		if(showType.equals("table")){
			for(String category:categories ){
				Map tempMap = new HashMap();
				List tempList = new ArrayList<String>();
				tempList.add(category);
				for(Map legendMap:legendList){
					String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
					if(queryMap.get(name)!=null){
						double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
						tempList.add(score);
					}else{
						tempList.add("-");
					}
				}
				tableData.add(tempList);
			}
		}
		//System.out.println("table------------------"+JsonUtil.list2Json(tableData));
		//返回结果
		Map resMap = new HashMap();
		resMap.put("categories", categories);
		resMap.put("series", series);
		resMap.put("step", Math.round(categories.size()/9));
		
		resMap.put("tableData", tableData);
		resMap.put("tableLabel", legendList);
		
		return resMap;
	}
	
	
	@Override
	public Map satisfactionSmallItemChart(HttpServletRequest request) {
		//获取查询参数
		Map param = new HashMap();
		String cusId = request.getParameter("cusId");
		String beginTime = request.getParameter("beginTime");
		String endTime = request.getParameter("endTime");
		String showType = request.getParameter("showType");
		String busType = request.getParameter("busType");
		//查询参数初始化
		param.put("cusId", cusId);
		param.put("beginTime", beginTime);
		param.put("endTime", endTime);
		param.put("busType", busType);
		//查询最新模板id
		StringBuilder sql = new StringBuilder();
		sql.append(" select id from CUSTOMER_SATIS_TEMPLATE where state=1 and template_type = :busType  ");
		String templateId = baseDao.queryForString(sql.toString(),param);
		param.put("templateId", templateId);
				
		//获取LegendList
		sql.setLength(0);
		sql.append(" select v2.id id,v2.name name from  ");
		sql.append(" ( ");
		sql.append(" select id,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM   ");
		sql.append(" where item_level = 1 and TEMPLATE_ID = :templateId    ");
		sql.append(" ) v1   ");
		sql.append(" inner join   ");
		sql.append(" (   ");
		sql.append(" select id,item_name name,FATHER_ID pid,ORDERNUM from CUSTOMER_SATIS_TEMPLATE_ITEM    ");
		sql.append(" where item_level = 2 and TEMPLATE_ID = :templateId     ");
		sql.append(" ) v2     ");
		sql.append(" on v1.id = v2.pid    ");
		sql.append(" order by  v1.ORDERNUM,v2.ORDERNUM    ");
		List<Map> legendList = baseDao.queryForList(sql.toString(),param);
		//获取xAxis
		List<String> categories = new ArrayList<String>();
		
		//查询数据
		sql.setLength(0);
		sql.append(" select CONCAT(t.id2,'_',i.$COLUMN) mname, truncate(AVG(d.RESULTSCORE)/10,1) score from   ");
		sql.append(" SATIS_INFO_DETAIL d   ");
		sql.append(" inner join $TABLE i  ");
		sql.append(" on d.BUSINESS_ID = i.ID ");
		sql.append(" inner join  ");
		sql.append(" ( ");
		sql.append(" 	select lv1.id id1,lv1.ITEM_NAME name1,lv2.id id2,lv2.ITEM_NAME name2,weight from  ");
		sql.append(" 	( ");
		sql.append(" 	select id,ITEM_NAME,FATHER_ID,TEMPLATE_WEIGHT weight from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 2 and TEMPLATE_ID = :templateId ");
		sql.append(" 	) lv2 ");
		sql.append(" 	inner join  ");
		sql.append(" 	( ");
		sql.append(" 	select id,ITEM_NAME from CUSTOMER_SATIS_TEMPLATE_ITEM where item_level = 1 and TEMPLATE_ID = :templateId ");
		sql.append("	) lv1  ");
		sql.append(" 	on lv2.FATHER_ID = lv1.id ");
		sql.append(" ) t  ");
		sql.append(" on t.id2 = d.CONFIGID ");
		sql.append(" 	where i.$COLUMN >=:beginTime and i.$COLUMN <=:endTime  ");
		sql.append(" 	  and i.TEMPLATE_ID =:templateId  ");
		if(StringUtil.notEmpty(cusId)){
			sql.append(" and i.customer_id = :cusId ");
		}
		if(StringUtil.notEmpty(cusId)) {
			sql.append(" AND i.CUSTOMER_ID = :cusId  ");
		}
		sql.append(" group by i.$COLUMN,t.id1,t.id2 ");
		
		
		String finalSql = sql.toString();
		if(busType.equals("1")||busType.equals("2")){
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 2, "yyyyMMdd");
			finalSql = finalSql.replace("$COLUMN","INVEST_TIME");
			finalSql = finalSql.replace("$TABLE","ORDER_SATIS_INFO");
		}else if(busType.equals("3")||busType.equals("4")){
			categories = DateUtil.getBetweenTwoDateList(beginTime, endTime, 1, "yyyyMM");
			finalSql = finalSql.replace("$COLUMN","CARD_MONTH");
			finalSql = finalSql.replace("$TABLE","AUDITING_SATIS_INFO");
		}
		List<Map> queryList = baseDao.queryForList(finalSql,param);
		//hightchart series
		List<Map> series = new ArrayList<Map>();
		List<List> tableData = new ArrayList<List>();
		Map queryMap = new HashMap();
		if(queryList!=null&&queryList.size()>0){
			for(Map map:queryList){
				queryMap.put(map.get("mname").toString(), map.get("score"));
			}
		}
		//组装 hightchart series
		if(showType.equals("chart")){
			
			for(Map legendMap:legendList){
				Map tempMap = new HashMap();
				tempMap.put("name", legendMap.get("name").toString());
				List tempList = new ArrayList();
				for(String category:categories){
					String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
					if(queryMap.get(name)!=null){
						double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
						tempList.add(score);
					}else{
						tempList.add(0);
					}
				}
				tempMap.put("data", tempList);
				series.add(tempMap);
			}
		}
		//组装talbe数据
		if(showType.equals("table")){
			for(String category:categories ){
				Map tempMap = new HashMap();
				List tempList = new ArrayList<String>();
				tempList.add(category);
				for(Map legendMap:legendList){
					String name = legendMap.get("id").toString()+"_"+category.replace("-", "");
					if(queryMap.get(name)!=null){
						double score=ConvertUtil.obj2Double(queryMap.get(name))*10;
						tempList.add(score);
					}else{
						tempList.add("-");
					}
				}
				tableData.add(tempList);
			}
		}
		System.out.println("tableData--------------------"+JsonUtil.list2Json(tableData));
		sql.setLength(0);
		sql.append(" select id,item_name text,FATHER_ID parentId,ORDERNUM sortId from CUSTOMER_SATIS_TEMPLATE_ITEM  ");
		sql.append(" where TEMPLATE_ID = :templateId  and (ITEM_LEVEL =1 or ITEM_LEVEL =2) ");
		TreeUtil treeUtil = new TreeUtil();
		Map root = treeUtil.createTree(baseDao.queryForList(sql.toString(),param));
		
		//返回结果
		Map resMap = new HashMap();
		
		resMap.put("categories", categories);
		resMap.put("series", series);
		resMap.put("step", Math.round(categories.size()/9));
		resMap.put("tableData", tableData);
		resMap.put("tableLabel", root);
		return resMap;
	}


	@Override
	public Map satisfactionMonthChart(HttpServletRequest request) {
		String cusId = request.getParameter("cusId");
		String showType = request.getParameter("showType");
		
		String selMonth = request.getParameter("selMonth");
		
		StringBuilder sql = new StringBuilder();
		Map param = new HashMap();
		param.put("selMonth", selMonth);
		param.put("cusId", cusId);
		List<String> categories = new ArrayList<String>();
		categories.add("事件响应类得分");
		categories.add("驻场服务得分");
		categories.add("驻场响应类得分");
		categories.add("总得分");
		
		sql.setLength(0);
		sql.append(" select i.month,i.all_score,d.satis_score from totle_satis_info i, totle_satis_detail d ");
		sql.append(" where i.id = d.bus_id and i.month = :selMonth and i.customer_id = :cusId  order by d.satis_type asc ");
		List<Map> queryList = baseDao.queryForList(sql.toString(),param);
		Map resMap = new HashMap();
		Map queryMap = new HashMap();
		if(queryList!=null&&queryList.size()>0){
			for(int i=0;i<queryList.size();i++){
				if(i==0){
					queryMap.put("XYL_SCORE",queryList.get(i).get("satis_score"));
				}else if(i==1){
					queryMap.put("ZC_SCORE",queryList.get(i).get("satis_score"));
					queryMap.put("ALL_SCORE",queryList.get(i).get("all_score"));
				}else if(i==2){
					queryMap.put("ZCXYL_SCORE",queryList.get(i).get("satis_score"));
				}
			}
		}
		
		if(showType.equals("chart")){
			Map tempMap = new HashMap();
			List<Map> series = new ArrayList<Map>();
			List tempList = new ArrayList();
			tempMap.put("name", DateUtil.format("yyyy-MM", selMonth));
			tempList.add(ConvertUtil.obj2Double(queryMap.get("XYL_SCORE")));
			tempList.add(ConvertUtil.obj2Double(queryMap.get("ZC_SCORE")));
			tempList.add(ConvertUtil.obj2Double(queryMap.get("ALL_SCORE")));
			tempMap.put("data", tempList);
			series.add(tempMap);
			resMap.put("series", series);
			resMap.put("categories", categories);
		}else{
			queryMap.put("MONTH", DateUtil.format("yyyy-MM", selMonth));
			resMap.put("tableLabel", categories);
			resMap.put("tableData",queryMap);
		}
		return resMap;
	}
	
	@Override
	public Map querySatisReport(String cusId,String beginDay,String endDay) {
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay);
		paramMap.put("endDay", endDay);
		//定义报表变量
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		//查询数据
		StringBuilder builder = new StringBuilder();
		builder.append(" select d.SATIS_TYPE,d.REQUEST_FG_RATE,d.SATIS_QZ,d.REQUEST_SATIS_SCORE,sum(d.ALL_COUNT) total_num,SUM(d.SATIS_COUNT) do_num,sum(d.ALL_COUNT-SATIS_COUNT) undo_num, ");
		builder.append(" truncate(avg(d.FG_RATE),2) cover_rate,truncate(avg(d.SATIS_SCORE),2) score  ");
		builder.append(" from TOTLE_SATIS_INFO i ,TOTLE_SATIS_DETAIL d ");
		builder.append(" where i.ID = d.BUS_ID and i.MONTH >= DATE_FORMAT(:beginDay,'%Y%m') and i.MONTH<=DATE_FORMAT(:endDay,'%Y%m')  ");
		if(StringUtil.notEmpty(cusId)){
			builder.append(" and i.CUSTOMER_ID = :cusId ");
		}
		builder.append(" group by d.SATIS_TYPE order by d.SATIS_TYPE ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		//组织表头
		lebalList.add("调查类型");
		lebalList.add("计划数");
		lebalList.add("已调查数");
		lebalList.add("未完成数");
		lebalList.add("要求覆盖率");
		lebalList.add("实际覆盖率");
		lebalList.add("得分权重");
		lebalList.add("要求得分");
		lebalList.add("满意度得分");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				if(ConvertUtil.obj2StrBlank(base.get("SATIS_TYPE")).equals("1")){
					tempList.add("事件响应支持类");
				}else if(ConvertUtil.obj2StrBlank(base.get("SATIS_TYPE")).equals("3")){
					tempList.add("驻场服务类");
				}else if(ConvertUtil.obj2StrBlank(base.get("SATIS_TYPE")).equals("6")){
					tempList.add("驻场响应支持类");
				}
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				
				
				
				tempList.add(ConvertUtil.obj2StrBlank(base.get("do_num")));
				if(ConvertUtil.obj2StrBlank(base.get("undo_num")).indexOf("-")>=0){
					tempList.add("0");
				}else{
					tempList.add(ConvertUtil.obj2StrBlank(base.get("undo_num")));
				}
				tempList.add(ConvertUtil.obj2StrBlank(base.get("REQUEST_FG_RATE"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("cover_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("SATIS_QZ")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("REQUEST_SATIS_SCORE")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("score")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}


	@Override
	public Map queryEngineerSatisReport(HttpServletRequest request) {
		String cusId = request.getParameter("cusId");
		String beginDay = request.getParameter("beginDay");
		String endDay = request.getParameter("endDay");
		
		Map paramMap = new HashMap();
		paramMap.put("cusId", cusId);
		paramMap.put("beginDay", beginDay);
		paramMap.put("endDay", endDay);
		paramMap.put("beginTime", beginDay+"666666");
		paramMap.put("endTime", endDay+"666666");
		
		String sql = " select  c.SATIS_BUSTYPE from KPI_SATIS_TEMPLATE t,KPI_CONFIG c  "  
				+ " where t.ID = c.TEMPLATE_ID and t.STATE=1 and c.SATIS_TYPE = 2  "; 
		String businessTypes = baseDao.queryForString(sql,paramMap);
		if(StringUtil.isNotBlank(businessTypes)){
			businessTypes = businessTypes + ",'8'";
		}else{
			businessTypes = "'8'";
		}
		
		//定义报表变量
		List<String> lebalList = new ArrayList<String>();  
		List<List<String>> dataList = new ArrayList<List<String>>();  
		//查询数据
		StringBuilder builder = new StringBuilder();
		builder.append(" select user_name,total_num,IFNULL(invest_num,0) invest_num, ");
		builder.append(" truncate(IFNULL(invest_num,0)*100/total_num,2) cover_rate,truncate(score,2) score from (  ");
		builder.append(" 	select count(b.ID) total_num,user_name,user_id from workflow_base b,( ");
		builder.append(" 		SELECT FLOWID,current_dealer_id user_id,current_dealer_name user_name  ");
		builder.append(" 		FROM workflow_node n,( ");
		builder.append(" 			SELECT u.USER_ID FROM SC_SERVICER_GROUP g,SC_SERVICER_GROUP_USER u ");
		builder.append(" 			WHERE g.ID = u.GROUP_ID AND PROJECT_ID = :cusId AND GROUP_TYPE in (3,4,5) group by u.USER_ID ");
		builder.append(" 		) u WHERE n.current_dealer_id  = u.USER_ID GROUP BY USER_ID,FLOWID ");
		builder.append(" 	) t where b.ID = t.FLOWID and b.PROJECT_ID=:cusId and b.BUSINESSTYPE in ("+businessTypes+") ");
		builder.append(" 	and b.CREATETIME>:beginTime and b.CREATETIME<:endTime GROUP BY t.user_id ");
		builder.append(" ) mtotal left join ( ");
		builder.append(" 	select count(id) invest_num,INVEST_USER_ID,avg(SCORE) score from ORDER_SATIS_INFO  ");
		builder.append(" 	where INVEST_TIME >:beginDay and INVEST_TIME<:endDay and PROJECT_ID = :cusId group by INVEST_USER_ID ");
		builder.append(" ) minvest on mtotal.user_id=minvest.INVEST_USER_ID ");
		List<Map> baseList = baseDao.queryForList(builder.toString(),paramMap);
		//组织表头
		lebalList.add("人员");
		lebalList.add("处理工单数");
		lebalList.add("调查数");
		lebalList.add("覆盖率");
		lebalList.add("平均满意度得分");
		
		//组织数据
		if(baseList!=null&&baseList.size()>0){
			for(Map base:baseList){
				List<String> tempList = new ArrayList<String>();
				tempList.add(ConvertUtil.obj2StrBlank(base.get("user_name")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("total_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("invest_num")));
				tempList.add(ConvertUtil.obj2StrBlank(base.get("cover_rate"))+"%");
				tempList.add(ConvertUtil.obj2StrBlank(base.get("score")));
				dataList.add(tempList);
			}
		}
		Map res = new HashMap();
		res.put("lebalList",lebalList);
		res.put("dataList",dataList);
		return res;
	}

////////////////////////////////////////////////////////////////////////////////////////////////
	
	@Override
	public Map satisTrendLine(String cusId,String startDate, String endDate) {
		SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
		Calendar cal = Calendar.getInstance();
		try {
			cal.setTime(df.parse(startDate));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		cal.add(Calendar.MONTH, -1);
		startDate = df.format(cal.getTime());
		
		Map chartMap = new HashMap();
		StringBuilder builder = new StringBuilder();
		builder.append(" SELECT I.ALL_SCORE,D.SATIS_SCORE,D.SATIS_TYPE,I.MONTH FROM TOTLE_SATIS_DETAIL D ,TOTLE_SATIS_INFO I ");
		builder.append(" WHERE D.BUS_ID = I.ID AND I.MONTH>=:startDate AND I.MONTH<=:endDate  ");
		if(StringUtil.isNotBlank(cusId)){
			builder.append(" and I.CUSTOMER_ID=:cusId ");
		}
		builder.append(" GROUP BY D.SATIS_TYPE,I.MONTH  ORDER BY SATIS_TYPE,MONTH ");
		
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		
		List<Map> queryList = baseDao.queryForList(builder.toString(), param);
		builder.setLength(0);	
		Map<String,Object> queryMap = new HashMap<String,Object>();
		
		if(queryList!=null&&queryList.size()>0){
			for(Map map:queryList){
				String key = map.get("SATIS_TYPE").toString()+"_"+map.get("MONTH").toString();
				queryMap.put(key, map);
			}
			List<Map> series = new ArrayList<Map>();
			List<String> categories = DateUtil.getIntervalMonths(Long.valueOf(startDate),Long.valueOf(endDate));
			List<String> categories_format = new  ArrayList<String>();
			List<String> typeList = new ArrayList();
			typeList.add("1");
			typeList.add("2");
			typeList.add("3");
			typeList.add("4");
			
			Map tempWholeMap = new HashMap();
			List tempWholeList = new ArrayList();
			tempWholeMap.put("name", "整体得分");
			tempWholeMap.put("type", "spline");
			//组装数据
			for(String type:typeList){
				Map tempMap = new HashMap();
				List tempList = new ArrayList();
				if(type.equals("1")){
					tempMap.put("name", "响应支持类");
				}else if(type.equals("2")){
					tempMap.put("name", "预防性维护类");
				}else if(type.equals("3")){
					tempMap.put("name", "驻场服务类");
				}else if(type.equals("4")){
					tempMap.put("name", "综合类满意度");
				}
				for(String cate:categories){
					if(type.equals("1")){
						categories_format.add(DateUtil.format("yyyy-MM", cate));
					}
					
					String key = type + "_" + cate;
					if(queryMap.get(key)!=null){
						tempList.add(((Map)queryMap.get(key)).get("SATIS_SCORE"));
						//整体得分
						if(type.equals("1")){
							tempWholeList.add(((Map)queryMap.get(key)).get("ALL_SCORE"));
						}
					}else{
						tempList.add(0);
						//整体得分
						if(type.equals("1")){
							tempWholeList.add(0);
						}
					}
				}
				
				tempMap.put("data", tempList);
				tempMap.put("type", "column");
				series.add(tempMap);
				if(type.equals("4")){
					tempWholeMap.put("data", tempWholeList);
					series.add(tempWholeMap);
				}
				chartMap.put("series", series);
				chartMap.put("categories", categories_format);
			}
			chartMap.put("nodata", false);
		}else{
			chartMap.put("nodata", true);
		}
		return chartMap;
	}


	@Override
	public Map satisCoverLine(String cusId,String startDate, String endDate) {
		SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
		Calendar cal = Calendar.getInstance();
		try {
			cal.setTime(df.parse(startDate));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		cal.add(Calendar.MONTH, -1);
		startDate = df.format(cal.getTime());
		
		Map chartMap = new HashMap();
		StringBuilder builder = new StringBuilder();
		builder.append(" SELECT D.FG_RATE,D.SATIS_TYPE,I.MONTH FROM TOTLE_SATIS_DETAIL D ,TOTLE_SATIS_INFO I ");
		builder.append(" WHERE D.BUS_ID = I.ID AND I.MONTH>=:startDate AND I.MONTH<=:endDate  ");
		if(StringUtil.isNotBlank(cusId)){
			builder.append(" and I.CUSTOMER_ID=:cusId ");
		}
		builder.append(" GROUP BY D.SATIS_TYPE,I.MONTH  ORDER BY SATIS_TYPE,MONTH");
		
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startDate", startDate)
			.addValue("endDate", endDate);
		List<Map> queryList = baseDao.queryForList(builder.toString(), param);
		builder.setLength(0);	
		Map<String,Object> queryMap = new HashMap<String,Object>();
		
		if(queryList!=null&&queryList.size()>0){
			for(Map map:queryList){
				String key = map.get("SATIS_TYPE").toString()+"_"+map.get("MONTH").toString();
				queryMap.put(key, map);
			}
			List<Map> series = new ArrayList<Map>();
			List<String> categories = DateUtil.getIntervalMonths(Long.valueOf(startDate), Long.valueOf(endDate));
			List<String> categories_format = new  ArrayList<String>();
			List<String> typeList = new ArrayList();
			typeList.add("1");
			typeList.add("2");
			typeList.add("3");
			typeList.add("4");
			
			//组装数据
			for(String type:typeList){
				Map tempMap = new HashMap();
				List tempList = new ArrayList();
				if(type.equals("1")){
					tempMap.put("name", "响应支持类");
				}else if(type.equals("2")){
					tempMap.put("name", "预防性维护类");
				}else if(type.equals("3")){
					tempMap.put("name", "驻场服务类");
				}else if(type.equals("4")){
					tempMap.put("name", "综合类满意度");
				}
				for(String cate:categories){
					if(type.equals("1")){
						categories_format.add(DateUtil.format("yyyy-MM", cate));
					}
					String key = type + "_" + cate;
					if(queryMap.get(key)!=null){
						tempList.add(((Map)queryMap.get(key)).get("FG_RATE"));
					}else{
						tempList.add(0);
					}
				}
				
				tempMap.put("data", tempList);
				series.add(tempMap);
				chartMap.put("series", series);
				chartMap.put("categories", categories_format);
			}
		}
		return chartMap;
	}
	
}