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

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.beans.SC_PARTNER_CUSTOMER_INFO;
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.NumberUtil;
import cn.ksource.core.util.StringUtil;
import cn.ksource.core.web.WebUtil;
import cn.ksource.web.Constants;
import cn.ksource.web.service.file.FileService;


@Service("ucSatisFacade")
public class UcSatisFacadeImpl implements UcSatisFacade {
	
	@Autowired
	private BaseDao baseDao;
	
	@Autowired
	private FileService fileService;
	
	@Override
	public Map getCurMonthSatisSurvey(Map<String, String> params) {
		Map resMap = new HashMap(); 
		String cusId = params.get("cusId");
		StringBuilder builder = new StringBuilder();
		params.put("month", DateUtil.getCurrentDate6().toString());
		
		builder.append(" SELECT D.SATIS_TYPE,D.ALL_COUNT,D.SATIS_COUNT,D.FG_RATE,D.SATIS_SCORE ");
		builder.append(" FROM TOTLE_SATIS_INFO I,TOTLE_SATIS_DETAIL D  ");
		builder.append(" where  I.ID = D.BUS_ID and I.MONTH = :month and I.CUSTOMER_ID = :cusId group by D.SATIS_TYPE ");
		List<Map> list = baseDao.queryForList(builder.toString(),params);
		if(list!=null&&list.size()>0){
			for(Map map:list){
				if(ConvertUtil.obj2StrBlank(map.get("SATIS_TYPE")).equals("1")){
					resMap.put("answerTotalCount", ConvertUtil.obj2StrBlank(map.get("ALL_COUNT")));
					resMap.put("answerRealCount", ConvertUtil.obj2StrBlank(map.get("SATIS_COUNT")));
					resMap.put("answerDdcCount", ConvertUtil.obj2Integer(map.get("ALL_COUNT"))-ConvertUtil.obj2Integer(map.get("SATIS_COUNT")));
					resMap.put("answerRate", ConvertUtil.obj2StrBlank(map.get("FG_RATE")));
					resMap.put("answerScore", ConvertUtil.obj2StrBlank(map.get("SATIS_SCORE")));
				}else if(ConvertUtil.obj2StrBlank(map.get("SATIS_TYPE")).equals("3")){
					resMap.put("residentTotalCount", ConvertUtil.obj2StrBlank(map.get("ALL_COUNT")));
					resMap.put("residentRealCount", ConvertUtil.obj2StrBlank(map.get("SATIS_COUNT")));
					resMap.put("residentDdcCount", ConvertUtil.obj2Integer(map.get("ALL_COUNT"))-ConvertUtil.obj2Integer(map.get("SATIS_COUNT")));
					resMap.put("residentRate", ConvertUtil.obj2StrBlank(map.get("FG_RATE")));
					resMap.put("residentScore", ConvertUtil.obj2StrBlank(map.get("SATIS_SCORE")));
				}
			}
		}
		return resMap;
	}

