package cn.com.countrygarden.bi.main.service.bigholderSatisfy.impl;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.github.pagehelper.PageRowBounds;

import cn.com.countrygarden.bi.main.dao.BigholderRightManagerMapper;
import cn.com.countrygarden.bi.main.dao.bigholderSatisfy.BigholderSatisfyMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbHsprCommunityMapper;
import cn.com.countrygarden.bi.main.dao.twerp.hm.TbSysOrganMapper;
import cn.com.countrygarden.bi.main.dto.AreaUser;
import cn.com.countrygarden.bi.main.dto.BigholderImportedDto;
import cn.com.countrygarden.bi.main.dto.BigholderProjectDto;
import cn.com.countrygarden.bi.main.dto.SatisfyInfo;
import cn.com.countrygarden.bi.main.dto.bigholder.UnsatisfyDto;
import cn.com.countrygarden.bi.main.model.BigholderLandEstate;
import cn.com.countrygarden.bi.main.model.BigholderRightManager;
import cn.com.countrygarden.bi.main.model.BigholderSatisfacationInfo;
import cn.com.countrygarden.bi.main.service.bigholderSatisfy.BigholderSatisfyService;
import cn.com.countrygarden.bi.main.util.erp.StringUtil;

@Service
@Transactional
public class BigholderSatisfyServiceImpl implements BigholderSatisfyService {

	/**
	 * 大业主总部管理员
	 */
	private final static String TOTAL_ADMIN = "dyz_admin";

	/**
	 * 按月度
	 */
	private final static String TIME_TYPE_MONTH = "0";

	/**
	 * 按季度
	 */
	private final static String TIME_TYPE_QUARTER = "1";

	@Autowired
	private BigholderSatisfyMapper bigholderSatisfyMapper;

	@Autowired
	private BigholderRightManagerMapper bigholderRightManagerMapper;

	@Autowired
	private TbSysOrganMapper tbSysOrganMapper;
	
	@Autowired
	private TbHsprCommunityMapper tbHsprCommunityMapper;

	@SuppressWarnings("unchecked")
	@Override
	public HashMap<String, Object> addSatisfyList(MultipartFile multipartFile, String fileName, String userCode) {
		HashMap<String, Object> result = new HashMap<String, Object>();
		Map<String, String> areaMap = new HashMap<>();
		List<BigholderRightManager> areas = getAreas();
		if (areas != null && areas.size() > 0) {
			for (BigholderRightManager bigholderRightManager : areas) {
				String area = bigholderRightManager.getArea();
				String areaId = bigholderRightManager.getAreaId();
				areaMap.put(area, areaId);
			}
		}
		HashMap<String, Object> map = readExcel(multipartFile, fileName, userCode, areaMap);
		List<BigholderSatisfacationInfo> list = (ArrayList<BigholderSatisfacationInfo>) map.get("infolist");
		List<String> errorInfoList = (ArrayList<String>) map.get("errorInfoList");
		if (errorInfoList != null && errorInfoList.size() > 0) {
			result.put("errorInfo", errorInfoList);
			return result;
		}
		ArrayList<String> updateAuditAreas = (ArrayList<String>) map.get("updateAuditAreas");
		if (updateAuditAreas != null && updateAuditAreas.size() > 0) {
			bigholderSatisfyMapper.updateAuditAreas(updateAuditAreas);
			bigholderSatisfyMapper.updateSatisfyAreas(updateAuditAreas);
			bigholderSatisfyMapper.updateExceptionDatas(updateAuditAreas);
			bigholderSatisfyMapper.updatePushDatas();
		}
		// String answerFinishTime = list.get(0).getAnswerFinishTime();
		// 覆盖当月数据(先删除，后插入)
		// bigholderSatisfyMapper.deleteImport(answerFinishTime);
		if (null != list && list.size() > 0) {
			int limit = 500;
			int num = list.size() / limit;
			if (num > 0) {
				for (int i = 0; i < num; i++) {
					List<BigholderSatisfacationInfo> tempList = list.subList(0, limit);
					bigholderSatisfyMapper.addSatisfyList(tempList);
					list.subList(0, limit).clear();
				}
				if (list.size() > 0) {
					bigholderSatisfyMapper.addSatisfyList(list);
					list.clear();
				}
			} else {
				bigholderSatisfyMapper.addSatisfyList(list);
				list.clear();
			}
		}
		result.put("flag", "ok");
		return result;
	}

