package cn.ksource.web.facade.desktop;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

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.facade.customermanage.CustomerManageFacade;

@Service("desktopFacade")
public class DesktopFacadeImpl implements DesktopFacade {
	
	@Resource
	private BaseDao baseDao;
	@Resource
	private CustomerManageFacade customerFacade;
	
	@Override
	public Map getOperationDeskDetail(String cusId) {
		String incidentAddNum = getIncidentAddNum(cusId);
		String incidentDealNum = getIncidentDealNum(cusId);
		String questionAddNum = getQuestionAddNum(cusId);
		String questionDealNum = getQuestionDealNum(cusId);
		String incidentLocalAddNum = getIncidentLocalAddNum(cusId);
		String incidentLocalDealNum = getIncidentLocalDealNum(cusId);
		String dailyPatrolAddNum = getDailyPatrolAddNum(cusId);
		String dailyPatrolDealNum = getDailyPatrolDealNum(cusId);
		String remindAddNum = getRemindAddNum(cusId);
		String remindDealNum = getRemindDealNum(cusId);
		String healthAddNum = getHealthAddNum(cusId);
		String healthDealNum = getHealthDealNum(cusId);
		String knowlegeAddNum = getKnowlegeAddNum(cusId);
		String knowlegeDealNum = getKnowlegeDealNum(cusId);
		String ciAddNum = getCiAddNum(cusId);
		String ciDealNum = getCiDealNum(cusId);
		String satifactionAddNum = getSatifactionAddNum(cusId);
		Map satifactionScore = getSatifactionScore(cusId);
		String satiScore = "0/0";
		if(!StringUtil.isEmpty(ConvertUtil.obj2StrBlank(satifactionScore.get("ALL_SCORE")))){
			double satifactionScore1 = NumberUtil.round(ConvertUtil.obj2Double(satifactionScore.get("ALL_SCORE")), 0);
			double satifactionScore2 = NumberUtil.round(ConvertUtil.obj2Double(satifactionScore.get("REQUEST_SCORE")), 0);
			satiScore = ConvertUtil.obj2StrBlank(satifactionScore1)+"/"+ConvertUtil.obj2StrBlank(satifactionScore2);
		}
		String fileAddNum = getFileAddNum();
		String fileDealNum = getFileDealNum();
		Map info = new HashMap();
		info.put("incidentAddNum", incidentAddNum);
		info.put("incidentDealNum", incidentDealNum);
		info.put("questionAddNum", questionAddNum);
		info.put("questionDealNum", questionDealNum);
		info.put("incidentLocalAddNum", incidentLocalAddNum);
		info.put("incidentLocalDealNum", incidentLocalDealNum);
		info.put("dailyPatrolAddNum", dailyPatrolAddNum);
		info.put("dailyPatrolDealNum", dailyPatrolDealNum);
		info.put("remindAddNum", remindAddNum);
		info.put("remindDealNum", remindDealNum);
		info.put("healthAddNum", healthAddNum);
		info.put("healthDealNum", healthDealNum);
		info.put("knowlegeAddNum", knowlegeAddNum);
		info.put("knowlegeDealNum", knowlegeDealNum);
		info.put("ciAddNum", ciAddNum);
		info.put("ciDealNum", ciDealNum);
		info.put("satifactionAddNum", satifactionAddNum);
		info.put("satifactionScore", satiScore);
		info.put("fileAddNum", fileAddNum);
		info.put("fileDealNum", fileDealNum);
		return info;
	}
	