	@Override
	public Map getLastYearSatisfactionCoverage(HttpServletRequest request) {
		Long endMonth = DateUtil.getCurrentDate6();
		Long startMonth = DateUtil.getDateAdd6FromMonth(endMonth, -12);
		String cusId = WebUtil.getWebLoginUserCusId(request);
		
		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.CUSTOMER_ID = :cusId GROUP BY D.SATIS_TYPE,I.MONTH  ORDER BY SATIS_TYPE,MONTH");
		List<Map> queryList = baseDao.queryForList(builder.toString(),new SqlParameter("cusId",cusId));
		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(startMonth, endMonth);
			List<String> categories_format = new  ArrayList<String>();
			List<String> typeList = new ArrayList();
			typeList.add("1");
			typeList.add("3");
			
			//组装数据
			for(String type:typeList){
				Map tempMap = new HashMap();
				List tempList = new ArrayList();
				if(type.equals("1")){
					tempMap.put("name", "响应支持类");
				}else if(type.equals("3")){
					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;
	}

	@Override
	public Map getLastYearSatisfactionMix(HttpServletRequest request) {
		Long endMonth = DateUtil.getCurrentDate6();
		Long startMonth = DateUtil.getDateAdd6FromMonth(endMonth, -12);
		String cusId = WebUtil.getWebLoginUserCusId(request);
		
		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.CUSTOMER_ID = :cusId  GROUP BY D.SATIS_TYPE,I.MONTH  ORDER BY SATIS_TYPE,MONTH");
		List<Map> queryList = baseDao.queryForList(builder.toString(),new SqlParameter("cusId",cusId));
		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(startMonth, endMonth);
			List<String> categories_format = new  ArrayList<String>();
			List<String> typeList = new ArrayList();
			typeList.add("1");
			typeList.add("3");
			
			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("3")){
					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);
			}
			
		}
		return chartMap;
	}

	@Override
	public int queryIncisatisCount(Map<String,String> params) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT( DISTINCT WB.ID ) ");
		sql.append("FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB LEFT JOIN ORDER_SATIS_INFO C ON C.BUSINESS_ID = WB.ID  WHERE WB.BUSINESS_ID = B.ID  ");
		sql.append("AND WB.BUSINESSTYPE = :businessType ");
		params.put("businessType",ConvertUtil.obj2StrBlank( Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT));
		sql = getIncidentSql(sql,params);
		return baseDao.queryForInteger(sql.toString(),params);
	}

	@Override
	public PageInfo queryIncisatisData(Map<String,String> params,PageInfo pageInfo) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT DISTINCT WB.ID,WB.ORDER_CODE,WB.ENDTIME,WB.WFNAME,WB.BUSINESSTYPE,WB.CREATERNAME,WB.CREATETIME,WB.WFNOTE,B.CONTACT_NAME,B.CONTACT_PHONE, ");
		sql.append("WB.CURRENT_NODE_ID,WB.CURRENT_NODE_NAME,WB.CUSTOMER_NAME,WB.CURRENT_DEALER_NAME,WB.BUSINESS_ID,WB.WFSTATE,C.SCORE ");
		sql.append("FROM SC_WORKFLOW_INCIDENT B,WORKFLOW_BASE WB LEFT JOIN ORDER_SATIS_INFO C ON C.BUSINESS_ID = WB.ID  WHERE WB.BUSINESS_ID = B.ID ");
		sql.append("AND WB.BUSINESSTYPE = :businessType ");
		params.put("businessType", ConvertUtil.obj2StrBlank(Constants.WORKFLOW_BASE_BUSINESS_TYPE_INCIDENT));
		
		sql = getIncidentSql(sql,params);
		
		sql.append( " ORDER BY WB.CREATETIME DESC  ");
		return baseDao.queryforSplitPageInfo(pageInfo,sql.toString(),params);
	}

	private StringBuilder getIncidentSql(StringBuilder sql,
			Map<String, String> params) {
		sql.append(" AND B.CUSTOMER_ID = :customer_id ");
		
		String contract = params.get("contract");
		if(StringUtil.notEmpty(contract)) {
			sql.append(" AND B.CONTACT_NAME LIKE :contract ");
			params.put("contract", "%"+contract+"%");
		}
		
		
		String ordercode = params.get("ordercode");
		if(StringUtil.notEmpty(ordercode)) {
			sql.append(" AND WB.ORDER_CODE LIKE :ordercode ");
			params.put("ordercode", "%"+ordercode+"%");
		}
		
		String orderName = params.get("orderName");
		if(StringUtil.notEmpty(orderName)) {
			sql.append(" AND WB.WFNAME LIKE :orderName ");
			params.put("orderName", "%"+orderName+"%");
		}
		
		String begin_time = params.get("begin_time");
		if(StringUtil.notEmpty(begin_time)) {
			sql.append(" AND WB.CREATETIME >= :begin_time ");
			params.put("begin_time", begin_time+"000000");
		}
		String end_time = params.get("end_time");
		if(StringUtil.notEmpty(begin_time)) {
			sql.append(" AND WB.CREATETIME <= :end_time ");
			params.put("end_time", end_time+"240000");
		}
		
		String satistype = params.get("satistype");
		if(StringUtil.notEmpty(satistype)) {
			String[] satistypes = satistype.split(",");
			
				sql.append(" AND WB.WFSTATE IN (");
				for(int i=0; i<satistypes.length; i++) {
					sql.append(" :state"+i);
					sql.append(",");
					params.put("state"+i, satistypes[i]);
				}
				sql.deleteCharAt(sql.lastIndexOf(","));
				sql.append(")");
		}else{
			sql.append("AND (WB.WFSTATE = 2 OR WB.WFSTATE = 4) ");
		}
		return sql;
	}


	@Override
	public Map querysatisitemData(String type, String busId) {
		StringBuffer sql = new StringBuffer("SELECT WB.ID,WB.BUSINESS_ID,WB.ORDER_CODE,WB.CUSTOMER_NAME,WB.WFNAME,WB.BUSINESSTYPE,A.CUSTOMER_CODE FROM WORKFLOW_BASE WB ,SC_PARTNER_CUSTOMER_INFO A ");
		sql.append(" WHERE WB.ID = :busId AND WB.CUSTOMER_ID = A.ID ");
		Map customerMap = baseDao.queryForMap(sql.toString(), new SqlParameter("busId",busId));
		customerMap.put("BUSINESSTYPE_TEXT", Constants.mapWORKFLOW_BUSINESS_TYPE_Label(ConvertUtil.obj2Str(customerMap.get("BUSINESSTYPE"))));
		StringBuffer sqlYj = new StringBuffer("SELECT C.*,B.ID AS TEMPLATE_ID FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B ");
		sqlYj.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 1 AND C.TEMPLATE_ID = B.ID AND B.STATE = 1 AND B.TEMPLATE_TYPE = :template_type  ORDER BY C.ORDERNUM ");
		StringBuffer sqlEj = new StringBuffer("SELECT C.*,B.ID AS TEMPLATE_ID,D.TEMPLATE_WEIGHT AS BIG_WEIGHT FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B,CUSTOMER_SATIS_TEMPLATE_ITEM D ");
		sqlEj.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 2 AND C.TEMPLATE_ID = B.ID AND B.STATE = 1 AND B.TEMPLATE_TYPE = :template_type  AND C.FATHER_ID = D.ID ORDER BY C.ORDERNUM ");
		StringBuffer sqlEva = new StringBuffer("SELECT A.* FROM SATIS_EVA_ITEM A  ");
		sqlEva.append(" WHERE  A.ITEM_ID IN (SELECT C.ID FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B,CUSTOMER_SATIS_TEMPLATE_ITEM D ");
		sqlEva.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 2 AND C.TEMPLATE_ID = B.ID AND B.STATE = 1 AND B.TEMPLATE_TYPE = :template_type  AND C.FATHER_ID = D.ID ) ORDER BY A.ORDERNUM  ");
		Map param = new HashMap();
		param.put("template_type", type);
		List<Map> itemYjList = baseDao.queryForList(sqlYj.toString(),param);
		List<Map> itemList = baseDao.queryForList(sqlEj.toString(),param);
		List<Map> evaList = baseDao.queryForList(sqlEva.toString(),param);
		
		if(itemList != null && itemList.size()>0){
			for(Map item:itemList){
				List<Map> evaluList = new ArrayList<Map>();
				for(Map eva:evaList){
					if(eva.get("ITEM_ID").equals(item.get("ID"))){
						evaluList.add(eva);
					}
				}
				
				item.put("EVALUATION", evaluList);
				item.put("ROWSPAN", evaluList.size());
			}
		}
         if(itemYjList.size()>0){
			
			for(Map temp:itemYjList){
				List<Map> itemEjList = new ArrayList();
	            for(Map item:itemList){
					if(temp.get("ID").equals(item.get("FATHER_ID"))){
						itemEjList.add(item);
					}
				}
	            temp.put("itemList", itemEjList);
	            temp.put("ROWSPAN", itemEjList.size());
			}
		}
		Map cardMap = new HashMap();
		cardMap.put("customerMap", customerMap);
		cardMap.put("itemYjList", itemYjList);
		cardMap.put("itemList", itemList);
		cardMap.put("count",itemYjList.size()+itemList.size());
		if(itemYjList.size()>0){
			cardMap.put("TEMPLATE_ID",itemYjList.get(0).get("TEMPLATE_ID"));
		}
		return cardMap;
	}

	@Override
	public Map getReportDetail(String busId, String type) {
		StringBuffer sql = new StringBuffer("SELECT O.*,WB.ID AS FLOWID,WB.BUSINESS_ID AS BUS_ID,WB.ORDER_CODE,WB.WFNAME,WB.BUSINESSTYPE FROM WORKFLOW_BASE WB,ORDER_SATIS_INFO O ");
		sql.append(" WHERE WB.ID = :busId AND O.BUSINESS_ID = WB.ID ");
		Map customerMap = baseDao.queryForMap(sql.toString(), new SqlParameter("busId",busId));
		customerMap.put("BUSINESSTYPE_TEXT", Constants.mapWORKFLOW_BUSINESS_TYPE_Label(ConvertUtil.obj2Str(customerMap.get("BUSINESSTYPE"))));
		StringBuffer sqlYj = new StringBuffer("SELECT C.*,B.ID AS TEMPLATE_ID FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B ");
		sqlYj.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 1 AND C.TEMPLATE_ID = B.ID  AND B.ID = :id AND B.TEMPLATE_TYPE = :template_type ORDER BY C.ORDERNUM ");
		StringBuffer sqlEj = new StringBuffer("SELECT C.*,B.ID AS TEMPLATE_ID,D.TEMPLATE_WEIGHT AS BIG_WEIGHT FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B,CUSTOMER_SATIS_TEMPLATE_ITEM D ");
		sqlEj.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 2 AND C.TEMPLATE_ID = B.ID  AND B.ID = :id AND B.TEMPLATE_TYPE = :template_type AND C.FATHER_ID = D.ID ORDER BY C.ORDERNUM ");
		StringBuffer sqlEva = new StringBuffer("SELECT A.* FROM SATIS_EVA_ITEM A  ");
		sqlEva.append(" WHERE  A.ITEM_ID IN (SELECT C.ID FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B,CUSTOMER_SATIS_TEMPLATE_ITEM D ");
		sqlEva.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 2 AND C.TEMPLATE_ID = B.ID  AND B.ID = :id AND B.TEMPLATE_TYPE = :template_type AND C.FATHER_ID = D.ID ) ORDER BY A.ORDERNUM  ");
		Map param = new HashMap();
		param.put("template_type", type);
		param.put("id", customerMap.get("TEMPLATE_ID"));
		List<Map> itemYjList = baseDao.queryForList(sqlYj.toString(),param);
		List<Map> itemList = baseDao.queryForList(sqlEj.toString(),param);
		List<Map> evaList = baseDao.queryForList(sqlEva.toString(),param);
		
		StringBuffer sqlitem = new StringBuffer("SELECT B.* FROM ORDER_SATIS_INFO A,SATIS_INFO_DETAIL B");
		sqlitem.append(" WHERE B.BUSINESS_ID = A.ID AND A.BUSINESS_ID = :business_id ");
		Map paramitem = new HashMap();
		paramitem.put("business_id", busId);
		List<Map> itList = baseDao.queryForList(sqlitem.toString(), paramitem);
		if(itemList != null && itemList.size()>0){
			for(Map item:itemList){
				List<Map> evaluList = new ArrayList<Map>();
				for(Map eva:evaList){
					if(eva.get("ITEM_ID").equals(item.get("ID"))){
						evaluList.add(eva);
					}
				}
				item.put("EVALUATION", evaluList);
			}
            for(Map items:itemList){
				
					List<Map> evaluation = (List)items.get("EVALUATION");
					for(Map it:itList){
						if(it.get("CONFIGID").equals(items.get("ID"))){
							for(Map eva:evaluation){
								if(ConvertUtil.obj2Int(eva.get("score"))==ConvertUtil.obj2Int((it.get("RESULTSCORE")))){
									eva.put("check", 1);
								}
							}
						}
					}
			}
			
		}
        if(itemYjList.size()>0){
			
			for(Map temp:itemYjList){
				List<Map> itemEjList = new ArrayList();
	            for(Map item:itemList){
					if(temp.get("ID").equals(item.get("FATHER_ID"))){
						itemEjList.add(item);
					}
				}
	            temp.put("itemList", itemEjList);
	            temp.put("ROWSPAN", itemEjList.size());
			}
		}
		Map cardMap = new HashMap();
		cardMap.put("customerMap", customerMap);
		cardMap.put("itemYjList", itemYjList);
		cardMap.put("itemList", itemList);
		cardMap.put("count",itemYjList.size()+itemList.size());
		return cardMap;
	}

	@Override
	public String getImageMsg(String busId) {
		String sql = "SELECT ID FROM ORDER_SATIS_INFO WHERE BUSINESS_ID = :business_id ";
		String id = baseDao.queryForString(sql,new SqlParameter("business_id",busId));
		List<Map> fileList = fileService.getFileList(id);
		String path="";
		if(fileList.size() > 0){
			path = ConvertUtil.obj2Str(fileList.get(0).get("FILE_PATH"));
			
		}
		return path;
	}

	@Override
	public Map gettjmsg(String cusId,String type) {
		String month = ConvertUtil.obj2StrBlank(DateUtil.getCurrentDate6());
		String sql = "SELECT * FROM TOTLE_SATIS_INFO A,TOTLE_SATIS_DETAIL B WHERE A.CUSTOMER_ID = :cusId AND A.MONTH = :month AND A.ID = B.BUS_ID AND B.SATIS_TYPE = :type ";
		Map param = new HashMap();
		param.put("cusId", cusId);
		param.put("month", month);
		param.put("type", type);
		Map nowmsgMap = baseDao.queryForMap(sql, param);
		String before = "";
		int nowmonth = ConvertUtil.obj2Int(month.substring(month.length()-2, month.length()));
		int nowyear = ConvertUtil.obj2Int( month.substring(0,month.length()-2));
		if(nowmonth >= 11){
			before = nowyear + ConvertUtil.obj2Str(nowmonth - 1);
		}else if(nowmonth < 11){
			if(nowmonth == 1){
				before = nowyear -1+"12";
			}else{
				before = nowyear + "0"+ConvertUtil.obj2Str(nowmonth-1);
			}
		}
		param.put("month", before);
		Map beformsgMap = baseDao.queryForMap(sql, param);
		if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(nowmsgMap.get("ID")))){
	        nowmsgMap.put("ddc", NumberUtil.sub(ConvertUtil.obj2Double(nowmsgMap.get("ALL_COUNT")), ConvertUtil.obj2Double(nowmsgMap.get("SATIS_COUNT"))));
	    }
		nowmsgMap.put("before", beformsgMap.get("ALL_SCORE"));
		
		if(StringUtil.isNotBlank(ConvertUtil.obj2StrBlank(nowmsgMap.get("ID")))&&!StringUtil.isEmpty(ConvertUtil.obj2StrBlank(beformsgMap.get("ALL_SCORE")))){
			nowmsgMap.put("hbzz", NumberUtil.div(NumberUtil.mul(ConvertUtil.obj2Double(nowmsgMap.get("ALL_SCORE")),ConvertUtil.obj2Double(nowmsgMap.get("before"))), ConvertUtil.obj2Double(nowmsgMap.get("before"))));
		}else{
			nowmsgMap.put("hbzz", "-");
		}
		return nowmsgMap;
	}

	@Override
	public int queryZcsatisCount(Map<String, String> params) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(E.ID)  FROM ((SELECT A.USER_ID ,A.USER_NAME AS ALLUSER,A.CUSTOMER_ID AS SUB_CUSTOMER_ID,B.CUSTOMER_NAME AS SUB_CUSTOMER_NAME ,B.CUSTOMER_ID,C.CUSTOMER_NAME FROM SC_CUSTOMER_ZC_UESR A , SC_PARTNER_CUSTOMER_SUB B ,SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = B.ID AND B.CUSTOMER_ID = C.ID )  ");
		sql.append(" UNION ALL ");
		sql.append(" (SELECT A.USER_ID ,A.USER_NAME AS ALLUSER,A.CUSTOMER_ID AS SUB_CUSTOMER_ID,C.CUSTOMER_NAME AS SUB_CUSTOMER_NAME ,C.ID AS CUSTOMER_ID,C.CUSTOMER_NAME FROM SC_CUSTOMER_ZC_UESR A , SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = C.ID ) ) D ");
		
		sql.append("  LEFT JOIN AUDITING_SATIS_INFO E  ON E.USER_ID = D.USER_ID  WHERE 1 = 1 ");
		
		sql = getZcsql(sql,params);
		
		return baseDao.queryForInteger(sql.toString(), params);
	}

	@Override
	public PageInfo queryZcsatisData(PageInfo pageInfo,
			Map<String, String> params) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT E.* ,D.USER_ID ,D.ALLUSER,D.SUB_CUSTOMER_ID,D.SUB_CUSTOMER_NAME,D.CUSTOMER_ID,D.CUSTOMER_NAME FROM ((SELECT A.USER_ID ,A.USER_NAME AS ALLUSER,A.CUSTOMER_ID AS SUB_CUSTOMER_ID,B.CUSTOMER_NAME AS SUB_CUSTOMER_NAME ,B.CUSTOMER_ID,C.CUSTOMER_NAME FROM SC_CUSTOMER_ZC_UESR A , SC_PARTNER_CUSTOMER_SUB B ,SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = B.ID AND B.CUSTOMER_ID = C.ID )  ");
		sql.append(" UNION ALL ");
		sql.append(" (SELECT A.USER_ID ,A.USER_NAME AS ALLUSER,A.CUSTOMER_ID AS SUB_CUSTOMER_ID,C.CUSTOMER_NAME AS SUB_CUSTOMER_NAME ,C.ID AS CUSTOMER_ID,C.CUSTOMER_NAME FROM SC_CUSTOMER_ZC_UESR A , SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = C.ID ) ) D ");
		
		sql.append("  LEFT JOIN AUDITING_SATIS_INFO E  ON E.USER_ID = D.USER_ID  WHERE 1 = 1 ");
		
		sql = getZcsql(sql,params);
		
		sql.append( " ORDER BY E.CARD_MONTH DESC,E.SUBMIT_TIME DESC  ");
		return baseDao.queryforSplitPageInfo(pageInfo,sql.toString(),params);
	}

	private StringBuilder getZcsql(StringBuilder sql, Map<String, String> params) {
		String ismy = params.get("ismy");
		
		if("1".equals(ismy)){
			sql.append(" AND D.USER_ID = :userId ");
		}
		
        String customer_id = params.get("customer_id");
		
		sql.append(" AND D.CUSTOMER_ID = :customer_id ");
		
		
		String beginMonth = params.get("begin_month");
		String endMonth = params.get("end_month");
		
		if(StringUtil.notEmpty(beginMonth)) {
			sql.append(" AND E.CARD_MONTH >= :beginMonth ");
		}
		
		if(StringUtil.notEmpty(endMonth)) {
			sql.append(" AND E.CARD_MONTH <= :endMonth ");
		}
		
		String spstate = params.get("spstate");
		if(StringUtil.notEmpty(spstate)) {
			sql.append(" AND E.STATE = :spstate ");
			
		}
		return sql;
	}

	@Override
	public Map getZcReportDetail(String busId) {
		StringBuffer sql = new StringBuffer("SELECT * FROM AUDITING_SATIS_INFO  WHERE ID = :busId ");
		Map customerMap = baseDao.queryForMap(sql.toString(), new SqlParameter("busId",busId));
		StringBuffer sqlYj = new StringBuffer("SELECT C.*,B.ID AS TEMPLATE_ID FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B ");
		sqlYj.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 1 AND C.TEMPLATE_ID = B.ID  AND B.ID = :id ORDER BY C.ORDERNUM ");
		StringBuffer sqlEj = new StringBuffer("SELECT C.*,B.ID AS TEMPLATE_ID,D.TEMPLATE_WEIGHT AS BIG_WEIGHT FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B,CUSTOMER_SATIS_TEMPLATE_ITEM D ");
		sqlEj.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 2 AND C.TEMPLATE_ID = B.ID  AND B.ID = :id AND C.FATHER_ID = D.ID ORDER BY C.ORDERNUM ");
		StringBuffer sqlEva = new StringBuffer("SELECT A.* FROM SATIS_EVA_ITEM A  ");
		sqlEva.append(" WHERE  A.ITEM_ID IN (SELECT C.ID FROM CUSTOMER_SATIS_TEMPLATE_ITEM C,CUSTOMER_SATIS_TEMPLATE B,CUSTOMER_SATIS_TEMPLATE_ITEM D ");
		sqlEva.append(" WHERE  C.STATE = 1 AND C.ITEM_LEVEL = 2 AND C.TEMPLATE_ID = B.ID AND B.ID = :id AND C.FATHER_ID = D.ID ) ORDER BY A.ORDERNUM  ");
		Map param = new HashMap();
		param.put("id", customerMap.get("TEMPLATE_ID"));
		List<Map> itemYjList = baseDao.queryForList(sqlYj.toString(),param);
		List<Map> itemList = baseDao.queryForList(sqlEj.toString(),param);
		List<Map> evaList = baseDao.queryForList(sqlEva.toString(),param);
		
		StringBuffer sqlitem = new StringBuffer("SELECT B.* FROM AUDITING_SATIS_INFO A,SATIS_INFO_DETAIL B");
		sqlitem.append(" WHERE B.BUSINESS_ID = A.ID AND A.ID = :business_id ");
		Map paramitem = new HashMap();
		paramitem.put("business_id", busId);
		List<Map> itList = baseDao.queryForList(sqlitem.toString(), paramitem);
		if(itemList != null && itemList.size()>0){
			for(Map item:itemList){
				List<Map> evaluList = new ArrayList<Map>();
				for(Map eva:evaList){
					if(eva.get("ITEM_ID").equals(item.get("ID"))){
						evaluList.add(eva);
					}
				}
				item.put("EVALUATION", evaluList);
			}
            for(Map items:itemList){
				
					List<Map> evaluation = (List)items.get("EVALUATION");
					for(Map it:itList){
						if(it.get("CONFIGID").equals(items.get("ID"))){
							for(Map eva:evaluation){
								if(ConvertUtil.obj2Int(eva.get("score"))==ConvertUtil.obj2Int((it.get("RESULTSCORE")))){
									eva.put("check", 1);
								}
							}
						}
					}
			}
			
		}
        if(itemYjList.size()>0){
			
			for(Map temp:itemYjList){
				List<Map> itemEjList = new ArrayList();
	            for(Map item:itemList){
					if(temp.get("ID").equals(item.get("FATHER_ID"))){
						itemEjList.add(item);
					}
				}
	            temp.put("itemList", itemEjList);
	            temp.put("ROWSPAN", itemEjList.size());
			}
		}
		Map cardMap = new HashMap();
		cardMap.put("itemYjList", itemYjList);
		cardMap.put("itemList", itemList);
		cardMap.put("count",itemYjList.size()+itemList.size());
		return cardMap;
	}

	@Override
	public Map getMonthSatisReport(Map<String, String> params) {
		String cusId = params.get("customer_id");
		if(!StringUtil.notEmpty(cusId)) {
			return new HashMap();
		}
		
		
		String beginMonth = params.get("begin_month");
		String endMonth = params.get("end_month");
		if(!StringUtil.notEmpty(beginMonth)) {
			beginMonth = DateUtil.getCurrentDate6().toString();
		}
		
		if(!StringUtil.notEmpty(endMonth)) {
			endMonth = DateUtil.getCurrentDate6().toString();
		}
		String showmonth = params.get("showmonth");
		StringBuffer sql = new StringBuffer("SELECT AVG(A.ALL_SCORE) AS ALL_SCORE ,A.CUSTOMER_ID,B.CUSTOMER_NAME FROM TOTLE_SATIS_INFO A,SC_PARTNER_CUSTOMER_INFO B WHERE A.CUSTOMER_ID = B.ID AND A.CUSTOMER_ID = :cusId AND A.MONTH BETWEEN :beginMonth AND :endMonth GROUP BY A.CUSTOMER_ID");
        SqlParameter param = new SqlParameter();
		param.put("cusId", cusId);
		param.put("beginMonth", beginMonth);
		param.put("endMonth", endMonth);
		Map map = baseDao.queryForMap(sql.toString(),param);
		if(!StringUtil.isEmpty(ConvertUtil.obj2StrBlank(map.get("ALL_SCORE")))){
			map.put("ALL_SCORE", NumberUtil.toDecimal2(ConvertUtil.obj2Double(map.get("ALL_SCORE"))));
		}
		
		StringBuffer detailsql = new StringBuffer("SELECT A.CUSTOMER_ID,B.SATIS_TYPE,SUM(B.ALL_COUNT) AS ALL_COUNT,SUM(SATIS_COUNT) AS SATIS_COUNT,AVG(FG_RATE) AS FG_RATE,AVG(REQUEST_FG_RATE) AS REQUEST_FG_RATE,AVG(SATIS_SCORE) AS SATIS_SCORE,AVG(REQUEST_SATIS_SCORE) AS REQUEST_SATIS_SCORE,AVG(SATIS_QZ) AS SATIS_QZ FROM TOTLE_SATIS_INFO A,TOTLE_SATIS_DETAIL B WHERE  A.ID = B.BUS_ID AND A.CUSTOMER_ID = :cusId  AND A.MONTH BETWEEN :beginMonth AND :endMonth  ");
		detailsql.append(" GROUP BY A.CUSTOMER_ID,B.SATIS_TYPE ORDER BY B.SATIS_TYPE ");
		List<Map> list = baseDao.queryForList(detailsql.toString(), param);
		if(list.size() > 0){
			for(Map map1:list){
				map1.put("SATIS_TYPE_TEXT", Constants.getmapCUSTOMER_SATIS_TEMPLATE_TEMPLATE_TYPE_Label(ConvertUtil.obj2StrBlank(map1.get("SATIS_TYPE"))));
				map1.put("SATIS_SCORE", NumberUtil.toDecimal2(ConvertUtil.obj2Double(map1.get("SATIS_SCORE"))));
				map1.put("REQUEST_SATIS_SCORE", NumberUtil.toDecimal2(ConvertUtil.obj2Double(map1.get("REQUEST_SATIS_SCORE"))));
			}
		}
		map.put("detail", list);
		return map;
	}

	@Override
	public List<Map> getTotalMonthSatisReport(Map<String, String> params) {
		String beginMonth = params.get("begin_month");
		String endMonth = params.get("end_month");
		if(!StringUtil.notEmpty(beginMonth)) {
			beginMonth = DateUtil.getCurrentDate6().toString();
		}
		
		if(!StringUtil.notEmpty(endMonth)) {
			endMonth = DateUtil.getCurrentDate6().toString();
		}
		
		
		StringBuffer sql = new StringBuffer("SELECT A.CUSTOMER_ID,AVG(A.ALL_SCORE) AS ALL_SCORE,C.CUSTOMER_NAME FROM TOTLE_SATIS_INFO A,SC_PARTNER_CUSTOMER_INFO C WHERE A.CUSTOMER_ID = C.ID AND A.MONTH BETWEEN :beginMonth AND :endMonth ");
		StringBuffer detailsql = new StringBuffer("SELECT A.CUSTOMER_ID,B.SATIS_TYPE,AVG(B.ALL_COUNT) AS DCZS,AVG(B.SATIS_COUNT) AS SJDCZS,AVG(B.FG_RATE) AS FGL,AVG(B.REQUEST_FG_RATE) YQFGL,AVG(SATIS_SCORE) AS DF,AVG(REQUEST_SATIS_SCORE) AS YQDF FROM TOTLE_SATIS_INFO A,TOTLE_SATIS_DETAIL B WHERE  A.ID = B.BUS_ID AND A.MONTH BETWEEN :beginMonth AND :endMonth ");
		
		Map paramMap = new HashMap();
		
			
		String customerid = params.get("customerId");
		if(StringUtil.notEmpty(customerid)) {
			sql.append(" AND A.CUSTOMER_ID = :customerId ");
			detailsql.append(" AND A.CUSTOMER_ID = :customerId ");
			paramMap.put("customerId",customerid);
		}
		paramMap.put("beginMonth", beginMonth);
		paramMap.put("endMonth", endMonth);
		
		sql.append(" GROUP BY A.CUSTOMER_ID ");
		
		List<Map> proList = baseDao.queryForList(sql.toString(),paramMap);
		
		
		detailsql.append(" GROUP BY A.CUSTOMER_ID,B.SATIS_TYPE ORDER BY B.SATIS_TYPE ");
		List<Map> detaillist = baseDao.queryForList(detailsql.toString(), paramMap);
		
		
		if(null!=proList && proList.size()>0) {
			
			Map cacheMap = new HashMap();
			if(null != detaillist && detaillist.size()>0) {
				for(Map m : detaillist) {
					String projectId = ConvertUtil.obj2StrBlank(m.get("CUSTOMER_ID"));
					String satisType = ConvertUtil.obj2StrBlank(m.get("SATIS_TYPE"));
					String unitKey = projectId+satisType;
					cacheMap.put(unitKey, m);
				}
			}
			
			for(Map totalMap : proList) {
				String cusId = ConvertUtil.obj2StrBlank(totalMap.get("CUSTOMER_ID"));
				totalMap.put("ALL_SCORE", NumberUtil.toDecimal2(ConvertUtil.obj2Double(totalMap.get("ALL_SCORE"))));
				//求出项目响应支持类得分
				String xyKey = cusId+Constants.CUSTOMER_SATIS_TEMPLATE_TEMPLATE_TYPE_ONE;
				Map xym = (Map)cacheMap.get(xyKey);
				double xydf = ConvertUtil.obj2Double(xym.get("DF"));
				totalMap.put("XYSCORE", NumberUtil.toDecimal2(xydf));
				
				//求出项目驻场服务类得分
				String zcfwKey = cusId+Constants.CUSTOMER_SATIS_TEMPLATE_TEMPLATE_TYPE_THREE;
				Map zcfwm = (Map)cacheMap.get(zcfwKey);
				double zcfwdf = ConvertUtil.obj2Double(zcfwm.get("DF"));
				totalMap.put("ZCSCORE", NumberUtil.toDecimal2(zcfwdf));
				
			}
		}
		
		return proList;
	}

	
}