	/**
	 * 读取excel文件（项目总问卷）
	 * 
	 * @param mf
	 * @param fileName
	 * @param userCode
	 * @param areaMap
	 * @return
	 */
	public HashMap<String, Object> readExcel(MultipartFile mf, String fileName, String userCode,
			Map<String, String> areaMap) {
		Map<String,String> existOwnerMap = new HashMap<String,String>();
		//查询区域权限列表
		List<BigholderRightManager> rightList = bigholderRightManagerMapper.findRightsGroupByArea();
		if(rightList!=null&&rightList.size()>0) {
			for (BigholderRightManager bigholderRightManager : rightList) {
				existOwnerMap.put(bigholderRightManager.getArea(), bigholderRightManager.gethandUser());
			}
		}
		HashMap<String, Object> map = new HashMap<String, Object>();
		ArrayList<String> updateAuditAreas = new ArrayList<String>();
		List<BigholderSatisfacationInfo> infolist = new ArrayList<BigholderSatisfacationInfo>();
		List<String> errorInfoList = new ArrayList<String>();
		DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
		Workbook xwb = null;
		BufferedInputStream bf = null;
		Sheet sheet = null;
		try {
			bf = new BufferedInputStream(mf.getInputStream());
			if (fileName.endsWith("xls")) {
				// Excel 2003
				xwb = new HSSFWorkbook(bf);
			} else if (fileName.endsWith("xlsx")) {
				// Excel 2007+
				xwb = new XSSFWorkbook(bf);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		// 获取第1个页面
		sheet = xwb.getSheetAt(0);
		// 获取表头总列数
		Integer columnNum = 0;
		Row row0 = sheet.getRow(0);
		if (row0 != null) {
			columnNum = row0.getPhysicalNumberOfCells();
			if (columnNum != 30) {
				errorInfoList.add("【excel模板不匹配,请用正确的excel模板导入】");
			}
		} else {
			errorInfoList.add("【excel表头不存在,请用正确的excel模板导入】");
		}
		if (errorInfoList.size() > 0) {
			map.put("errorInfoList", errorInfoList);
			return map;
		}
		for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				// 开始校验数据(excel关键字段)
				String strFinishTime = getValue(row.getCell(1));
				if (StringUtil.isNotEmpty(strFinishTime)) {
					try {
						 Date parseDate = sdf.parse(strFinishTime);
					} catch (ParseException e) {
						errorInfoList.add("excel第【" + (i + 1) + "】行的第2列 [提交答卷时间] 格式不正确===>>>参考格式(yyyy/MM/dd或yyyy-MM-dd)");
					}
				} 
				String projectName = getValue(row.getCell(26));
				if (!StringUtils.hasText(projectName)) {
					errorInfoList.add("excel第【" + (i + 1) + "】行的第27列 [项目名称] 不能为空或 #N/A等非法字符");
				}
				String job = getValue(row.getCell(28));
				if (!StringUtils.hasText(job)) {
					errorInfoList.add("excel第【" + (i + 1) + "】行的第29列 [您的岗位] 不能为空或 #N/A等非法字符");
				} else {
					if (job.indexOf(" ") != -1) {
						errorInfoList.add("excel第【" + (i + 1) + "】行的第29列 [您的岗位] 存在空格");
					} else {
						if (!("项目营销负责人".equals(job) || "项目总经理/副总经理".equals(job))) {
							errorInfoList.add("excel第【" + (i + 1) + "】行的第29列 [您的岗位] 只能填(项目营销负责人)或(项目总经理/副总经理)");
						}
					}
				}
				String estateArea;
				estateArea = getValue(row.getCell(29));
				if (!StringUtils.hasText(estateArea)) {
					errorInfoList.add("excel第【" + (i + 1) + "】行的第30列 [物业区域] 不能为空或 #N/A等非法字符");
				}
			} else {
				continue;
			}
		}
		if (errorInfoList.size() > 0) {
			map.put("errorInfoList", errorInfoList);
			return map;
		}
		for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				// 开始保存数据
				String strFinishTime = getValue(row.getCell(1));
				if(!StringUtils.hasText(strFinishTime)) {
					strFinishTime = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date());
				}
				String projectName = getValue(row.getCell(26));
				String job = getValue(row.getCell(28));
				String estateArea = getValue(row.getCell(29));
				BigholderSatisfacationInfo model = new BigholderSatisfacationInfo();
				model.setAnswerFinishTime(strFinishTime);
				model.setAnswerUseTime(getValue(row.getCell(2)));
				model.setAnswerSource(getValue(row.getCell(3)));
				model.setAnswerSourceInfo(getValue(row.getCell(4)));
				model.setAnswerSourceIp(getValue(row.getCell(5)));
				model.setA1(getValue(row.getCell(6)));
				model.setA2(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(7))).substring(0, 1))));
				model.setA3(getValue(row.getCell(8)));
				model.setA41(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(9))).substring(0, 1))));
				model.setA42(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(10))).substring(0, 1))));
				model.setA43(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(11))).substring(0, 1))));
				model.setA44(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(12))).substring(0, 1))));
				model.setA51(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(13))).substring(0, 1))));
				model.setA52(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(14))).substring(0, 1))));
				model.setA53(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(15))).substring(0, 1))));
				model.setA54(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(16))).substring(0, 1))));
				model.setA61(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(17))).substring(0, 1))));
				model.setA62(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(18))).substring(0, 1))));
				model.setA71(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(19))).substring(0, 1))));
				model.setA72(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(20))).substring(0, 1))));
				model.setA73(Integer.parseInt(toIntValue(myTrim(getValue(row.getCell(21))).substring(0, 1))));
				model.setA8(getValue(row.getCell(22)));
				model.setA9(getValue(row.getCell(23)));
				model.setA10(getValue(row.getCell(24)));
				model.setArea(getValue(row.getCell(25)));
				model.setProjectName(projectName);
				model.setName(getValue(row.getCell(27)));
				model.setJob(job);
				// 判断区域
				if (!StringUtils.hasText(areaMap.get(estateArea))) {
					model.setEstateAreaIsRed("1");
				} else {
					model.setEstateAreaIsRed("0");
					updateAuditAreas.add(estateArea);
				}
				if (!StringUtils.hasText(existOwnerMap.get(estateArea))) {
					model.setIsExistOwner("0");
				} else {
					model.setIsExistOwner("1");
				}
				model.setEstateArea(estateArea);
				model.setCreateBy(userCode);
				model.setLastUpdateBy(userCode);
				Calendar cal = Calendar.getInstance();
				cal.setTime(new Date());
				cal.add(Calendar.MONTH, -1);
				model.setCreateDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cal.getTime()));
				model.setLastUpdateDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
				infolist.add(model);
			} else {
				continue;
			}
		}
		map.put("infolist", infolist);
		map.put("updateAuditAreas", updateAuditAreas);
		return map;
	}

	/**
	 * 获取cell数据
	 * 
	 * @param cell
	 * @return
	 */
	@SuppressWarnings({ "static-access", "deprecation" })
	private String getValue(Cell cell) {
		if (null == cell||String.valueOf(cell).contains("#N/A")||String.valueOf(cell).contains("VLOOKUP")||String.valueOf(cell).contains("vlookup")) {
			return "";
		} else {
			if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
				return String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
				} else {
					return String.valueOf(cell.getNumericCellValue());
				}
			} else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {
				try {
					return String.valueOf(cell.getNumericCellValue());
				} catch (IllegalStateException e) {
					return String.valueOf(cell.getRichStringCellValue());
				}
			} else {
				return String.valueOf(cell.getStringCellValue());
			}
		}
	}

	/**
	 * 处理空格
	 * 
	 * @param value
	 * @return
	 */
	private String myTrim(String value) {
		if (StringUtils.hasText(value.trim())) {
			return value.trim();
		} else {
			return " ";
		}
	}

	/*

	 * 处理项目总问卷评分数据
	 */
	private String toIntValue(String value) {
		switch (value) {
		case "1":
		case "2":
		case "3":
		case "4":
		case "5":
			return value;
		default:
			return "0";
		}
	}
	
	/*
	 * 处理区域总裁问卷评分数据
	 */
	private Integer toIntValue2(String value) {
		switch (value) {
		case "0":
		case "1":
		case "2":
		case "3":
		case "4":
		case "5":
			return Integer.parseInt(value);
		default:
			return null;
		}
	}

	@Override
	public List<BigholderRightManager> getAreas() {
		return tbSysOrganMapper.getAreas();
	}

	@Override
	public boolean updateArea(String estateArea, String area,String project) {
		List<BigholderRightManager> list = bigholderRightManagerMapper.findRightsByArea(area,null);
		String isExistOwner = "0";
		if(list!=null&&list.size()>0) {
			isExistOwner = "1";
			bigholderSatisfyMapper.updateArea(estateArea, area ,project,isExistOwner);
		}else {
			bigholderSatisfyMapper.updateArea(estateArea, area ,project,isExistOwner);
		}
		// 修改修正后的区域的审核状态
		ArrayList<String> updateAuditAreas = new ArrayList<String>();
		updateAuditAreas.add(estateArea);
		updateAuditAreas.add(area);
		bigholderSatisfyMapper.updateSatisfyAreas(updateAuditAreas);
		bigholderSatisfyMapper.updateAuditAreas(updateAuditAreas);
		bigholderSatisfyMapper.updateExceptionDatas(updateAuditAreas);
		bigholderSatisfyMapper.updatePushDatas();
		return true;
	}

	@Override
	public List<BigholderRightManager> getUsers(String user, PageRowBounds pageRowBounds) {
		List<Map<String, String>> users = tbSysOrganMapper.getUsers(user, pageRowBounds);
		List<BigholderRightManager> list = new ArrayList<>();
		for (Map<String, String> map : users) {
			String userId = map.get("userId");
			String depName = map.get("depName");
			String handUser = map.get("handUser");
			BigholderRightManager rightManager = new BigholderRightManager();
			rightManager.setUserId(userId);
			rightManager.sethandUser(handUser);
			rightManager.setDepName(depName+"-"+handUser);
			list.add(rightManager);
		}
		return list;
	}

	@Override
	public boolean addRightList(String area, String areaId, String user, String userId) {
		List<BigholderRightManager> rights = bigholderRightManagerMapper.findRightsByArea(area,null);
		for (BigholderRightManager bigholderRightManager : rights) {
			if(user.equals(bigholderRightManager.gethandUser())) {
				return false;
			}
		}
		bigholderRightManagerMapper.addRightList(area, areaId, user, userId);
		String flag = "1";
		bigholderSatisfyMapper.updateIsExistOwner(area,flag);
		return true;
	}

	@Override
	public boolean deleteRightList(String areaId, String userId) {
		List<BigholderRightManager> rights = bigholderRightManagerMapper.findRightsByArea(null,areaId);
		bigholderRightManagerMapper.deleteRightList(areaId, userId);
		if(rights!=null&&rights.size()>1) {
			return true;
		}else {
			String area = rights.get(0).getArea();
			String flag = "0";
			bigholderSatisfyMapper.updateIsExistOwner(area,flag);
			return true;
		}
	}

	@Override
	public List<AreaUser> findRightList(String user, String area, PageRowBounds pageRowBounds) {
		List<BigholderRightManager> rightList = bigholderRightManagerMapper.findRightList(user);
		List<BigholderRightManager> areas = null;
		if (StringUtils.hasText(user)) {
			List<String> areaIds = bigholderRightManagerMapper.findAreaIds(user);
			if (areaIds == null || areaIds.size() == 0) {
				return new ArrayList<AreaUser>();
			}
			areas = tbSysOrganMapper.getAreasWithCondition(area, areaIds, pageRowBounds);
		} else {
			List<String> areaIds = new ArrayList<String>();
			areas = tbSysOrganMapper.getAreasWithCondition(area, areaIds, pageRowBounds);
		}
		List<AreaUser> areaUserList = new ArrayList<AreaUser>();
		if (areas != null && areas.size() > 0) {
			for (BigholderRightManager areaU : areas) {
				AreaUser areaUser = new AreaUser();
				areaUser.setArea(areaU.getArea());
				areaUser.setAreaId(areaU.getAreaId());
				List<AreaUser> areaUsers = new ArrayList<AreaUser>();
				if (rightList != null && rightList.size() > 0) {
					for (BigholderRightManager bigholderRightManager : rightList) {
						AreaUser areaUser2 = new AreaUser();
						if (areaU.getArea().equals(bigholderRightManager.getArea())) {
							areaUser2.setUser((bigholderRightManager.gethandUser()));
							areaUser2.setUserId(bigholderRightManager.getUserId());
							areaUser2.setAreaId(areaU.getAreaId());
							areaUsers.add(areaUser2);
						}
					}
				}
				areaUser.setAreaUsers(areaUsers);
				areaUserList.add(areaUser);
			}
		}
		return areaUserList;
	}

	@Override
	public List<BigholderImportedDto> findImportedList(String yearMonth, PageRowBounds pageRowBounds) {
		List<BigholderImportedDto> list = bigholderSatisfyMapper.findImportedList(yearMonth, pageRowBounds);
		List<BigholderRightManager> rightList = bigholderRightManagerMapper.findRightList(null);
		HashMap<String, List<String>> map = new HashMap<String,List<String>>();
		for (BigholderRightManager bigholderRightManager : rightList) {
			String area = bigholderRightManager.getArea();
			String user = bigholderRightManager.gethandUser();
			if(map.containsKey(area)) {
				List<String> list1 = map.get(area);
				list1.add(user);
			}else {
				List<String> list2 = new ArrayList<String>();
				list2.add(user);
				map.put(area, list2);
			}
		}
		for (BigholderImportedDto bigholderImportedDto : list) {
			String estateArea = bigholderImportedDto.getEstateArea();
			if(map.get(estateArea)!=null) {
				bigholderImportedDto.setHandUsers(map.get(estateArea));
			}else {
				bigholderImportedDto.setHandUsers(new ArrayList<>());
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public HashMap<String, Object> addLandEstateList(MultipartFile multipartFile, String fileName, String userCode) {
		// 判断当前季度（一个季度只能导入一次总裁问卷）
		Calendar calendar = Calendar.getInstance();
		int year = calendar.get(Calendar.YEAR);
		int month = calendar.get(Calendar.MONTH) + 1;
		// 存某一季度的年月
		ArrayList<String> quarters = new ArrayList<String>();
		switch (month) {
		case 1:
		case 2:
		case 3:
			quarters.add(year + "-01");
			quarters.add(year + "-02");
			quarters.add(year + "-03");
			break;
		case 4:
		case 5:
		case 6:
			quarters.add(year + "-04");
			quarters.add(year + "-05");
			quarters.add(year + "-06");
			break;
		case 7:
		case 8:
		case 9:
			quarters.add(year + "-07");
			quarters.add(year + "-08");
			quarters.add(year + "-09");
			break;
		default:
			quarters.add(year + "-10");
			quarters.add(year + "-11");
			quarters.add(year + "-12");
			break;
		}
//		List<String> ids = bigholderSatisfyMapper.findDataByQuarters(quarters);
		HashMap<String, Object> result = new HashMap<String, Object>();
//		if (ids != null && ids.size() > 0) {
//			result.put("quarterExist", "1");
//			return result;
//		}
		Map<String, String> areaMap = new HashMap<>();
		List<BigholderRightManager> areas = getAreas();
		if (areas != null && areas.size() > 0) {
			for (BigholderRightManager bigholderRightManager : areas) {
				String area = bigholderRightManager.getArea();
				String areaId = bigholderRightManager.getAreaId();
				areaMap.put(area, areaId);
			}
		}
		HashMap<String, Object> map = readExcel2(multipartFile, fileName, userCode, areaMap);
		List<String> errorInfoList = (List<String>) map.get("errorInfoList");
		if (errorInfoList != null && errorInfoList.size() > 0) {
			result.put("errorInfo", errorInfoList);
			return result;
		}
		List<BigholderLandEstate> list = (ArrayList<BigholderLandEstate>) map.get("infolist");
		// String answerFinishTime = list.get(0).getAnswerFinishTime();
		// 覆盖当月数据(先删除，后插入)
		// bigholderSatisfyMapper.deleteImport(answerFinishTime);
		if (null != list && list.size() > 0) {
			int limit = 500;
			int num = list.size() / limit;
			if (num > 0) {
				for (int i = 0; i < num; i++) {
					List<BigholderLandEstate> tempList = list.subList(0, limit);
					bigholderSatisfyMapper.addLandEstateList(tempList);
					list.subList(0, limit).clear();
				}
				if (list.size() > 0) {
					bigholderSatisfyMapper.addLandEstateList(list);
					list.clear();
				}
			} else {
				bigholderSatisfyMapper.addLandEstateList(list);
				list.clear();
			}
		}
		result.put("flag", "ok");
		return result;
	}

	/**
	 * 读取excel文件（总裁问卷）
	 * 
	 * @param mf
	 * @param fileName
	 * @param userCode
	 * @param areaMap
	 * @return
	 */
	public HashMap<String, Object> readExcel2(MultipartFile mf, String fileName, String userCode,
			Map<String, String> areaMap) {
		HashMap<String, Object> map = new HashMap<String, Object>();
		// DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
		Workbook xwb = null;
		BufferedInputStream bf = null;
		Sheet sheet = null;
		List<BigholderLandEstate> infolist = new ArrayList<BigholderLandEstate>();
		List<String> errorInfoList = new ArrayList<String>();
		try {
			bf = new BufferedInputStream(mf.getInputStream());
			if (fileName.endsWith("xls")) {
				// Excel 2003
				xwb = new HSSFWorkbook(bf);
			} else if (fileName.endsWith("xlsx")) {
				// Excel 2007+
				xwb = new XSSFWorkbook(bf);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		// 获取第1个页面
		sheet = xwb.getSheetAt(0);
		// 获取表头总列数
		Integer columnNum = 0;
		Row row0 = sheet.getRow(0);
		if (row0 != null) {
			columnNum = row0.getPhysicalNumberOfCells();
			if (columnNum != 10) {
				errorInfoList.add("【excel模板不匹配,请用正确的excel模板导入】");
			}
		} else {
			errorInfoList.add("【excel表头不存在,请用正确的excel模板导入】");
		}
		if (errorInfoList.size() > 0) {
			map.put("errorInfoList", errorInfoList);
			return map;
		}
		for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				// 开始校验数据(excel关键字段)
				String estateArea = getValue(row.getCell(1));
				if (!StringUtils.hasText(estateArea)) {
					errorInfoList.add("excel第【" + (i + 1) + "】行的第2列 [物业区域] 不能为空或 #N/A等非法字符");
				} else {
					if (!StringUtils.hasText(areaMap.get(estateArea))) {
						errorInfoList.add("excel第【" + (i + 1) + "】行的第2列 [物业区域] 不能正确匹配，请修改为能正确匹配的区域");
					}
				}
			} else {
				continue;
			}
		}
		if (errorInfoList.size() > 0) {
			map.put("errorInfoList", errorInfoList);
			return map;
		}
		for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
			Row row = sheet.getRow(i);
			if (row != null) {
				// 保存数据
				String estateArea = getValue(row.getCell(1));
				BigholderLandEstate model = new BigholderLandEstate();
				model.setLandArea(getValue(row.getCell(0)));
				model.setEstateArea(estateArea);
				model.setA2(getValue(row.getCell(2)));

				model.setA3(toIntValue2(myTrim(getValue(row.getCell(3))).substring(0, 1)));
				model.setA4(toIntValue2(myTrim(getValue(row.getCell(4))).substring(0, 1)));
				model.setA8(toIntValue2(myTrim(getValue(row.getCell(5))).substring(0, 1)));
				model.setA9(toIntValue2(myTrim(getValue(row.getCell(6))).substring(0, 1)));
				model.setA10(toIntValue2(myTrim(getValue(row.getCell(7))).substring(0, 1)));
				model.setA11(getValue(row.getCell(8)));
				model.setA12(getValue(row.getCell(9)));
				model.setCreateBy(userCode);
				model.setLastUpdateBy(userCode);
				model.setCreateDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
				model.setLastUpdateDate(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
				infolist.add(model);
			} else {
				continue;
			}
		}
		map.put("infolist", infolist);
		return map;
	}

	@Override
	public List<BigholderProjectDto> projectRankList(String userCode,String yearMonth, String yearMonthStart, String yearMonthEnd,
			String timeType, PageRowBounds pageRowBounds) {
		// 获取登陆者权限控制
		boolean isTotalAdmin = isTotalAdmin(userCode);
		List<BigholderProjectDto> projectRankList = null;
		List<String> nowMonthList = new ArrayList<>();
		List<String> lastMonthList = new ArrayList<>();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		Calendar cal1 = Calendar.getInstance();
		Calendar cal2 = Calendar.getInstance();
		Calendar cal3 = Calendar.getInstance();
		Calendar cal4 = Calendar.getInstance();
		try {
			cal1.setTime(sdf.parse(yearMonthEnd));
			cal2.setTime(sdf.parse(yearMonthStart));
			if(StringUtils.hasText(yearMonth)) {
				cal3.setTime(sdf.parse(yearMonth));
			}
			cal4.setTime(sdf.parse(yearMonthStart));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		cal1.add(Calendar.MONTH, -1);
		String yearMonthEndLastMonth = sdf.format(cal1.getTime());
		cal2.add(Calendar.MONTH, -1);
		String lastMonth1 = sdf.format(cal2.getTime());
		cal2.add(Calendar.MONTH, -1);
		String lastMonth2 = sdf.format(cal2.getTime());
		cal2.add(Calendar.MONTH, -1);
		String lastMonth3 = sdf.format(cal2.getTime());
		cal4.add(Calendar.MONTH, +1);
		String secondMonth = sdf.format(cal4.getTime());
		cal4.add(Calendar.MONTH, +1);
		String thirdMonth = sdf.format(cal4.getTime());
		List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
		if (isTotalAdmin) {
			List<String> areas = tbSysOrganMapper.getAreasOnly();
			if (TIME_TYPE_MONTH.equals(timeType)) {
				nowMonthList.add(yearMonthEnd);
				lastMonthList.add(yearMonthEndLastMonth);
				if(StringUtils.hasText(yearMonth)) {
					cal3.add(Calendar.MONTH, -1);
					String lastMonth = sdf.format(cal3.getTime());
					nowMonthList.clear();
					lastMonthList.clear();
					nowMonthList.add(yearMonth);
					lastMonthList.add(lastMonth);
				}
				if(pageRowBounds==null) {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList);
				}else {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList,
							pageRowBounds);
				}
			}
			if (TIME_TYPE_QUARTER.equals(timeType)) {
				nowMonthList.add(yearMonthStart);
				nowMonthList.add(secondMonth);
				nowMonthList.add(thirdMonth);
				lastMonthList.add(lastMonth1);
				lastMonthList.add(lastMonth2);
				lastMonthList.add(lastMonth3);
				if(pageRowBounds==null) {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList);
				}else {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList,
							pageRowBounds);
				}
			}
		} else {
			List<String> areas = bigholderRightManagerMapper.findAreasByUserCode(userCode);
			if(areas==null||areas.size()==0) {
				areas.add("x");
			}
			if (TIME_TYPE_MONTH.equals(timeType)) {
				nowMonthList.add(yearMonthEnd);
				lastMonthList.add(yearMonthEndLastMonth);
				if(StringUtils.hasText(yearMonth)) {
					cal3.add(Calendar.MONTH, -1);
					String lastMonth = sdf.format(cal3.getTime());
					nowMonthList.clear();
					lastMonthList.clear();
					nowMonthList.add(yearMonth);
					lastMonthList.add(lastMonth);
				}
				if(pageRowBounds==null) {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList);
				}else {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList,
							pageRowBounds);
				}
			}
			if (TIME_TYPE_QUARTER.equals(timeType)) {
				nowMonthList.add(yearMonthStart);
				nowMonthList.add(secondMonth);
				nowMonthList.add(thirdMonth);
				lastMonthList.add(lastMonth1);
				lastMonthList.add(lastMonth2);
				lastMonthList.add(lastMonth3);
				if(pageRowBounds==null) {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList);
				}else {
					projectRankList = bigholderSatisfyMapper.projectRankList(areas, nowMonthList, lastMonthList,
							pageRowBounds);
				}
			}
		}
		if (TIME_TYPE_MONTH.equals(timeType)) {
			for (BigholderProjectDto projectDto : projectRankList) {
				HashMap<String, String> map = new HashMap<String, String>();
				String projectId = projectDto.getProjectName();
				String projectName = "";
				for (String month : months) {
					List<String> monthList = new ArrayList<String>();
					monthList.add(month);
					ArrayList<String> areas = new ArrayList<String>();
					areas.add(projectDto.getEstateArea());
					String satisfy = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, projectId);
					/*if(!StringUtils.hasText(satisfy)) {
						satisfy = "0.00";
					}*/
					map.put(month, satisfy);
				}
				projectDto.setMap(map);
				projectDto.setProjectId(projectId);
				projectName = tbHsprCommunityMapper.findBaseCommByCommId(projectId).get(0).getCommName();
				projectDto.setProjectName(projectName);
			}
		}
		if (TIME_TYPE_QUARTER.equals(timeType)) {
			String quarter = getQuarterByMonth(months.get(0));
			for (BigholderProjectDto projectDto : projectRankList) {
				HashMap<String, String> map = new HashMap<String, String>();
				String projectId = projectDto.getProjectName();
				String satisfy = projectDto.getSatisfy1();
				String projectName = "";
				/*if(!StringUtils.hasText(satisfy)) {
					satisfy = "0.00";
				}*/
				map.put(quarter, satisfy);
				projectDto.setMap(map);
				projectDto.setProjectId(projectId);
				projectName = tbHsprCommunityMapper.findBaseCommByCommId(projectId).get(0).getCommName();
				projectDto.setProjectName(projectName);
			}
		}
		return projectRankList;
	}

	@Override
	public SatisfyInfo satisfyInfosByTime(String userCode, String area, String project, String yearMonthStart,
			String yearMonthEnd, String timeType) {
		boolean isTotalAdmin = isTotalAdmin(userCode);
		List<String> monthList = new ArrayList<>();
		SatisfyInfo satisfyInfo = new SatisfyInfo();
		if (isTotalAdmin) {
			satisfyInfo.setIsTotalAdmin("1");
			// 全国(多区域)
			if (!StringUtils.hasText(area) && !StringUtils.hasText(project)) {
				if (TIME_TYPE_MONTH.equals(timeType)) {
					List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
					List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
					for (String month : months) {
						monthList.add(month);
						String satisfy = bigholderSatisfyMapper.satisfyInfosByMonth(monthList,null);
						SatisfyInfo satisfyInfo2 = new SatisfyInfo();
						satisfyInfo2.setYearMonth(month);
						if (satisfy == null || !StringUtils.hasText(satisfy)) {
							satisfy = "0.00";
						}
						satisfyInfo2.setSatisfy(satisfy);
						satisfyInfos.add(satisfyInfo2);
						monthList.clear();
					}
					satisfyInfo.setSatisfyInfos(satisfyInfos);
				}
				if (TIME_TYPE_QUARTER.equals(timeType)) {
					List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
					monthList.add(months.get(0));
					monthList.add(months.get(1));
					monthList.add(months.get(2));
					String satisfy1 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, null);
					monthList.clear();
					monthList.add(months.get(3));
					monthList.add(months.get(4));
					monthList.add(months.get(5));
					String satisfy2 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, null);
					monthList.clear();
					monthList.add(months.get(6));
					monthList.add(months.get(7));
					monthList.add(months.get(8));
					String satisfy3 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, null);
					monthList.clear();
					monthList.add(months.get(9));
					monthList.add(months.get(10));
					monthList.add(months.get(11));
					String satisfy4 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, null);
					if (satisfy1 == null || !StringUtils.hasText(satisfy1)) {
						satisfy1 = "0.00";
					}
					if (satisfy2 == null || !StringUtils.hasText(satisfy2)) {
						satisfy2 = "0.00";
					}
					if (satisfy3 == null || !StringUtils.hasText(satisfy3)) {
						satisfy3 = "0.00";
					}
					if (satisfy4 == null || !StringUtils.hasText(satisfy4)) {
						satisfy4 = "0.00";
					}
					List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy1, null, "第1季度", null, null));
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy2, null, "第2季度", null, null));
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy3, null, "第3季度", null, null));
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy4, null, "第4季度", null, null));
					satisfyInfo.setSatisfyInfos(satisfyInfos);
				}
			}
		} else {
			satisfyInfo.setIsTotalAdmin("0");
			// 全国(多区域)满意度
			if (!StringUtils.hasText(area) && !StringUtils.hasText(project)) {
				List<String> areas = bigholderRightManagerMapper.findAreasByUserCode(userCode);
				if(areas==null||areas.size()==0) {
					List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
					List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
					for (String month : months) {
						String satisfy = "0.00";
						SatisfyInfo satisfyInfo2 = new SatisfyInfo();
						satisfyInfo2.setYearMonth(month);
						satisfyInfo2.setSatisfy(satisfy);
						satisfyInfos.add(satisfyInfo2);
					}
					satisfyInfo.setSatisfyInfos(satisfyInfos);
					return satisfyInfo;
				}
				if (TIME_TYPE_MONTH.equals(timeType)) {
					List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
					List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
					for (String month : months) {
						monthList.add(month);
						String satisfy = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
						if (satisfy == null || !StringUtils.hasText(satisfy)) {
							satisfy = "0.00";
						}
						SatisfyInfo satisfyInfo2 = new SatisfyInfo();
						satisfyInfo2.setYearMonth(month);
						satisfyInfo2.setSatisfy(satisfy);
						satisfyInfos.add(satisfyInfo2);
						monthList.clear();
					}
					satisfyInfo.setSatisfyInfos(satisfyInfos);
				}
				if (TIME_TYPE_QUARTER.equals(timeType)) {
					List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
					monthList.add(months.get(0));
					monthList.add(months.get(1));
					monthList.add(months.get(2));
					String satisfy1 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
					monthList.clear();
					monthList.add(months.get(3));
					monthList.add(months.get(4));
					monthList.add(months.get(5));
					String satisfy2 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
					monthList.clear();
					monthList.add(months.get(6));
					monthList.add(months.get(7));
					monthList.add(months.get(8));
					String satisfy3 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
					monthList.clear();
					monthList.add(months.get(9));
					monthList.add(months.get(10));
					monthList.add(months.get(11));
					String satisfy4 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
					if (satisfy1 == null || !StringUtils.hasText(satisfy1)) {
						satisfy1 = "0.00";
					}
					if (satisfy2 == null || !StringUtils.hasText(satisfy2)) {
						satisfy2 = "0.00";
					}
					if (satisfy3 == null || !StringUtils.hasText(satisfy3)) {
						satisfy3 = "0.00";
					}
					if (satisfy4 == null || !StringUtils.hasText(satisfy4)) {
						satisfy4 = "0.00";
					}
					List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy1, null, "第1季度", null, null));
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy2, null, "第2季度", null, null));
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy3, null, "第3季度", null, null));
					satisfyInfos.add(new SatisfyInfo(null, null, satisfy4, null, "第4季度", null, null));
					satisfyInfo.setSatisfyInfos(satisfyInfos);
				}
			}
		}
		// 区域
		if (StringUtils.hasText(area) && !StringUtils.hasText(project)) {
			List<String> areas = new ArrayList<>();
			areas.add(area);
			if (TIME_TYPE_MONTH.equals(timeType)) {
				List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
				List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
				for (String month : months) {
					monthList.add(month);
					String satisfy = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
					if (satisfy == null || !StringUtils.hasText(satisfy)) {
						satisfy = "0.00";
					}
					SatisfyInfo satisfyInfo2 = new SatisfyInfo();
					satisfyInfo2.setYearMonth(month);
					satisfyInfo2.setSatisfy(satisfy);
					satisfyInfos.add(satisfyInfo2);
					monthList.clear();
				}
				satisfyInfo.setSatisfyInfos(satisfyInfos);
			}
			if (TIME_TYPE_QUARTER.equals(timeType)) {
				List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
				monthList.add(months.get(0));
				monthList.add(months.get(1));
				monthList.add(months.get(2));
				String satisfy1 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
				monthList.clear();
				monthList.add(months.get(3));
				monthList.add(months.get(4));
				monthList.add(months.get(5));
				String satisfy2 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
				monthList.clear();
				monthList.add(months.get(6));
				monthList.add(months.get(7));
				monthList.add(months.get(8));
				String satisfy3 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
				monthList.clear();
				monthList.add(months.get(9));
				monthList.add(months.get(10));
				monthList.add(months.get(11));
				String satisfy4 = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
				if (satisfy1 == null || !StringUtils.hasText(satisfy1)) {
					satisfy1 = "0.00";
				}
				if (satisfy2 == null || !StringUtils.hasText(satisfy2)) {
					satisfy2 = "0.00";
				}
				if (satisfy3 == null || !StringUtils.hasText(satisfy3)) {
					satisfy3 = "0.00";
				}
				if (satisfy4 == null || !StringUtils.hasText(satisfy4)) {
					satisfy4 = "0.00";
				}
				List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy1, null, "第1季度", null, null));
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy2, null, "第2季度", null, null));
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy3, null, "第3季度", null, null));
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy4, null, "第4季度", null, null));
				satisfyInfo.setSatisfyInfos(satisfyInfos);
			}
		}
		// 项目
		if (StringUtils.hasText(area) && StringUtils.hasText(project)) {
			List<String> areas = new ArrayList<>();
			areas.add(area);
			if (TIME_TYPE_MONTH.equals(timeType)) {
				List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
				List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
				for (String month : months) {
					monthList.add(month);
					String satisfy = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, project);
					if (satisfy == null || !StringUtils.hasText(satisfy)) {
						satisfy = "0.00";
					}
					SatisfyInfo satisfyInfo2 = new SatisfyInfo();
					satisfyInfo2.setYearMonth(month);
					satisfyInfo2.setSatisfy(satisfy);
					satisfyInfos.add(satisfyInfo2);
					monthList.clear();
				}
				satisfyInfo.setSatisfyInfos(satisfyInfos);
			}
			if (TIME_TYPE_QUARTER.equals(timeType)) {
				List<String> months = getMonthBetween(yearMonthStart, yearMonthEnd);
				monthList.add(months.get(0));
				monthList.add(months.get(1));
				monthList.add(months.get(2));
				String satisfy1 = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, project);
				monthList.clear();
				monthList.add(months.get(3));
				monthList.add(months.get(4));
				monthList.add(months.get(5));
				String satisfy2 = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, project);
				monthList.clear();
				monthList.add(months.get(6));
				monthList.add(months.get(7));
				monthList.add(months.get(8));
				String satisfy3 = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, project);
				monthList.clear();
				monthList.add(months.get(9));
				monthList.add(months.get(10));
				monthList.add(months.get(11));
				String satisfy4 = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, project);
				if (satisfy1 == null || !StringUtils.hasText(satisfy1)) {
					satisfy1 = "0.00";
				}
				if (satisfy2 == null || !StringUtils.hasText(satisfy2)) {
					satisfy2 = "0.00";
				}
				if (satisfy3 == null || !StringUtils.hasText(satisfy3)) {
					satisfy3 = "0.00";
				}
				if (satisfy4 == null || !StringUtils.hasText(satisfy4)) {
					satisfy4 = "0.00";
				}
				List<SatisfyInfo> satisfyInfos = new ArrayList<SatisfyInfo>();
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy1, null, "第1季度", null, null));
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy2, null, "第2季度", null, null));
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy3, null, "第3季度", null, null));
				satisfyInfos.add(new SatisfyInfo(null, null, satisfy4, null, "第4季度", null, null));
				satisfyInfo.setSatisfyInfos(satisfyInfos);
			}
		}
		return satisfyInfo;
	}

	@Override
	public List<UnsatisfyDto> unsatisfyInfo(String userCode, String area, String project, String yearMonthStart,
			String yearMonthEnd) {
		boolean isTotalAdmin = isTotalAdmin(userCode);
		List<String> monthList = getMonthBetween(yearMonthStart, yearMonthEnd);
		List<String> areas = null;
		if (!isTotalAdmin) {
			areas = bigholderRightManagerMapper.findAreasByUserCode(userCode);
			if(areas==null||areas.size()==0) {
				areas.add("x");//没有权限设置为x
			}
		}
		List<UnsatisfyDto> unsatisfyInfos = null;
		// 全国(多区域)
		if (!StringUtils.hasText(area) && !StringUtils.hasText(project)) {
			unsatisfyInfos = bigholderSatisfyMapper.unsatisfyInfo(areas, null, monthList);
		}
		// 区域
		if (StringUtils.hasText(area) && !StringUtils.hasText(project)) {
			List<String> areas2 = new ArrayList<String>();
			areas2.add(area);
			unsatisfyInfos = bigholderSatisfyMapper.unsatisfyInfo(areas2, null, monthList);
		}
		// 项目
		if (StringUtils.hasText(area) && StringUtils.hasText(project)) {
			unsatisfyInfos = bigholderSatisfyMapper.unsatisfyInfo(areas, project, monthList);
		}
		List<UnsatisfyDto> dtos = new ArrayList<>();
		for(int i=0;i<unsatisfyInfos.size();i++) {
			if(unsatisfyInfos.get(i)!=null&&!"0".equals(unsatisfyInfos.get(i).getTotal())) {
				dtos.add(unsatisfyInfos.get(i));
			}
		}
		return dtos;
	}

	@Override
	public List<UnsatisfyDto> trendAnalysis(String userCode, String area, String project, String yearMonthStart,
			String yearMonthEnd, String type) {
		boolean isTotalAdmin = isTotalAdmin(userCode);
		List<String> monthList = getMonthBetween(yearMonthStart, yearMonthEnd);
		List<String> areas = null;
		List<UnsatisfyDto> unsatisfyInfos = new ArrayList<>();
		if (!isTotalAdmin) {
			areas = bigholderRightManagerMapper.findAreasByUserCode(userCode);
			if(areas==null||areas.size()==0) {
				for (String month : monthList) {
					UnsatisfyDto dto = new UnsatisfyDto();
					dto.setEvaluateScore("0.00");
					dto.setYearMonth(month);
					unsatisfyInfos.add(dto);
				}
				return unsatisfyInfos;
			}
		}
		// 全国(多区域)
		if (!StringUtils.hasText(area) && !StringUtils.hasText(project)) {
			if ("a2".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA2(areas, null, monthList);
			}
			if ("a41".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA41(areas, null, monthList);
			}
			if ("a42".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA42(areas, null, monthList);
			}
			if ("a43".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA43(areas, null, monthList);
			}
			if ("a44".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA44(areas, null, monthList);
			}
			if ("a51".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA51(areas, null, monthList);
			}
			if ("a52".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA52(areas, null, monthList);
			}
			if ("a53".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA53(areas, null, monthList);
			}
			if ("a54".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA54(areas, null, monthList);
			}
			if ("a61".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA61(areas, null, monthList);
			}
			if ("a62".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA62(areas, null, monthList);
			}
			if ("a71".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA71(areas, null, monthList);
			}
			if ("a72".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA72(areas, null, monthList);
			}
			if ("a73".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA73(areas, null, monthList);
			}
		}
		// 区域
		if (StringUtils.hasText(area) && !StringUtils.hasText(project)) {
			List<String> areas2 = new ArrayList<String>();
			areas2.add(area);
			if ("a2".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA2(areas2, null, monthList);
			}
			if ("a41".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA41(areas2, null, monthList);
			}
			if ("a42".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA42(areas2, null, monthList);
			}
			if ("a43".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA43(areas2, null, monthList);
			}
			if ("a44".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA44(areas2, null, monthList);
			}
			if ("a51".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA51(areas2, null, monthList);
			}
			if ("a52".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA52(areas2, null, monthList);
			}
			if ("a53".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA53(areas2, null, monthList);
			}
			if ("a54".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA54(areas2, null, monthList);
			}
			if ("a61".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA61(areas2, null, monthList);
			}
			if ("a62".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA62(areas2, null, monthList);
			}
			if ("a71".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA71(areas2, null, monthList);
			}
			if ("a72".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA72(areas2, null, monthList);
			}
			if ("a73".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA73(areas2, null, monthList);
			}
		}
		// 项目
		if (StringUtils.hasText(area) && StringUtils.hasText(project)) {
			List<String> areas3 = new ArrayList<String>();
			areas3.add(area);
			if ("a2".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA2(areas3, project, monthList);
			}
			if ("a41".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA41(areas3, project, monthList);
			}
			if ("a42".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA42(areas3, project, monthList);
			}
			if ("a43".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA43(areas3, project, monthList);
			}
			if ("a44".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA44(areas3, project, monthList);
			}
			if ("a51".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA51(areas3, project, monthList);
			}
			if ("a52".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA52(areas3, project, monthList);
			}
			if ("a53".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA53(areas3, project, monthList);
			}
			if ("a54".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA54(areas3, project, monthList);
			}
			if ("a61".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA61(areas3, project, monthList);
			}
			if ("a62".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA62(areas3, project, monthList);
			}
			if ("a71".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA71(areas3, project, monthList);
			}
			if ("a72".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA72(areas3, project, monthList);
			}
			if ("a73".equals(type)) {
				unsatisfyInfos = bigholderSatisfyMapper.trendA73(areas3, project, monthList);
			}
		}
		for (String month : monthList) {
			boolean monthExistFlag = false;
			for (UnsatisfyDto unsatisfyDto : unsatisfyInfos) {
				if (month.equals(unsatisfyDto.getYearMonth())) {
					monthExistFlag = true;
					break;
				}
			}
			if (monthExistFlag == false) {
				UnsatisfyDto dto = new UnsatisfyDto();
				dto.setEvaluateScore("0.00");
				dto.setYearMonth(month);
				unsatisfyInfos.add(dto);
			}
		}
		Collections.sort(unsatisfyInfos,  new Comparator<UnsatisfyDto>() {
			@Override
			public int compare(UnsatisfyDto o1, UnsatisfyDto o2) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
				int mark = -1;
				Date date1 = null;
				Date date2 = null;
				try {
					date1 = sdf.parse(o1.getYearMonth());
					date2 = sdf.parse(o2.getYearMonth());
				} catch (ParseException e) {
					e.printStackTrace();
				}
				if(date1.getTime()>date2.getTime()) {
					mark = 1;
				}
				if(o1.getYearMonth().equals(o2.getYearMonth())) {
					mark = 0;
				}
				return mark;
			}
		});
		return unsatisfyInfos;
	}
	
	@Override
	public String isExistExceptionArea() {
		Map<String, String> areaMap = new HashMap<>();
		List<BigholderRightManager> areas = getAreas();
		if (areas != null && areas.size() > 0) {
			for (BigholderRightManager bigholderRightManager : areas) {
				String area = bigholderRightManager.getArea();
				String areaId = bigholderRightManager.getAreaId();
				areaMap.put(area, areaId);
			}
		}
		List<String> estateAreas = bigholderSatisfyMapper.getEstateAreas();
		for (String estateArea : estateAreas) {
			if(!StringUtils.hasText(areaMap.get(estateArea))) {
				return "0";//异常
			}
		}
		return "1";//正常
	}

	/**
	 * 判断是否为总部管理员
	 * 
	 * @param userCode
	 * @return
	 */
	public boolean isTotalAdmin(String userCode) {
		List<String> roleCodes = bigholderSatisfyMapper.getRoleByUserCode(userCode);
		boolean isTotalAdmin = false;
		if (roleCodes != null && roleCodes.size() > 0) {
			for (String roleCode : roleCodes) {
				if (TOTAL_ADMIN.equals(roleCode)) {
					isTotalAdmin = true;
					break;
				}
			}
		}
		return isTotalAdmin;
	}

	/**
	 * 按区域 ,月份计算满意度
	 */