	/**
	 * 事件新增数量
	 * @param cusId
	 * @return
	 */
	private String getIncidentAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_INCIDENT where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_INCIDENT where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW())  and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_INCIDENT where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 事件处理数量
	 * @param cusId
	 * @return
	 */
	private String getIncidentDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append("  ");
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_INCIDENT c,workflow_base b,workflow_node n ");
		builder.append(" 		where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and n.FLOWSTATE = 1 and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_INCIDENT c,workflow_base b ");
		builder.append(" 		where c.FLOW_ID = b.id ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and b.wfstate=1 ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(ID) num from SC_WORKFLOW_INCIDENT  where DATE_FORMAT(CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')  ");
		builder.append(" 		and (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and CUSTOMER_ID=:cusId  ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	
	/**
	 * 问题新增数量
	 * @param cusId
	 * @return
	 */
	private String getQuestionAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_QUESTION where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_QUESTION where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_QUESTION where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 问题处理数量
	 * @param cusId
	 * @return
	 */
	private String getQuestionDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_QUESTION c,workflow_base b,workflow_node n ");
		builder.append(" 		where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')  and n.DEAL_TYPE = 1 and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_QUESTION c,workflow_base b ");
		builder.append(" 		where c.FLOW_ID = b.id ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')  and b.wfstate=1 ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_QUESTION c,workflow_base b,workflow_node n ");
		builder.append(" 		where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and n.NODE_TEMPLATE_ID = :nodeTemplateId ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId and b.WFSTATE=1  ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		SqlParameter param = new SqlParameter();
		param.addValue("cusId",cusId)
			.addValue("nodeTemplateId",Constants.WTHG);
		return baseDao.queryForString(builder.toString(),param);
	}
	
	
	/**
	 * 驻场运维新增数量
	 * @param cusId
	 * @return
	 */
	private String getIncidentLocalAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_INCIDENT_LOCAL where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_INCIDENT_LOCAL where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW())  and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_INCIDENT_LOCAL where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	
	/**
	 * 驻场运维新处理数量
	 * @param cusId
	 * @return
	 */
	private String getIncidentLocalDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append("  ");
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_INCIDENT_LOCAL c,workflow_base b,workflow_node n ");
		builder.append(" 		where c.FLOW_ID = b.id and n.ID = b.CURRENT_NODE_ID ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and n.DEAL_TYPE = 1 and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_INCIDENT_LOCAL c,workflow_base b ");
		builder.append(" 		where c.FLOW_ID = b.id ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and b.wfstate=1 ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(ID) num from SC_WORKFLOW_INCIDENT_LOCAL  where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m')  ");
		builder.append(" 		and (ANSWER_TIMEOUT=1 or DEAL_TIMEOUT=1) and CUSTOMER_ID=:cusId  ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	
	/**
	 * 日常巡检新增数量
	 * @param cusId
	 * @return
	 */
	private String getDailyPatrolAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from CI_DAILY_PATROL where DATE_FORMAT(PATROL_DATE,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUS_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from CI_DAILY_PATROL where  YEARWEEK(PATROL_DATE)=YEARWEEK(NOW()) and CUS_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from CI_DAILY_PATROL where   DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUS_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 日常巡检处理数量
	 * @param cusId
	 * @return
	 */
	private String getDailyPatrolDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from CI_DAILY_PATROL where  DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m')    ");
		builder.append(" 	and STATE=1 and CUS_ID=:cusId  ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from CI_DAILY_PATROL where  DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m')    ");
		builder.append(" 	and STATE=2 and CUS_ID=:cusId  ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from CI_DAILY_PATROL where  DATE_FORMAT(PATROL_DATE,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m')    ");
		builder.append(" 	and STATE=3 and CUS_ID=:cusId  ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	
	/**
	 * 例行巡检新增数量
	 * @param cusId
	 * @return
	 */
	private String getRemindAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_CI_REMIND where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_CI_REMIND where  YEARWEEK(CREATE_TIME)=YEARWEEK(NOW())  and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_CI_REMIND where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 例行巡检处理数量
	 * @param cusId
	 * @return
	 */
	private String getRemindDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(t.ID) num from CI_REMIND_TIMERTASK t ,CI_REMIND_CUSTOMER_ITEM i ");
		builder.append(" 		where t.CUSTOMER_ITEM_ID = i.ID and t.STATE=1 ");
		builder.append(" 		and DATE_FORMAT(t.PLAN_EXECUTION_DATE,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and i.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_CI_REMIND c,workflow_base b ");
		builder.append(" 		where c.FLOW_ID = b.id ");
		builder.append(" 		and  DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and b.WFSTATE=1 ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_CI_REMIND c,workflow_base b ");
		builder.append(" 		where c.FLOW_ID = b.id ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and b.WFSTATE=2 ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 健康检查新增数量
	 * @param cusId
	 * @return
	 */
	private String getHealthAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_CI_HEALTH where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_CI_HEALTH where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW())  and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from SC_WORKFLOW_CI_HEALTH where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
		builder.append(" ) t ");
		
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 健康检查处理数量
	 * @param cusId
	 * @return
	 */
	private String getHealthDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(t.ID) num from CI_HEALTH_PLAN_TIMER t ,CI_HEALTH_PLAN p  ");
		builder.append(" 		where t.PLAN_ID = p.ID and p.CUS_ID = :cusId  ");
		builder.append(" 		and t.STATE = 1 and DATE_FORMAT(t.PLAN_EXE_DATE,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_CI_HEALTH c,workflow_base b,workflow_node n ");
		builder.append(" 		where c.FLOW_ID = b.id and b.wfstate=1 and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from SC_WORKFLOW_CI_HEALTH c,workflow_base b,workflow_node n ");
		builder.append(" 		where c.FLOW_ID = b.id and b.wfstate=2 and DATE_FORMAT(c.CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')  ");
		builder.append(" 		and c.CUSTOMER_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 知识库新增数量
	 * @param cusId
	 * @return
	 */
	private String getKnowlegeAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from KM_LIBRARY_TEMP where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d')  ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from KM_LIBRARY_TEMP where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW())  ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from KM_LIBRARY_TEMP where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m')  ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 知识库处理数量
	 * @param cusId
	 * @return
	 */
	private String getKnowlegeDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from KM_LIBRARY_TEMP c ");
		builder.append(" 		where 1=1 ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') and c.audit_state=1 ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from KM_LIBRARY c ");
		builder.append(" 		where 1=1 ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	
	
	
	
	
	/**
	 * 配置库新增数量
	 * @param cusId
	 * @return
	 */
	private String getCiAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from CMDB_CI_BASE where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUS_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from CMDB_CI_BASE where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW())  and CUS_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from CMDB_CI_BASE where  DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUS_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 配置库处理数量
	 * @param cusId
	 * @return
	 */
	private String getCiDealNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from CMDB_CI_BASE c");
		builder.append(" 		where 1=1 ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') and c.STATE=0 ");
		builder.append(" 		and c.CUS_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(c.ID) num from CMDB_CI_BASE c ");
		builder.append(" 		where 1=1 ");
		builder.append(" 		and DATE_FORMAT(c.CREATE_TIME,'%y%M') = DATE_FORMAT(now(),'%y%M') and c.STATE=1 ");
		builder.append(" 		and c.CUS_ID=:cusId ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	
	/**
	 * 满意度调查新增数量
	 * @param cusId
	 * @return
	 */
	private String getSatifactionAddNum(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from ORDER_SATIS_INFO where DATE_FORMAT(INVEST_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from ORDER_SATIS_INFO where YEARWEEK(INVEST_TIME)=YEARWEEK(NOW())  and CUSTOMER_ID=:cusId ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from ORDER_SATIS_INFO where  DATE_FORMAT(INVEST_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and CUSTOMER_ID=:cusId ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("cusId",cusId));
	}
	
	/**
	 * 满意度调查得分
	 * @param cusId
	 * @return
	 */
	private Map getSatifactionScore(String cusId){
		StringBuilder builder = new StringBuilder();
		builder.append(" select ALL_SCORE,REQUEST_SCORE from TOTLE_SATIS_INFO  ");
		builder.append(" where  month= DATE_FORMAT(now(),'%Y%m') and CUSTOMER_ID = :cusId  ");
		return baseDao.queryForMap(builder.toString(),new SqlParameter("cusId",cusId));
	}

	@Override
	public Map getCusSatisfactionSurvey(HttpServletRequest request) {
		List<Map> cusList = customerFacade.getCusList();
		StringBuilder builder = new StringBuilder();
		builder.append(" select i.customer_id id,i.all_score score  ");
		builder.append(" from totle_satis_info i ");
		builder.append(" where  i.month = date_format(now(),'%Y%m')  ");
		builder.append(" group by customer_id ");
		
		List<Map> dataList = baseDao.queryForList(builder.toString());
		Map dataListSet = new HashMap();
		if(dataList!=null&&dataList.size()>0){
			for(Map map:dataList){
				dataListSet.put(map.get("id").toString(), map.get("score"));
			}
		}
		
		List<String> categories = new ArrayList<String>();
		for(Map map:cusList){
			String cusName = ConvertUtil.obj2StrBlank(map.get("name"));
			categories.add(cusName);
		}
		
	    List series = new ArrayList<Integer>();
		for(Map cus:cusList){
			String key =  ConvertUtil.obj2StrBlank(cus.get("id"));
			if(dataListSet.get(key)!=null){
				series.add(ConvertUtil.obj2Double(dataListSet.get(key)));
			}else{
				series.add(0);
			}
		}
		Map surveyMap = new HashMap();
		surveyMap.put("series",series);
		surveyMap.put("categories",categories);
		return surveyMap;
	}

	@Override
	public Map getCusSatisfactionDetail(String cusId) {
		StringBuilder builder = new StringBuilder();
		customerFacade.getCusById(cusId);
		Map cusInfo = customerFacade.getCusById(cusId);
		
		builder.setLength(0);
		builder.append(" select CONCAT(d.SATIS_TYPE,'_',i.MONTH) ID,d.SATIS_SCORE SCORE   ");
		builder.append(" from TOTLE_SATIS_INFO i,TOTLE_SATIS_DETAIL d  ");
		builder.append(" where i.ID = d.BUS_ID  ");
		builder.append(" and left(i.MONTH,4) = DATE_FORMAT(now(),'%Y')  ");
		builder.append(" and i.CUSTOMER_ID = :cusId   ");
		builder.append(" group by i.MONTH,d.SATIS_TYPE  ");
		List<Map> dataList = baseDao.queryForList(builder.toString(),new SqlParameter("cusId",cusId));
		Map dataListMap = new HashMap();
		if(dataList!=null&&dataList.size()>0){
			for(Map map:dataList){
				dataListMap.put(map.get("ID").toString(), map.get("SCORE"));
			}
		}
		//组合十二月
		List<String> categs = new ArrayList<String>();
		String curYear = ConvertUtil.obj2StrBlank(DateUtil.getYear());
		List<String> categories = new ArrayList<String>();
		for(int i=1;i<=12;i++){
			if(i<10){
				categs.add(curYear+"0"+i);
				categories.add(curYear+"-0"+i);
			}else{
				categs.add(curYear+i);
				categories.add(curYear+"-"+i);
			}
		}
		//组装图表数据
		List<String> labelList = new ArrayList<String>();
		labelList.add("1");
		labelList.add("3");
		labelList.add("6");
		
		List<Map> series = new ArrayList<Map>();
		for(String label:labelList){
			Map tempMap = new HashMap();
			List tempList = new ArrayList();
			for(String categorie:categs){
				String key = label + "_"+ categorie;
				if(dataListMap.get(key)!=null){
					tempList.add(dataListMap.get(key));
				}else{
					tempList.add(0);
				}
			}
			if(label.equals("1")){
				tempMap.put("name","事件响应支持类");
			}else if(label.equals("3")){
				tempMap.put("name","驻场服务类");
			}else if(label.equals("6")){
				tempMap.put("name","驻场响应支持类");
			}
			tempMap.put("data", tempList);
			series.add(tempMap);
		}
		Map surveyMap = new HashMap();
		surveyMap.put("series",series);
		surveyMap.put("categories",categories);
		surveyMap.put("cusInfo",cusInfo);
		return surveyMap;
	}
	
	/**
	 * 文档新增数量
	 * @param cusId
	 * @return
	 */
	private String getFileAddNum(){
		StringBuilder builder = new StringBuilder();
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	select count(id) num from GG_FILES where DATE_FORMAT(CREATE_TIME,'%Y%m%d')= DATE_FORMAT(NOW(),'%Y%m%d') and (AUDIT_STATE = :shtg or  AUDIT_STATE IS NULL) ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from GG_FILES where YEARWEEK(CREATE_TIME)=YEARWEEK(NOW()) and (AUDIT_STATE = :shtg or  AUDIT_STATE IS NULL) ");
		builder.append(" 	union all ");
		builder.append(" 	select count(id) num from GG_FILES where DATE_FORMAT(CREATE_TIME,'%Y%m')= DATE_FORMAT(NOW(),'%Y%m') and (AUDIT_STATE = :shtg or  AUDIT_STATE IS NULL) ");
		builder.append(" ) t ");
		return baseDao.queryForString(builder.toString(),new SqlParameter("shtg",Constants.FILE_STATE_SHTG));
	}
	
	/**
	 * 文档处理数量
	 * @param cusId
	 * @return
	 */
	private String getFileDealNum(){
		StringBuilder builder = new StringBuilder();
		builder.append("  ");
		builder.append(" select cast(GROUP_CONCAT(t.num SEPARATOR '/') as char) from ( ");
		builder.append(" 	( ");
		builder.append(" 	select count(ID) num from GG_FILES  ");
		builder.append(" where DATE_FORMAT(CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m') and AUDIT_STATE = :wsh  ");
		builder.append(" 	) ");
		builder.append(" 	union all ");
		builder.append(" 	( ");
		builder.append(" 		select count(ID) num from GG_FILES  where DATE_FORMAT(CREATE_TIME,'%Y%m') = DATE_FORMAT(now(),'%Y%m')  ");
		builder.append(" 		and (AUDIT_STATE = :shtg or AUDIT_STATE = :shbtg or  AUDIT_STATE IS NULL) ");
		builder.append(" 	) ");
		builder.append(" ) t ");
		Map param = new HashMap();
		param.put("wsh", Constants.FILE_STATE_DSH);
		param.put("shtg", Constants.FILE_STATE_SHTG);
		param.put("shbtg", Constants.FILE_STATE_SHBTG);
		return baseDao.queryForString(builder.toString(),param);
	}
	
}