package cn.ksource.web.facade.uc.order;

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

import javax.servlet.http.HttpServletRequest;

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

import cn.ksource.core.dao.BaseDao;
import cn.ksource.core.dao.SqlParameter;
import cn.ksource.core.page.PageInfo;
import cn.ksource.core.util.ConvertUtil;
import cn.ksource.core.util.DateUtil;
import cn.ksource.core.util.StringUtil;
import cn.ksource.web.Constants;
import cn.ksource.web.service.DataDictionaryService;
import cn.ksource.web.service.file.FileService;
import cn.ksource.web.service.order.OrderService;

@Service("ucQuestionFacade")
public class UcQuestionFacadeImpl implements UcQuestionFacade {
	
	@Autowired
	private BaseDao baseDao;
	
	@Autowired
	private FileService fileService;
	
	@Autowired
	private OrderService orderService;
	
	@Autowired
	private DataDictionaryService dataDictionaryService;
	
	@Override
	public int queryQuestionOrderCount(Map<String,String> params) {
		String cusId = params.get("cusId");
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT COUNT(WB.ID) FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB LEFT JOIN WORKFLOW_NODE C ON  C.FLOWID = WB.ID AND WB.CURRENT_NODE_ID = C.ID AND C.FLOWSTATE <> 3  ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID  AND WB.BUSINESSTYPE = :businessType ");
		
		sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
		
		params.put("cusId", cusId);
			
		sqlpart = getQuestionSql(sqlpart,params);
		
		sql.append(sqlpart);
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		return baseDao.queryForInteger(sql.toString(),params);
	}
	
	@Override
	public PageInfo queryQuestionOrderList(PageInfo pageInfo,Map<String,String> params) {
		String cusId = params.get("cusId");
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE,C.FLOWSTATE , ");
		sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.PROJECT_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ");
		sql.append("FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB LEFT JOIN WORKFLOW_NODE C ON  C.FLOWID = WB.ID AND WB.CURRENT_NODE_ID = C.ID AND C.FLOWSTATE <> 3 ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID  AND WB.BUSINESSTYPE = :businessType ");
		params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
		
		params.put("cusId", cusId);
		sqlpart = getQuestionSql(sqlpart,params);
		
		sql.append(sqlpart);
		sql.append( " ORDER BY WB.CREATETIME DESC ");
		
		return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
	}
	