//	public List<SatisfyInfo> calculateSatisfyInfosByArea(List<String> monthList, String area) {
//		List<String> areas = new ArrayList<String>();
//		areas.add(area);
//		List<SatisfyInfo> list = bigholderSatisfyMapper.satisfyInfosByMonth(monthList, areas);
//		if (list != null && list.size() > 0) {
//			return list;
//		} else {
//			List<SatisfyInfo> list2 = new ArrayList<SatisfyInfo>();
//			list2.add(new SatisfyInfo());
//			return list2;
//		}
//	}

	/**
	 * 按项目 ,月份计算满意度
	 */
//	public List<SatisfyInfo> calculateSatisfyInfosByProject(List<String> monthList, String area, String project) {
//		List<String> areas = new ArrayList<String>();
//		areas.add(area);
//		List<SatisfyInfo> list = bigholderSatisfyMapper.projectSatisfyByMonth(monthList, areas, project);
//		if (list != null && list.size() > 0) {
//			return list;
//		} else {
//			List<SatisfyInfo> list2 = new ArrayList<SatisfyInfo>();
//			list2.add(new SatisfyInfo());
//			return list2;
//		}
//	}

	/**
	 * 获取起始结束日期 所有月份
	 * 
	 * @param minDate
	 * @param maxDate
	 * @return
	 */
	private List<String> getMonthBetween(String minDate, String maxDate) {
		ArrayList<String> monthList = new ArrayList<String>();
		// 格式化为年月
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		Calendar min = Calendar.getInstance();
		Calendar max = Calendar.getInstance();
		try {
			min.setTime(sdf.parse(minDate));
			min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);
			max.setTime(sdf.parse(maxDate));
			max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		Calendar curr = min;
		while (curr.before(max)) {
			monthList.add(sdf.format(curr.getTime()));
			curr.add(Calendar.MONTH, 1);
		}
		return monthList;
	}

	/**
	 * 根据年月判断季度
	 * 
	 * @param month
	 * @return
	 */
	private String getQuarterByMonth(String yearMonth) {
		String month = yearMonth.split("-")[1];
		switch (month) {
		case "01":
		case "02":
		case "03":
			return "第1季度";
		case "04":
		case "05":
		case "06":
			return "第2季度";
		case "07":
		case "08":
		case "09":
			return "第3季度";
		default:
			return "第4季度";
		}
	}
	}