	public StringBuilder getQuestionSql(StringBuilder sqlpart,Map<String,String> params){
		String orderName = params.get("orderName");
		if(StringUtil.notEmpty(orderName)) {
			sqlpart.append(" AND B.NAME LIKE :orderName ");
			params.put("orderName", "%"+orderName+"%");
		}
		
		String orderCode = params.get("orderCode");
		if(StringUtil.notEmpty(orderCode)) {
			sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
			params.put("orderCode", "%"+orderCode+"%");
		}
		
		
		String contract = params.get("contract");
		if(StringUtil.notEmpty(contract)) {
			sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
			params.put("contract", "%"+contract+"%");
		}
		
		String states = params.get("state");
		if(StringUtil.notEmpty(states)) {
			sqlpart.append(" AND WB.WFSTATE = :state ");
		}
		
		String pri = params.get("pri");
		if(StringUtil.notEmpty(pri)) {
			String[] pris = pri.split(",");
			if(pris.length==1) {
				sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
				params.put("priority_id", pris[0]);
			} else {
				sqlpart.append(" AND B.PRIORITY_ID IN (");
				for(int i=0; i<pris.length; i++) {
					sqlpart.append(" :pri"+i);
					sqlpart.append(",");
					params.put("pri"+i, pris[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		
		String dg = params.get("dg");
		if(StringUtil.notEmpty(dg)) {
			String[] dgs = dg.split(",");
			if(dgs.length==1) {
				sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
				params.put("influence_id", dgs[0]);
			} else {
				sqlpart.append(" AND B.INFLUENCE_ID IN (");
				for(int i=0; i<dgs.length; i++) {
					sqlpart.append(" :dg"+i);
					sqlpart.append(",");
					params.put("dg"+i, dgs[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String source = params.get("source");
		if(StringUtil.notEmpty(source)) {
			String[] sources = source.split(",");
			if(sources.length==1) {
				sqlpart.append(" AND B.SOURCE_ID = :type_id");
				params.put("type_id", sources[0]);
			} else {
				sqlpart.append(" AND B.SOURCE_ID IN (");
				for(int i=0; i<sources.length; i++) {
					sqlpart.append(" :type"+i);
					sqlpart.append(",");
					params.put("type"+i, sources[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		String sl = params.get("sl");
		if(StringUtil.notEmpty(sl)) {
			String[] sls = sl.split(",");
			if(sls.length==1) {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
				params.put("third_category_id", sls[0]);
			} else {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
				for(int i=0; i<sls.length; i++) {
					sqlpart.append(" :third_category_id"+i);
					sqlpart.append(",");
					params.put("third_category_id"+i, sls[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		String status = params.get("status");
		if(StringUtil.notEmpty(status)) {
			String[] state = status.split(",");
			if("ywc".equals(state[0])){
				sqlpart.append( " AND (B.STATE = :state  OR B.STATE = :state1 )");
				params.put("state", Constants.SC_WORKFLOW_QUESTION_STATE_YWC);
				params.put("state1", Constants.SC_WORKFLOW_QUESTION_STATE_YPJ);
			}else if("jxz".equals(state[0])){
				sqlpart.append( " AND (B.STATE != :state  AND B.STATE != :state1 AND B.STATE != :state2 )  ");
				params.put("state", Constants.SC_WORKFLOW_QUESTION_STATE_YWC);
				params.put("state1", Constants.SC_WORKFLOW_QUESTION_STATE_YPJ);
				params.put("state2", Constants.SC_WORKFLOW_QUESTION_STATE_YGB);
			}else if(!"all".equals(state[0])){
				sqlpart.append( " AND B.STATE = :state  ");
				params.put("state", state[0]);
				if(state.length == 2){
					sqlpart.append( "  AND C.FLOWSTATE = :flowstate ");
					params.put("flowstate", state[1]);
				}
			}
		}
		return sqlpart;
	}
	
	@Override
	public Map queryQuestionCount(String cusId) {
		//初始化变量
		
		//问题受理响应中
		int wtsldxy = 0;
		//问题受理处理中
		int wtslclz = 0;
		
		//问题审核待响应
		int wtshdxy = 0;
		//问题审核处理中
		int wtshclz = 0;
		
		//问题诊断待响应
		int wtzddxy = 0;
		//问题诊断处理中
		int wtzdclz = 0;
		
		
		//方案审批待响应
		int faspdxy = 0;
		//方案审批处理中
		int faspclz = 0;
		
		//方案实施待响应
		int fassdxy = 0;
		//方案实施处理中
		int fassclz = 0;
		
		//问题回顾待响应
		int wthgdxy = 0;
		//问题回顾处理中
		int wthgclz = 0;
		
		//已完成
		int ywc = 0;
		//进行中
		int jxz = 0;
		
		String selectSql = new String();
		String sql = new String();
		
		
		Map paramMap = new HashMap();
		selectSql = "SELECT COUNT(*) AS NUM,A.STATE,C.FLOWSTATE FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B LEFT JOIN WORKFLOW_NODE C ON B.ID = C.FLOWID AND B.CURRENT_NODE_ID = C.ID AND C.FLOWSTATE <> :flowstate WHERE  A.CUSTOMER_ID = :cusId AND A.ID = B.BUSINESS_ID  GROUP BY A.STATE,C.FLOWSTATE "; 
		sql = "SELECT COUNT(ID) FROM SC_WORKFLOW_QUESTION WHERE  STATE != :yjj AND STATE !=:gb AND STATE != :ypj AND CUSTOMER_ID = :cusId";
		paramMap.put("cusId", cusId);
		paramMap.put("flowstate", 3);
		
		
		List<Map> list = baseDao.queryForList(selectSql,paramMap);
		if(null!=list && list.size()>0) {
			for(Map map : list) {
				String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
				int flowstate = 0;
				if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(map.get("FLOWSTATE")))){
					flowstate = ConvertUtil.obj2Integer(map.get("FLOWSTATE"));
				}
				int num = ConvertUtil.obj2Int(map.get("NUM"));
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
					if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
						wtsldxy += num;
					}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
						wtslclz += num;
					}
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
					if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
						wtshdxy += num;
					}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
						wtshclz += num;
					}
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
					if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
						wtzddxy += num;
					}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
						wtzdclz += num;
					}
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
					if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
						faspdxy += num;
					}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
						faspclz += num;
					}
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
					if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
						fassdxy += num;
					}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
						fassclz += num;
					}
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
					if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_UNDISPOSE){
						wthgdxy += num;
					}else if(flowstate == Constants.WORKFLOW_NODE_FLOWSTATE_DOING){
						wthgclz += num;
					}
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_YWC)) {
					ywc += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_YPJ)) {
					ywc += num;
					continue;
				}
				
			}
		}
		
		Map map = new HashMap();
		map.put("wtsldxy", wtsldxy);
		map.put("wtslclz", wtslclz);
		
		map.put("wtshdxy", wtshdxy);
		map.put("wtshclz", wtshclz);
		
		map.put("wtzddxy", wtzddxy);
		map.put("wtzdclz", wtzdclz);
		
		map.put("faspdxy", faspdxy);
		map.put("faspclz", faspclz);
		
		map.put("fassdxy", fassdxy);
		map.put("fassclz", fassclz);
		
		map.put("wthgdxy", wthgdxy);
		map.put("wthgclz", wthgclz);
		
		//查询该加盟商未完成的事件
		
		paramMap.put("yjj", Constants.SC_WORKFLOW_QUESTION_STATE_YWC);
		paramMap.put("gb", Constants.SC_WORKFLOW_QUESTION_STATE_YGB);
		paramMap.put("ypj", Constants.SC_WORKFLOW_QUESTION_STATE_YPJ);
		
		
		jxz = baseDao.queryForInteger(sql,paramMap);
		
		map.put("ywc", ywc);
		map.put("jxz", jxz);
		
		return map;
	}
	
	
	@Override
	public Map queryQuestionBaseMsg( String orderId) {
		String selectSql = "SELECT A.*,N.ANSWER_TIME FROM SC_WORKFLOW_QUESTION A LEFT JOIN  WORKFLOW_BASE B ON A.ID = B.BUSINESS_ID LEFT JOIN WORKFLOW_NODE N ON B.CURRENT_NODE_ID = N.ID  WHERE A.ID = :orderId  ";
		Map baseMap =  baseDao.queryForMap(selectSql,new SqlParameter("orderId",orderId));
		return baseMap;
	}
	
	@Override
	public Map questionInfo(HttpServletRequest request) {
		String questionId=request.getParameter("orderId");
		String flowId=request.getParameter("flowId");
		Map param =new HashMap();
		param.put("questionId", questionId);
		param.put("flowId", flowId);
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT c.*,u.SJHM FROM SC_WORKFLOW_QUESTION c,gg_user u WHERE c.CREATE_USER_ID=u.id AND c.ID=:questionId ");
		Map questionMap=baseDao.queryForMap(sql.toString(), param);
		questionMap.put("STATE", Constants.getmapSC_WORKFLOW_QUESTION_STATE_Label(questionMap.get("STATE").toString()));
		Map map = new HashMap();
		map.put("questionMap", questionMap);
		//关联工单
		List<Map> orderList=orderService.queryLinkOrders(flowId);
		if(orderList!=null && orderList.size()>0){
			for(Map orderMap:orderList){
				orderMap.put("WFSTATE", Constants.getWORKFLOW_BASE_WFSTATE_Label(ConvertUtil.obj2StrBlank(orderMap.get("WFSTATE"))));
				orderMap.put("BUSINESSTYPE", Constants.mapWORKFLOW_BUSINESS_TYPE_Label(orderMap.get("BUSINESSTYPE").toString()));
			}
		}
		map.put("orderList", orderList);
		//关联设备
		sql.setLength(0);
		sql.append("SELECT * FROM cmdb_ci_ref_order r,cmdb_ci_base b WHERE r.CI_ID=b.ID");
		sql.append(" AND r.FLOW_ID=:flowId");
		List ciList=baseDao.queryForList(sql.toString(),param);
		map.put("ciList", ciList);
		//关联文档
		List<Map> fileList=fileService.getFileList(flowId);
		if(fileList!=null && fileList.size()>0){
			for(Map fileMap:fileList){
				fileMap.put("FILE_SIZE", ConvertUtil.byte2KM(Double.parseDouble(fileMap.get("FILE_SIZE").toString()), 2));
			}
		}
		map.put("fileList", fileList);
		return map;
	}

	@Override
	public Map getLastMonthQuestionEffect(HttpServletRequest request) {
		Map chartMap = new HashMap();
		String cusId = request.getParameter("cusId");
		String userId = request.getParameter("userId");
		Long endDay = DateUtil.getCurrentDate8();
		Long startDay = DateUtil.getDateAdd(endDay,-30,8);
		
		SqlParameter param = new SqlParameter();
		param.addValue("startTime", startDay+"000000")
			.addValue("endTime", endDay + "600000")
			.addValue("userId", userId)
			.addValue("cusId", cusId);
		
		StringBuilder builder = new StringBuilder();
		if(StringUtil.isBlank(userId)){
			builder.append(" SELECT count(ID) NUM,INFLUENCE_ID FROM SC_WORKFLOW_QUESTION WHERE ");
			builder.append(" CUSTOMER_ID = :cusId AND CREATE_TIME >= :startTime AND CREATE_TIME <= :endTime ");
			builder.append(" GROUP BY INFLUENCE_ID ");
		}else{
			builder.append(" SELECT count(q.ID) NUM,q.INFLUENCE_ID FROM SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b  ");
			builder.append(" where b.ID = q.FLOW_ID and  EXISTS ( ");
			builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
			builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime  group by q.INFLUENCE_ID ");
		}
		
		List<Map> dataList = baseDao.queryForList(builder.toString(),param);
		if(dataList!=null&&dataList.size()>0){
			Map dataMap = new HashMap();
			Map tempMap = new HashMap();
			List serieslist = new ArrayList();
			for(Map data : dataList){
				dataMap.put(data.get("INFLUENCE_ID").toString(), data);
			}
			List<Map> catelist = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.EVENT_EFFECT_DG);
			if(catelist!=null&&catelist.size()>0){
				for(Map cate : catelist){
					List tempList = new ArrayList();
					tempList.add(cate.get("DATAVALUE").toString());
					if(dataMap.get(cate.get("DATAKEY").toString())!=null){
						tempList.add(((Map)dataMap.get(cate.get("DATAKEY").toString())).get("NUM"));
					}else{
						tempList.add(0);
					}
					serieslist.add(tempList);
				}
			}
			chartMap.put("seriesData", serieslist);
		}
		return chartMap;
	}

	@Override
	public Map getLastMonthQuestionServer(HttpServletRequest request) {
		Map chartMap = new HashMap();
		Long endDay = DateUtil.getCurrentDate8();
		Long startDay = DateUtil.getDateAdd(endDay,-30,8);
		
		String cusId = request.getParameter("cusId");
		String userId = request.getParameter("userId");
		StringBuilder builder = new StringBuilder();
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startTime", startDay+"000000")
			.addValue("endTime", endDay + "600000")
			.addValue("userId", userId);
		if(StringUtil.isBlank(userId)){
			builder.append(" select count(ID) NUM,THIRD_CATEGORY_ID SERVER_ID  from SC_WORKFLOW_QUESTION  where CUSTOMER_ID = :cusId ");
			builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by SERVER_ID ");
		}else{
			builder.append(" select count(q.ID) NUM,q.THIRD_CATEGORY_ID SERVER_ID  from SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b ");
			builder.append(" where b.ID = q.FLOW_ID and  EXISTS ( ");
			builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
			builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime  group by SERVER_ID ");
		}
		
		
		
		
		List<Map> dataList = baseDao.queryForList(builder.toString(),param);
		if(dataList!=null&&dataList.size()>0){
			builder.setLength(0);
			builder.append(" SELECT C.ID SERVER_ID,C.CATEGORY_NAME SERVER_NAME FROM SC_SERVCE_CATEGORY C,SC_SERVCE_CATEGORY_CUSTOMER P   ");
			builder.append(" WHERE C.ID = P.CATEGORY_ID and C.LEVEL = 3 and C.STATE=1  ");
			builder.append(" AND P.STATE = 1 AND P.CUSTOMER_ID = :cusId ORDER BY SERIAL ASC ");
			List<Map> categoriesList = baseDao.queryForList(builder.toString(),param);
			List<String> categories = new ArrayList<String>();
			Map dataMap = new HashMap();
			Map tempMap = new HashMap();
			List<Map> series = new ArrayList<Map>();
			tempMap.put("name", "数量");
			List tempList = new ArrayList();
			for(Map data:dataList){
				dataMap.put(data.get("SERVER_ID").toString(), data);
			}
			for(Map cate:categoriesList){
				categories.add(cate.get("SERVER_NAME").toString());
				if(dataMap.get(cate.get("SERVER_ID").toString())!=null){
					tempList.add(((Map)dataMap.get(cate.get("SERVER_ID").toString())).get("NUM"));
				}else{
					tempList.add(0);
				}
			}
			tempMap.put("data", tempList);
			series.add(tempMap);
			chartMap.put("categories",categories);
			chartMap.put("series",series);
		}
		return chartMap;
	}

	@Override
	public Map getLastMonthQuestionLv(HttpServletRequest request) {
		Map chartMap = new HashMap();
		String cusId = request.getParameter("cusId");
		String userId = request.getParameter("userId");
		Long endDay = DateUtil.getCurrentDate8();
		Long startDay = DateUtil.getDateAdd(endDay,-30,8);
		
		SqlParameter param = new SqlParameter();
		param.addValue("startTime", startDay+"000000")
			.addValue("endTime", endDay + "600000")
			.addValue("userId", userId)
			.addValue("cusId", cusId);
		
		StringBuilder builder = new StringBuilder();
		if(StringUtil.isBlank(userId)){
			builder.append(" SELECT count(ID) NUM,PRI_LEVEL_ID FROM SC_WORKFLOW_QUESTION WHERE ");
			builder.append(" CUSTOMER_ID = :cusId AND CREATE_TIME >= :startTime AND CREATE_TIME <= :endTime ");
			builder.append(" GROUP BY PRI_LEVEL_ID ");
		}else{
			builder.append(" SELECT count(q.ID) NUM,q.PRI_LEVEL_ID FROM SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b ");
			builder.append(" where b.ID = q.FLOW_ID and  EXISTS ( ");
			builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
			builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime  group by q.PRI_LEVEL_ID ");
		}

		List<Map> dataList = baseDao.queryForList(builder.toString(),param);
		if(dataList!=null&&dataList.size()>0){
			Map dataMap = new HashMap();
			Map tempMap = new HashMap();
			List serieslist = new ArrayList();
			
			for(Map data : dataList){
				dataMap.put(data.get("PRI_LEVEL_ID").toString(), data);
			}
			
			builder.setLength(0);
			builder.append(" select ID LEVEL_ID,LEVEL_NAME from SC_SLA order by SERIAL ");
			List<Map> catelist = baseDao.queryForList(builder.toString());
			for(Map cate : catelist){
				List tempList = new ArrayList();
				tempList.add(cate.get("LEVEL_NAME").toString());
				if(dataMap.get(cate.get("LEVEL_ID").toString())!=null){
					tempList.add(((Map)dataMap.get(cate.get("LEVEL_ID").toString())).get("NUM"));
				}else{
					tempList.add(0);
				}
				serieslist.add(tempList);
			}
			chartMap.put("seriesData", serieslist);
		}
		return chartMap;
	}

	@Override
	public Map getLastMonthQuestionPri(HttpServletRequest request) {
		Map chartMap = new HashMap();
		String cusId = request.getParameter("cusId");
		String userId = request.getParameter("userId");
		Long endDay = DateUtil.getCurrentDate8();
		Long startDay = DateUtil.getDateAdd(endDay,-30,8);
		
		SqlParameter param = new SqlParameter();
		param.addValue("startTime", startDay+"000000")
			.addValue("endTime", endDay + "600000")
			.addValue("userId", userId)
			.addValue("cusId", cusId);
		
		StringBuilder builder = new StringBuilder();
		if(StringUtil.isBlank(userId)){
			builder.append(" SELECT count(ID) NUM,PRIORITY_ID FROM SC_WORKFLOW_QUESTION WHERE ");
			builder.append(" CUSTOMER_ID = :cusId AND CREATE_TIME >= :startTime AND CREATE_TIME <= :endTime ");
			builder.append(" GROUP BY PRIORITY_ID ");
		}else{
			builder.append(" SELECT count(q.ID) NUM,q.PRIORITY_ID FROM SC_WORKFLOW_QUESTION q,WORKFLOW_BASE b  ");
			builder.append(" where b.ID = q.FLOW_ID and  EXISTS ( ");
			builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
			builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime  group by q.PRIORITY_ID ");
		}
		
		List<Map> dataList = baseDao.queryForList(builder.toString(),param);
		if(dataList!=null&&dataList.size()>0){
			Map dataMap = new HashMap();
			Map tempMap = new HashMap();
			List serieslist = new ArrayList();
			for(Map data : dataList){
				dataMap.put(data.get("PRIORITY_ID").toString(), data);
			}
			List<Map> catelist = dataDictionaryService.getDataDictionaryByCategoryKey(Constants.EVENT_PRI);
			for(Map cate : catelist){
				List tempList = new ArrayList();
				tempList.add(cate.get("DATAVALUE").toString());
				if(dataMap.get(cate.get("DATAKEY").toString())!=null){
					tempList.add(((Map)dataMap.get(cate.get("DATAKEY").toString())).get("NUM"));
				}else{
					tempList.add(0);
				}
				serieslist.add(tempList);
			}
			chartMap.put("seriesData", serieslist);
		}
		return chartMap;
	}

	@Override
	public Map getLastMonthQuestionCount(HttpServletRequest request) {
		Map chartMap = new HashMap();
		Long endDay = DateUtil.getCurrentDate8();
		Long startDay = DateUtil.getDateAdd(endDay,-30,8);
		
		String cusId = request.getParameter("cusId");
		String userId = request.getParameter("userId");
		StringBuilder builder = new StringBuilder();
		SqlParameter param = new SqlParameter();
		param.addValue("cusId", cusId)
			.addValue("startTime", startDay+"000000")
			.addValue("userId", userId)
			.addValue("endTime", endDay + "600000");
		if(StringUtil.isBlank(userId)){
			builder.append(" select count(ID) NUM,DATE_FORMAT(CREATE_TIME,'%Y%m%d') CREATEDAY  from SC_WORKFLOW_QUESTION  where CUSTOMER_ID = :cusId ");
			builder.append(" and CREATE_TIME>=:startTime and CREATE_TIME<=:endTime group by CREATEDAY ");
		}else{
			builder.append(" select count(q.ID) NUM,DATE_FORMAT(q.CREATE_TIME,'%Y%m%d') CREATEDAY from workflow_base b ,SC_WORKFLOW_QUESTION q ");
			builder.append(" where b.ID = q.FLOW_ID and  EXISTS ( ");
			builder.append(" select FLOWID from workflow_node n where b.ID = n.FLOWID and n.CURRENT_DEALER_ID = :userId ");
			builder.append(" ) and q.CUSTOMER_ID = :cusId and q.CREATE_TIME>=:startTime and q.CREATE_TIME<=:endTime  group by CREATEDAY ");
		}
		
		List<Map> dataList = baseDao.queryForList(builder.toString(),param);
		if(dataList!=null&&dataList.size()>0){
			
			List<String> categories = DateUtil.getDates(startDay ,endDay);
			categories.add(String.valueOf(DateUtil.getCurrentDate8()));
			List<String> categories_format = new ArrayList<String>();
			Map dataMap = new HashMap();
			Map tempMap = new HashMap();
			List<Map> series = new ArrayList<Map>();
			tempMap.put("name", "问题数量");
			List tempList = new ArrayList();
			for(Map data:dataList){
				dataMap.put(data.get("CREATEDAY").toString(), data);
			}
			for(String day:categories){
				categories_format.add(DateUtil.format("yyyy-MM-dd",day));
				if(dataMap.get(day)!=null){
					tempList.add(((Map)dataMap.get(day)).get("NUM"));
				}else{
					tempList.add(0);
				}
			}
			tempMap.put("data", tempList);
			series.add(tempMap);
			chartMap.put("categories",categories_format);
			chartMap.put("step",Math.ceil(categories.size()/9));
			chartMap.put("series",series);
		}
		return chartMap;
	}
	
	@Override
	public Map queryQuestionpoolCount(String cusId) {
		//初始化变量
		int ywc = 0;
		//问题受理
		int wtsl = 0;
		
		//问题审核
		int wtsh = 0;
		
		//问题诊断
		int wtzd = 0;
		
		//方案审批
		int fasp = 0;
		
		//方案实施
		int fass = 0;
		
		//问题回顾
		int wthg = 0;
		StringBuffer sql = new StringBuffer();
		StringBuffer sqlall = new StringBuffer();
		Map paramMap = new HashMap();
		paramMap.put("wfstate", Constants.WORKFLOW_BASE_WFSTATE_DELETE);
		paramMap.put("cusId", cusId);
		//查询该人员所属的项目
		sql.append("SELECT COUNT(*) AS NUM,E.STATE FROM SC_WORKFLOW_QUESTION E,WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE B.STATE = 1  ");
		sqlall.append("SELECT COUNT(F.ID) FROM (SELECT E.* FROM SC_WORKFLOW_QUESTION E,WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE B.STATE = 1  ");
		sql.append(") C WHERE E.ID = D.BUSINESS_ID AND E.CUSTOMER_ID = :cusId AND D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
		sql.append(" GROUP BY E.STATE ");
		sqlall.append(") C WHERE E.ID = D.BUSINESS_ID AND E.CUSTOMER_ID = :cusId AND D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
		sqlall.append(" GROUP BY E.ID) F ");
		List<Map> list = baseDao.queryForList(sql.toString(),paramMap);
		if(null!=list && list.size()>0) {
			for(Map map : list) {
				String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
				int num = ConvertUtil.obj2Int(map.get("NUM"));
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
					wtsl += num;
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
					wtsh += num;
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
					wtzd += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
					fasp += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
					fass += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
					wthg += num;
					continue;
				}
				
			}
		}
		int all = baseDao.queryForInteger(sqlall.toString(), paramMap);
		Map map = new HashMap();
		map.put("wtsl", wtsl);
		
		map.put("wtsh", wtsh);
		
		map.put("wtzd", wtzd);
		
		map.put("fasp", fasp);
		
		map.put("fass", fass);
		
		map.put("wthg", wthg);
		
		map.put("all", all);
		return map;
	}
	
	@Override
	public int queryQuestionJxzCount(Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB   ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND WB.BUSINESSTYPE = :businessType  AND WB.WFSTATE = 1 ");
		
		sqlpart = getJxzSql(sqlpart,params);
		
		sql.append(sqlpart);
		sql.append( " GROUP BY B.ID  ) D");
		params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		return baseDao.queryForInteger(sql.toString(),params);
	}
	
	@Override
	public PageInfo queryQuestionJxzList(PageInfo pageInfo,Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE , ");
		sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.PROJECT_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ");
		sql.append("FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID  AND  C.FLOWID = WB.ID AND WB.BUSINESSTYPE = :businessType AND WB.WFSTATE = 1 ");
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		sqlpart = getJxzSql(sqlpart,params);
		sql.append(sqlpart);
		sql.append( " GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
		
		return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
	}
	
	public StringBuilder getJxzSql(StringBuilder sqlpart,Map<String,String> params){
		
		String cusId = params.get("cusId");
		if(StringUtil.notEmpty(cusId)) {
			sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
		}
		String subCustomerId = params.get("subCustomerId");
		if(StringUtil.notEmpty(subCustomerId)) {
			sqlpart.append(" AND B.SUB_CUSTOMER_ID = :subCustomerId ");
			params.put("subCustomerId", subCustomerId);
		}
		
		String orderName = params.get("orderName");
		if(StringUtil.notEmpty(orderName)) {
			sqlpart.append(" AND B.NAME LIKE :orderName ");
			params.put("orderName", "%"+orderName+"%");
		}
		
		String orderCode = params.get("orderCode");
		if(StringUtil.notEmpty(orderCode)) {
			sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
			params.put("orderCode", "%"+orderCode+"%");
		}
		
		
		String contract = params.get("contract");
		if(StringUtil.notEmpty(contract)) {
			sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
			params.put("contract", "%"+contract+"%");
		}
		
		
		String pri = params.get("pri");
		if(StringUtil.notEmpty(pri)) {
			String[] pris = pri.split(",");
			if(pris.length==1) {
				sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
				params.put("priority_id", pris[0]);
			} else {
				sqlpart.append(" AND B.PRIORITY_ID IN (");
				for(int i=0; i<pris.length; i++) {
					sqlpart.append(" :pri"+i);
					sqlpart.append(",");
					params.put("pri"+i, pris[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		
		String dg = params.get("dg");
		if(StringUtil.notEmpty(dg)) {
			String[] dgs = dg.split(",");
			if(dgs.length==1) {
				sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
				params.put("influence_id", dgs[0]);
			} else {
				sqlpart.append(" AND B.INFLUENCE_ID IN (");
				for(int i=0; i<dgs.length; i++) {
					sqlpart.append(" :dg"+i);
					sqlpart.append(",");
					params.put("dg"+i, dgs[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String source = params.get("source");
		if(StringUtil.notEmpty(source)) {
			String[] sources = source.split(",");
			if(sources.length==1) {
				sqlpart.append(" AND B.SOURCE_ID = :type_id");
				params.put("type_id", sources[0]);
			} else {
				sqlpart.append(" AND B.SOURCE_ID IN (");
				for(int i=0; i<sources.length; i++) {
					sqlpart.append(" :type"+i);
					sqlpart.append(",");
					params.put("type"+i, sources[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String sl = params.get("sl");
		if(StringUtil.notEmpty(sl)) {
			String[] sls = sl.split(",");
			if(sls.length==1) {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
				params.put("third_category_id", sls[0]);
			} else {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
				for(int i=0; i<sls.length; i++) {
					sqlpart.append(" :third_category_id"+i);
					sqlpart.append(",");
					params.put("third_category_id"+i, sls[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		String status = params.get("status");
		if(StringUtil.notEmpty(status)) {
			sqlpart.append( " AND B.STATE = :state  ");
			params.put("state", status);
		}
		return sqlpart;
	}

	@Override
	public int queryQuestionEndCount(Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB   ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID  AND WB.BUSINESSTYPE = :businessType  AND WB.WFSTATE = :type ");
		
		sqlpart = getEndSql(sqlpart,params);
		sql.append(sqlpart);
		sql.append( " GROUP BY B.ID  ) D");
		params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		return baseDao.queryForInteger(sql.toString(),params);
	}

	@Override
	public PageInfo queryQuestionEndList(PageInfo pageInfo,Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT B.RESOLVE_TYPE_NAME,B.CREATE_TIME,B.CREATE_USER_NAME,WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE ,WB.ENDTIME , ");
		sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.PROJECT_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ,");
		sql.append(" D.HANG_UP_USERNAME,D.HANG_UP_TIME,D.HANG_UP_REASON ,WB.SCORE ");
		sql.append("FROM (SELECT F.*,E.SCORE FROM  WORKFLOW_BASE F LEFT JOIN ORDER_SATIS_INFO E ON F.ID = E.BUSINESS_ID WHERE  F.BUSINESSTYPE = :businessType ) WB, WORKFLOW_NODE C ,SC_WORKFLOW_QUESTION B LEFT JOIN HANG_UP_INFO D ON B.ID = D.BUS_ID ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID  AND  C.FLOWID = WB.ID  AND WB.WFSTATE = :type ");
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		sqlpart = getEndSql(sqlpart,params);
		
		sql.append(sqlpart);
		sql.append( " GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
		
		return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
	}
	
	public StringBuilder getEndSql(StringBuilder sqlpart,Map<String,String> params){
		String customerId = params.get("cusId");
		if(StringUtil.notEmpty(customerId)) {
			sqlpart.append(" AND B.CUSTOMER_ID = :customerId ");
			params.put("customerId", customerId);
		}
		String subCustomerId = params.get("subCustomerId");
		if(StringUtil.notEmpty(subCustomerId)) {
			sqlpart.append(" AND B.SUB_CUSTOMER_ID = :subCustomerId ");
			params.put("subCustomerId", subCustomerId);
		}
		
		String orderName = params.get("orderName");
		if(StringUtil.notEmpty(orderName)) {
			sqlpart.append(" AND B.NAME LIKE :orderName ");
			params.put("orderName", "%"+orderName+"%");
		}
		
		String orderCode = params.get("orderCode");
		if(StringUtil.notEmpty(orderCode)) {
			sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
			params.put("orderCode", "%"+orderCode+"%");
		}
		
		
		String contract = params.get("contract");
		if(StringUtil.notEmpty(contract)) {
			sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
			params.put("contract", "%"+contract+"%");
		}
		
		
		String pri = params.get("pri");
		if(StringUtil.notEmpty(pri)) {
			String[] pris = pri.split(",");
			if(pris.length==1) {
				sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
				params.put("priority_id", pris[0]);
			} else {
				sqlpart.append(" AND B.PRIORITY_ID IN (");
				for(int i=0; i<pris.length; i++) {
					sqlpart.append(" :pri"+i);
					sqlpart.append(",");
					params.put("pri"+i, pris[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		
		String dg = params.get("dg");
		if(StringUtil.notEmpty(dg)) {
			String[] dgs = dg.split(",");
			if(dgs.length==1) {
				sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
				params.put("influence_id", dgs[0]);
			} else {
				sqlpart.append(" AND B.INFLUENCE_ID IN (");
				for(int i=0; i<dgs.length; i++) {
					sqlpart.append(" :dg"+i);
					sqlpart.append(",");
					params.put("dg"+i, dgs[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String source = params.get("source");
		if(StringUtil.notEmpty(source)) {
			String[] sources = source.split(",");
			if(sources.length==1) {
				sqlpart.append(" AND B.SOURCE_ID = :type_id");
				params.put("type_id", sources[0]);
			} else {
				sqlpart.append(" AND B.SOURCE_ID IN (");
				for(int i=0; i<sources.length; i++) {
					sqlpart.append(" :type"+i);
					sqlpart.append(",");
					params.put("type"+i, sources[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String sl = params.get("sl");
		if(StringUtil.notEmpty(sl)) {
			String[] sls = sl.split(",");
			if(sls.length==1) {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
				params.put("third_category_id", sls[0]);
			} else {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
				for(int i=0; i<sls.length; i++) {
					sqlpart.append(" :third_category_id"+i);
					sqlpart.append(",");
					params.put("third_category_id"+i, sls[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		String status = params.get("status");
		if(StringUtil.notEmpty(status)) {
			sqlpart.append( " AND B.STATE = :state  ");
			params.put("state", status);
		}
		return sqlpart;
	}
	
	@Override
	public int questionpoolCount(Map<String,String> params) {
		/**
		 * 查询我的工单池规则
		 * 如果是一二三线或者驻场工程师桌面,则直接查询工单表中 所属分组是当前工程师所在组的
		 * 如果是项目服务台，则直接查询工单表中所属分组是当前工程师所在组的 （同上）
		 * 如果是客户服务台，查询我的工单池  则查询工单表中分组是 （查询该客户下所有的项目的服务台分组）
		 * 如果是总服务台，则查询所有客户下（加盟商下所有项目的服务台分组）
		 */
		
		
		StringBuffer sql = new StringBuffer();
		params.put("wfstate",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_WFSTATE_DELETE));
			
		sql.append("SELECT COUNT(D.ID) FROM WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE STATE = 1");
		sql.append(") C WHERE  D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
		StringBuilder builder = new StringBuilder(sql);
		
		builder = getPoolSql(builder,params);
		
		builder.append(" AND D.BUSINESSTYPE = :businessType");
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		
		int count = baseDao.queryForInteger(builder.toString(),params);
		
		return 0;
	}

	@Override
	public PageInfo questionpoolData(PageInfo pageInfo,Map<String,String> params) {
		
		/**
		 * 查询我的工单池规则
		 * 如果是项目服务台，则直接查询工单表 
		 * 如果是客户服务台，查询我的工单池  则查询工单表中分组是 （查询该客户下所有的项目的分组）
		 * 如果是总服务台，则查询所有客户下（加盟商下所有项目的分组）
		 */
		
		
		StringBuffer sql = new StringBuffer();
		params.put("wfstate",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_WFSTATE_DELETE));
		//查询该人员所属的项目
		sql.append("SELECT D.* FROM WORKFLOW_BASE D,(SELECT B.ID,B.ROLENAME FROM AC_ROLE B WHERE STATE = 1 ");
		
		sql.append(") C WHERE  D.CURRENT_DEAL_ROLEIDA = C.ID AND D.WFSTATE != :wfstate AND (D.CURRENT_DEALER_ID IS NULL OR D.CURRENT_DEALER_ID = '' )");
		StringBuilder builder = new StringBuilder(sql);
		
		builder = getPoolSql(builder,params);
		
		builder.append(" AND D.BUSINESSTYPE = :businessType");
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		
		PageInfo result = baseDao.queryforSplitPageInfo(pageInfo, builder.toString(), params);
		
		return result;
	}
	
	public StringBuilder getPoolSql(StringBuilder builder,Map<String,String> params){
		
		String status = params.get("status");
		if(StringUtil.notEmpty(status)) {
			builder.append(" AND D.WFSTATE = :status ");
		}
		String cusId = params.get("cusId");
		if(StringUtil.notEmpty(cusId)) {
			builder.append(" AND D.CUSTOMER_ID = :cusId ");
		}
		String subCustomerId = params.get("subCustomerId");
		if(StringUtil.notEmpty(subCustomerId)) {
			builder.append(" AND D.SUB_CUSTOMER_ID = :subCustomerId ");
		}
		
		String orderName = params.get("orderName");
		if(StringUtil.notEmpty(orderName)) {
			builder.append(" AND D.WFNAME LIKE :orderName ");
			params.put("orderName", "%"+orderName+"%");
		}
		
		
		String orderCode = params.get("orderCode");
		if(StringUtil.notEmpty(orderCode)) {
			builder.append(" AND D.ORDER_CODE LIKE :orderCode");
			params.put("orderCode", "%"+orderCode+"%");
		}
		
		String customerName = params.get("customerName");
		if(StringUtil.notEmpty(customerName)) {
			builder.append(" AND D.CUSTOMER_NAME LIKE :customerName ");
			params.put("customerName", "%"+customerName+"%");
		}
		return builder;
	}
	
	@Override
	public Map queryQuestionJxzNodeCount(String cusId) {
		//初始化变量
		int ywc = 0;
		//问题受理
		int wtsl = 0;
		
		//问题审核
		int wtsh = 0;
		
		//问题诊断
		int wtzd = 0;
		
		//方案审批
		int fasp = 0;
		
		//方案实施
		int fass = 0;
		
		//问题回顾
		int wthg = 0;
		
		
		//只有服务台和管理人员才可以看到所有的工单，（所以查询当前的用户属于那种服务台）
		StringBuffer selectSql = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		
		Map paramMap = new HashMap();
		selectSql.append( "SELECT COUNT(*) AS NUM,A.STATE FROM  SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B   WHERE A.ID = B.BUSINESS_ID AND B.WFSTATE = 1 "); 
		sql.append( "SELECT COUNT(A.ID)  FROM SC_WORKFLOW_QUESTION  A,WORKFLOW_BASE B  WHERE  A.ID = B.BUSINESS_ID AND B.WFSTATE = 1 ");
		selectSql.append(" AND A.CUSTOMER_ID = :cusId ");
		sql.append(" AND A.CUSTOMER_ID = :cusId ");
		paramMap.put("cusId", cusId);
		selectSql.append("  GROUP BY A.STATE ");
		
		List<Map> list = baseDao.queryForList(selectSql.toString(),paramMap);
		if(null!=list && list.size()>0) {
			for(Map map : list) {
				String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
				int num = ConvertUtil.obj2Int(map.get("NUM"));
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
					wtsl += num;
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
					wtsh += num;
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
					wtzd += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
					fasp += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
					fass += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
					wthg += num;
					continue;
				}
				
			}
		}
		int all = baseDao.queryForInteger(sql.toString(), paramMap);
		Map map = new HashMap();
		map.put("wtsl", wtsl);
		
		map.put("wtsh", wtsh);
		
		map.put("wtzd", wtzd);
		
		map.put("fasp", fasp);
		
		map.put("fass", fass);
		
		map.put("wthg", wthg);
		
		map.put("all", all);
		
		return map;
	}
	
	@Override
	public Map queryQuestionNodeCount(String cusId) {
        //初始化变量
		int ywc = 0;
		//问题受理
		int wtsl = 0;
		
		//问题审核
		int wtsh = 0;
		
		//问题诊断
		int wtzd = 0;
		
		//方案审批
		int fasp = 0;
		
		//方案实施
		int fass = 0;
		
		//问题回顾
		int wthg = 0;
		
		
		//只有服务台和管理人员才可以看到所有的工单，（所以查询当前的用户属于那种服务台）
		StringBuffer selectSql = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		
		Map paramMap = new HashMap();
		selectSql.append( "SELECT COUNT(*) AS NUM,D.STATE FROM (SELECT A.* FROM SC_WORKFLOW_QUESTION A,WORKFLOW_BASE B , WORKFLOW_NODE C  WHERE  B.ID = C.FLOWID AND C.FLOWSTATE = 1  AND C.CURRENT_DEALER_ID IS NOT  NULL AND A.ID = B.BUSINESS_ID  "); 
		sql.append( "SELECT COUNT(D.ID) FROM (SELECT A.*  FROM SC_WORKFLOW_QUESTION  A,WORKFLOW_BASE B , WORKFLOW_NODE C WHERE B.ID = C.FLOWID AND C.FLOWSTATE = 1  AND C.CURRENT_DEALER_ID IS NOT NULL AND A.ID = B.BUSINESS_ID ");
		selectSql.append(" AND A.CUSTOMER_ID = :cusId ");
		sql.append(" AND A.CUSTOMER_ID = :cusId ");
		paramMap.put("cusId",cusId);
		selectSql.append(" GROUP BY A.ID) D  GROUP BY D.STATE ");
		sql.append(" GROUP BY A.ID) D ");
		
		List<Map> list = baseDao.queryForList(selectSql.toString(),paramMap);
		if(null!=list && list.size()>0) {
			for(Map map : list) {
				String state = ConvertUtil.obj2StrBlank(map.get("STATE"));
				int num = ConvertUtil.obj2Int(map.get("NUM"));
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SL)) {
					wtsl += num;
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_SH)) {
					wtsh += num;
					continue;
				}
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_WTZD)) {
					wtzd += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASP)) {
					fasp += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_FASS)) {
					fass += num;
					continue;
				}
				
				if(state.equals(Constants.SC_WORKFLOW_QUESTION_STATE_HG)) {
					wthg += num;
					continue;
				}
				
			}
		}
		int all = baseDao.queryForInteger(sql.toString(), paramMap);
		Map map = new HashMap();
		map.put("wtsl", wtsl);
		
		map.put("wtsh", wtsh);
		
		map.put("wtzd", wtzd);
		
		map.put("fasp", fasp);
		
		map.put("fass", fass);
		
		map.put("wthg", wthg);
		
		map.put("all", all);
		
		return map;
	}
	@Override
	public int queryQuestionOrderNodeCount(Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT COUNT(D.ID) FROM (SELECT B.* FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB , WORKFLOW_NODE C   ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID AND  C.FLOWID = WB.ID  AND C.FLOWSTATE = 1 AND C.CURRENT_DEALER_ID IS NOT NULL AND WB.BUSINESSTYPE = :businessType ");
		
		sqlpart = getNodeSql(sqlpart,params);
		sql.append(sqlpart);
		sql.append( " GROUP BY B.ID  ) D");
		params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		int count = baseDao.queryForInteger(sql.toString(),params);
		return 0;
	}
	@Override
	public PageInfo queryQuestionOrderNodeList(PageInfo pageInfo,Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		StringBuilder sqlpart = new StringBuilder();
		sql.append("SELECT WB.ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE , ");
		sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.SUB_CUSTOMER_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,B.STATE ,B.NAME ");
		sql.append("FROM SC_WORKFLOW_QUESTION B,WORKFLOW_BASE WB , WORKFLOW_NODE C ");
		sqlpart.append(" WHERE WB.BUSINESS_ID = B.ID  AND  C.FLOWID = WB.ID AND C.FLOWSTATE = 1  AND C.CURRENT_DEALER_ID IS NOT NULL AND WB.BUSINESSTYPE = :businessType ");
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_QUESTION));
		
		sqlpart = getNodeSql(sqlpart,params);
		sql.append(sqlpart);
		sql.append( " GROUP BY WB.ID ORDER BY WB.CREATETIME DESC ");
		
		return baseDao.queryforSplitPageInfo(pageInfo, sql.toString(), params);
	}
	
	public StringBuilder getNodeSql(StringBuilder sqlpart,Map<String,String> params){
		
		String cusId = params.get("cusId");
		if(StringUtil.notEmpty(cusId)) {
			sqlpart.append(" AND B.CUSTOMER_ID = :cusId ");
		}
		String subCustomerId = params.get("subCustomerId");
		if(StringUtil.notEmpty(subCustomerId)) {
			sqlpart.append(" AND B.SUB_CUSTOMER_ID = :subCustomerId ");
		}
		
		String orderName = params.get("orderName");
		if(StringUtil.notEmpty(orderName)) {
			sqlpart.append(" AND B.NAME LIKE :orderName ");
			params.put("orderName", "%"+orderName+"%");
		}
		
		String orderCode = params.get("orderCode");
		if(StringUtil.notEmpty(orderCode)) {
			sqlpart.append(" AND B.ORDER_CODE LIKE :orderCode ");
			params.put("orderCode", "%"+orderCode+"%");
		}
		
		
		String contract = params.get("contract");
		if(StringUtil.notEmpty(contract)) {
			sqlpart.append(" AND B.CONTACT_NAME LIKE :contract ");
			params.put("contract", "%"+contract+"%");
		}
		
		
		String pri = params.get("pri");
		if(StringUtil.notEmpty(pri)) {
			String[] pris = pri.split(",");
			if(pris.length==1) {
				sqlpart.append(" AND B.PRIORITY_ID = :priority_id");
				params.put("priority_id", pris[0]);
			} else {
				sqlpart.append(" AND B.PRIORITY_ID IN (");
				for(int i=0; i<pris.length; i++) {
					sqlpart.append(" :pri"+i);
					sqlpart.append(",");
					params.put("pri"+i, pris[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		
		String dg = params.get("dg");
		if(StringUtil.notEmpty(dg)) {
			String[] dgs = dg.split(",");
			if(dgs.length==1) {
				sqlpart.append(" AND B.INFLUENCE_ID = :influence_id");
				params.put("influence_id", dgs[0]);
			} else {
				sqlpart.append(" AND B.INFLUENCE_ID IN (");
				for(int i=0; i<dgs.length; i++) {
					sqlpart.append(" :dg"+i);
					sqlpart.append(",");
					params.put("dg"+i, dgs[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String source = params.get("source");
		if(StringUtil.notEmpty(source)) {
			String[] sources = source.split(",");
			if(sources.length==1) {
				sqlpart.append(" AND B.SOURCE_ID = :type_id");
				params.put("type_id", sources[0]);
			} else {
				sqlpart.append(" AND B.SOURCE_ID IN (");
				for(int i=0; i<sources.length; i++) {
					sqlpart.append(" :type"+i);
					sqlpart.append(",");
					params.put("type"+i, sources[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		
		
		String sl = params.get("sl");
		if(StringUtil.notEmpty(sl)) {
			String[] sls = sl.split(",");
			if(sls.length==1) {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID = :third_category_id");
				params.put("third_category_id", sls[0]);
			} else {
				sqlpart.append(" AND B.THIRD_CATEGORY_ID IN (");
				for(int i=0; i<sls.length; i++) {
					sqlpart.append(" :third_category_id"+i);
					sqlpart.append(",");
					params.put("third_category_id"+i, sls[i]);
				}
				sqlpart.deleteCharAt(sqlpart.lastIndexOf(","));
				sqlpart.append(")");
			}
		}
		String status = params.get("status");
		if(StringUtil.notEmpty(status)) {
			sqlpart.append( " AND B.STATE = :state  ");
			params.put("state", status);
		}
		return sqlpart;
	}

}