package com.kingdee.shr.customer.gtiit.rpt;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.ui.ModelMap;
import com.kingdee.shr.base.syssetting.web.json.JSONUtils;
import com.alibaba.fastjson.JSON;
//import com.alibaba.fastjson.JSONObject;
import org.json.JSONObject;

//import com.alibaba.fastjson.JSON;
//import com.alibaba.fastjson.JSONObject;
//import com.alibaba.fastjson.JSON;
import com.cloudera.impala.jdbc4.internal.apache.log4j.Logger;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.base.syssetting.context.SHRContext;
import com.kingdee.shr.base.syssetting.exception.SHRWebException;
import com.kingdee.shr.base.syssetting.web.handler.ListHandler;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
//import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFCellStyle;
//import org.apache.poi.hssf.usermodel.HSSFRow;
//import org.apache.poi.hssf.usermodel.HSSFSheet;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;

// 员工花名册
public class PersonRosterListHandler extends ListHandler {

	Context ctx = SHRContext.getInstance().getContext();
	private static Logger logger1 = Logger.getLogger("com.kingdee.shr.customer.gtiit.rpt.PersonRosterListHandler");
	private int totalCount;

	public PersonRosterListHandler() {
	}

	public String getGridColModelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws SHRWebException, BOSException, SQLException {
		List<String> colNames = this.getcolNamesAction();
		List<Map<String, Object>> colModel = this.getcolModelAction();
		LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
		map.put("colNames", colNames);
		map.put("colModel", colModel);
		JSONUtils.writeJson(response, map);
		return null;
	}

	public void getGridDataAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException {
		int rows = Integer.parseInt(request.getParameter("rows"));
		int page = Integer.parseInt(request.getParameter("page"));
		// 获取过滤条件
		String fastFilterItems = request.getParameter("fastFilterItems");
		JSONObject tofastFilterItems = new JSONObject(fastFilterItems);
		//JSONObject tofastFilterItems = JSON.parseObject(fastFilterItems);

		// 获取人员
		JSONObject personJson = tofastFilterItems.getJSONObject("person");
		String personid = personJson.getString("values");
		// 获取人员类别
		JSONObject personTypeJson = tofastFilterItems.getJSONObject("personType");
		String personTypeId = personTypeJson.getString("values");
		// 获取状态
		JSONObject statusJson = tofastFilterItems.getJSONObject("status");
		String status = statusJson.getString("values");
		// 入职时间段
		JSONObject periodEmploymentJson = tofastFilterItems.getJSONObject("periodEmployment");
		String pervalues = periodEmploymentJson.getString("values");
		String startDate = null;
		String endDate = null;
		if (pervalues != null && !pervalues.equals("")) {
			JSONObject periodEmploymentValues = periodEmploymentJson.getJSONObject("values");
			startDate = periodEmploymentValues.getString("startDate");
			endDate = periodEmploymentValues.getString("endDate");
		}
		// 离职时间段
		JSONObject periodResignationJson = tofastFilterItems.getJSONObject("periodResignation");
		String resValues = periodResignationJson.getString("values");
		String resStartDate = null;
		String resEndDate = null;
		if (resValues != null && !resValues.equals("")) {
			JSONObject periodResignationValues = periodResignationJson.getJSONObject("values");
			resStartDate = periodResignationValues.getString("startDate");
			resEndDate = periodResignationValues.getString("endDate");
		}
		// 截止时间
		JSONObject deadlineJson = tofastFilterItems.getJSONObject("deadline");
		String deadValues = deadlineJson.getString("values");
		String dataeTime = null;
		if (deadValues != null && !deadValues.equals("")) {
			JSONObject deadVluesJson = deadlineJson.getJSONObject("values");
			dataeTime = deadVluesJson.getString("date");
		}
		List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
		int num = 0;
		IRowSet rs = DbUtil.executeQuery(this.ctx,
				this.getSql(personid, personTypeId, status, startDate, endDate, resStartDate, resEndDate, dataeTime));
         //序列号
		int serial=0;
		while (rs.next()) {
			++num;
			if (num >= rows * (page - 1) + 1 && num <= rows * page) {
				serial++;
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("No", Integer.toString(serial));
				map.put("PERSONNUMBER", rs.getString("PERSONNUMBER"));
				map.put("PERSONNUMBER", rs.getString("PERSONNUMBER"));
				map.put("LASTNAME", rs.getString("LASTNAME"));
				map.put("MiddleName", rs.getString("MiddleName"));
				map.put("FirstName", rs.getString("FirstName"));
				map.put("LocalName", rs.getString("LocalName"));
				if(StringUtils.isNotBlank(rs.getString("DisplayName"))) {
					map.put("DisplayName",rs.getString("DisplayName"));
				}else {
					map.put("DisplayName","");
				}
				map.put("Department", rs.getString("Department"));
				if(StringUtils.isNotBlank(rs.getString("Dept"))) {
					map.put("Dept",rs.getString("Dept"));
				}else {
					map.put("Dept","");
				}
				map.put("Job", rs.getString("Job"));
				map.put("Level", rs.getString("Level"));
				map.put("Position", rs.getString("Position"));
				map.put("PositionCode", rs.getString("PositionCode"));
				map.put("WorkCategory", rs.getString("WorkCategory"));
				map.put("PersonType", rs.getString("PersonType"));
				map.put("FULLTIMEORPARTTIME", rs.getString("FULLTIMEORPARTTIME"));
				map.put("LINEMANAGER", rs.getString("LINEMANAGER"));
				map.put("LINEMANAGERNAME", rs.getString("LINEMANAGERNAME"));
				if (rs.getString("StartTime") != null && !("").equals(rs.getString("StartTime"))) {
					map.put("StartTime", rs.getString("StartTime").substring(0, 10));
				} else {
					map.put("StartTime", null);
				}
				if (rs.getString("EndTime") != null && !("").equals(rs.getString("EndTime"))) {

					map.put("EndTime", rs.getString("EndTime").substring(0, 10));
				} else {
					map.put("EndTime", null);
				}
				if (rs.getString("HireDate") != null && !("").equals(rs.getString("HireDate"))) {

					map.put("HireDate", rs.getString("HireDate").substring(0, 10));
				} else {
					map.put("HireDate", null);
				}

				map.put("ProbationPeriod", rs.getString("ProbationPeriod"));
				map.put("ProbationPeriodUnit", rs.getString("ProbationPeriodUnit"));
				if (rs.getString("ProbationEndDate") != null && !("").equals(rs.getString("ProbationEndDate"))) {
					map.put("ProbationEndDate", rs.getString("ProbationEndDate").substring(0, 10));
				} else {
					map.put("ProbationEndDate", null);
				}
				map.put("WorkPhone", rs.getString("WorkPhone"));
				map.put("CellPhone", rs.getString("CellPhone"));
				map.put("PRIMARYEMAIL", rs.getString("PRIMARYEMAIL"));
				map.put("Email", rs.getString("Email"));
				map.put("Nationality", rs.getString("Nationality"));
				// 日期只要取年月日
				if (rs.getString("WORKPERMITISSUEDATE") != null && !("").equals(rs.getString("WORKPERMITISSUEDATE"))) {
					map.put("WORKPERMITISSUEDATE", rs.getString("WORKPERMITISSUEDATE").substring(0, 10));
				} else {
					map.put("WORKPERMITISSUEDATE", null);
				}
				if (rs.getString("WORKPERMITEXPIRATIONDATE") != null
						&& !("").equals(rs.getString("WORKPERMITEXPIRATIONDATE"))) {
					map.put("WORKPERMITEXPIRATIONDATE", rs.getString("WORKPERMITEXPIRATIONDATE").substring(0, 10));
				} else {
					map.put("WORKPERMITEXPIRATIONDATE", null);
				}
				if (rs.getString("RESIDENCEPERMITISSUEDATE") != null
						&& !("").equals(rs.getString("RESIDENCEPERMITISSUEDATE"))) {
					map.put("RESIDENCEPERMITISSUEDATE", rs.getString("RESIDENCEPERMITISSUEDATE").substring(0, 10));
				} else {
					map.put("RESIDENCEPERMITISSUEDATE", null);
				}
				if (rs.getString("RESIDENCEPERMITEXPIRATIONDATE") != null
						&& !("").equals(rs.getString("RESIDENCEPERMITEXPIRATIONDATE"))) {
					map.put("RESIDENCEPERMITEXPIRATIONDATE",
							rs.getString("RESIDENCEPERMITEXPIRATIONDATE").substring(0, 10));
				} else {
					map.put("RESIDENCEPERMITEXPIRATIONDATE", null);
				}
				map.put("IDCARDNOORPASSPORTNO", rs.getString("IDCARDNOORPASSPORTNO"));
				if (rs.getString("IDCARDCOMMENCINGDATE") != null
						&& !("").equals(rs.getString("IDCARDCOMMENCINGDATE"))) {

					map.put("IDCARDCOMMENCINGDATE", rs.getString("IDCARDCOMMENCINGDATE").substring(0, 10));
				} else {
					map.put("IDCARDCOMMENCINGDATE", null);
				}
				if (rs.getString("IDCARDEXPIREDDATE") != null && !("").equals(rs.getString("IDCARDEXPIREDDATE"))) {

					map.put("IDCARDEXPIREDDATE", rs.getString("IDCARDEXPIREDDATE").substring(0, 10));
				} else {
					map.put("IDCARDEXPIREDDATE", null);
				}
				map.put("FIDCardAddress", rs.getString("FIDCardAddress"));
				map.put("CURRENTADDRESS", rs.getString("CURRENTADDRESS"));
				map.put("Sex", rs.getString("Sex"));
				if (rs.getString("Birthdate") != null && !("").equals(rs.getString("Birthdate"))) {

					map.put("Birthdate", rs.getString("Birthdate").substring(0, 10));
				} else {
					map.put("Birthdate", null);
				}
				map.put("Ethnicity", rs.getString("Ethnicity"));
				map.put("Hukoutype", rs.getString("Hukoutype"));
				if (rs.getString("HOUSEHOLDREGISTEREDDATE") != null
						&& !("").equals(rs.getString("HOUSEHOLDREGISTEREDDATE"))) {

					map.put("HouseholdRegistereddate", rs.getString("HOUSEHOLDREGISTEREDDATE").substring(0, 10));
				} else {
					map.put("HouseholdRegistereddate", null);
				}
				// map.put("HouseholdRegistereddate", rs.getString("HouseholdRegistereddate"));
				map.put("Nativeplace", rs.getString("Nativeplace"));
				map.put("POLITICASTATUS", rs.getString("POLITICASTATUS"));
				map.put("Maritalstatus", rs.getString("Maritalstatus"));
				map.put("Zipcode", rs.getString("Zipcode"));
				map.put("GRADUATEDSCHOOLOFHIGHESTACADEMICDEGREE",
						rs.getString("GRADUATEDSCHOOLOFHIGHESTACADEMICDEGREE"));
				map.put("HIGHESTACADEMICDEGREE", rs.getString("HIGHESTACADEMICDEGREE"));
				map.put("HIGHESTQUALIFICATIONOFFULLTIMEEDUCATION",
						rs.getString("HIGHESTQUALIFICATIONOFFULLTIMEEDUCATION"));
				map.put("MAJOROFFULLTIMEEDUCATION", rs.getString("MAJOROFFULLTIMEEDUCATION"));
				if (rs.getString("GRADUATEDDATE") != null && !("").equals(rs.getString("GRADUATEDDATE"))) {

					map.put("GRADUATEDDATE", rs.getString("GRADUATEDDATE").substring(0, 10));
				} else {
					map.put("GRADUATEDDATE", null);
				}
				map.put("GRADUATESCHOOLOFHIGHESTDEGREEOFPARTTIMEEDUCATION",
						rs.getString("GRADUATESCHOOLOFHIGHESTDEGREEOFPARTTIMEEDUCATION"));
				map.put("HIGHESTDEGREEOFPARTTIMEEDUCATION", rs.getString("HIGHESTDEGREEOFPARTTIMEEDUCATION"));
				map.put("HIGHESTQUALIFICATIONOFPARTTIMEEDUCATION",
						rs.getString("HIGHESTQUALIFICATIONOFPARTTIMEEDUCATION"));
				map.put("MAJOROFPARTTIMEEDUCATION", rs.getString("MAJOROFPARTTIMEEDUCATION"));
				map.put("EMERGENCYCONTACT", rs.getString("EMERGENCYCONTACT"));
				map.put("TELEPHONEOFEMERGENCY", rs.getString("TELEPHONEOFEMERGENCY"));
				map.put("RELATIONSHIP", rs.getString("RELATIONSHIP"));
				if (rs.getString("TERMINATIONDATE") != null && !("").equals(rs.getString("TERMINATIONDATE"))) {

					map.put("TERMINATIONDATE", rs.getString("TERMINATIONDATE").substring(0, 10));
				} else {
					map.put("TERMINATIONDATE", null);
				}
				map.put("TERMINATIONREASON", rs.getString("TERMINATIONREASON"));
				maplist.add(map);
			}
		}
		this.totalCount = num;
		Map<String, Object> gridDataMap = new LinkedHashMap<String, Object>();
		if (this.totalCount % rows == 0) {
			gridDataMap.put("total", this.totalCount / rows);
		} else {
			gridDataMap.put("total", this.totalCount / rows + 1);
		}
		gridDataMap.put("page", page);
		gridDataMap.put("records", this.totalCount);
		gridDataMap.put("rows", maplist);
		JSONUtils.writeJson(response, gridDataMap);
	}

	public ArrayList<String> getcolNamesAction() throws SHRWebException {
		ArrayList<String> list = new ArrayList<String>();
		list.add("No.序号");
		list.add("Person Number 工号");
		list.add("Last Name 姓");
		list.add("Middle Name 中间名");
		list.add("First Name 名");
		list.add("Local Name 本地名称");
		list.add("Display Name 展示名称");
		list.add("Department 部门");
		list.add("Dept. 部门简称");
		list.add("Job 职务");
		list.add("Level(L1-L15)");
		list.add("Position 职位");
		list.add("Position Code");
		list.add("Work Category 员工类别");
		list.add("Person Type 人员类型");
		list.add("Full Time or Part Time 全职或兼职");
		list.add("Line Manager 直线经理 （工号）");
		list.add("Line Manager 直线经理 （姓名）");
		list.add("Start Time");
		list.add("End Time");
		list.add("Hire Date 入职日期");
		list.add("Probation Period 试用期");
		list.add("Probation Period Unit 试用期单位");
		list.add("Probation End Date 试用期结束日期");
		list.add("Work Phone 工作电话");
		list.add("Cell Phone 手机");
		list.add("Email (Primary)工作邮箱");
		list.add("Email 邮箱");
		list.add("Nationality 国籍");
		list.add("Work Permit Issue Date 工作许可签发日期");
		list.add("Work Permit Expiration Date 工作许可失效日期");
		list.add("Residence Permit Issue Date 居住许可签发日期");
		list.add("Residence Permit Expiration Date 居住许可失效日期");
		list.add("ID card no. or Passport no. 身份证号/护照号");
		list.add("ID card commencing date 身份证开始日期");
		list.add("ID card expired date 身份证结束日期");
		list.add("ID card address 身份证地址");
		list.add("Current address 现在通讯地址");
		list.add("Sex 性别");
		list.add("Birth date 出生日期");
		list.add("Ethnicity 民族");
		list.add("Hukou type 户籍类型");
		list.add("Household Registered  date 户口登记日期");
		list.add("Native place 籍贯");
		list.add("Political status 政治面貌");
		list.add("Marital status 婚姻状况");
		list.add("Zip code 邮政编码");
		list.add("Graduated School of Highest Academic Degree (full-time) 全日制最高学历毕业院校");
		list.add("Highest Academic Degree (full-time) 全日制最高学历");
		list.add("Highest Qualification of Full-time Education 全日制最高学历所获学位");
		list.add("Major of full time education 全日制最高学历所学专业");
		list.add("Graduated date 毕业日期");
		list.add("Graduate School of Highest Degree of Part-time Education 非全日制最高学历毕业院校");
		list.add("Highest Degree of Part-time Education 非全日制最高学历");
		list.add("Highest Qualification of Part-time education 非全日制最高学历所获学位");
		list.add("Major of Part-time education 非全日制最高学历所学专业");
		list.add("emergency contact 紧急联系人姓名");
		list.add("telephone of emergency contact 紧急联系人电话");
		list.add("relationship 所属关系");
		list.add("Termination Date 离职日期");
		list.add("Termination Reason 离职原因");

		return list;
	}

	public List<Map<String, Object>> getcolModelAction() {
		List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
		modelColList.add(this.buildColModelMap("No", "No.序号", 40));
		modelColList.add(this.buildColModelMap("PERSONNUMBER", "Person Number 工号", 70));
		modelColList.add(this.buildColModelMap("LASTNAME", "Last Name 姓", 70));
		modelColList.add(this.buildColModelMap("MiddleName", "Middle Name 中间名", 70));
		modelColList.add(this.buildColModelMap("FirstName", "First Name 名", 70));
		modelColList.add(this.buildColModelMap("LocalName", "Local Name 本地名称", 70));
		modelColList.add(this.buildColModelMap("DisplayName", "Display Name 展示名称", 70));
		modelColList.add(this.buildColModelMap("Department", "Department 部门", 70));
		modelColList.add(this.buildColModelMap("Dept", "Dept. 部门简称", 70));
		modelColList.add(this.buildColModelMap("Job", "Job 职务", 70));
		modelColList.add(this.buildColModelMap("Level", "Level(L1-L15)", 70));
		modelColList.add(this.buildColModelMap("Position", "Position 职位", 70));
		modelColList.add(this.buildColModelMap("PositionCode", "position Code", 70));
		modelColList.add(this.buildColModelMap("WorkCategory", "Work Category 员工类别", 70));
		modelColList.add(this.buildColModelMap("PersonType", "Person Type 人员类型", 70));
		modelColList.add(this.buildColModelMap("FULLTIMEORPARTTIME", "Full Time or Part Time 全职或兼职", 50));
		modelColList.add(this.buildColModelMap("LINEMANAGER", "Line Manager 直线经理 （工号）", 50));
		modelColList.add(this.buildColModelMap("LINEMANAGERNAME", "Line Manager 直线经理 （姓名）", 50));
		modelColList.add(this.buildColModelMap("StartTime", "Start Time ", 50));
		modelColList.add(this.buildColModelMap("EndTime", "End Time", 50));
		modelColList.add(this.buildColModelMap("HireDate", "Hire Date 入职日期", 50));
		modelColList.add(this.buildColModelMap("ProbationPeriod", "Probation Period 试用期", 50));
		modelColList.add(this.buildColModelMap("ProbationPeriodUnit", "Probation Period Unit 试用期单位", 50));
		modelColList.add(this.buildColModelMap("ProbationEndDate", "Probation End Date 试用期结束日期", 50));
		modelColList.add(this.buildColModelMap("WorkPhone", "Work Phone 工作电话", 50));
		modelColList.add(this.buildColModelMap("CellPhone", "Cell Phone 手机", 50));
		modelColList.add(this.buildColModelMap("PRIMARYEMAIL", "Email (Primary) 工作邮箱", 120));
		modelColList.add(this.buildColModelMap("Email", "Email 邮箱", 50));
		modelColList.add(this.buildColModelMap("Nationality", "Nationality 国籍", 50));
		modelColList.add(this.buildColModelMap("WORKPERMITISSUEDATE", "Work Permit Issue Date 工作许可签发日期", 120));
		modelColList.add(this.buildColModelMap("WORKPERMITEXPIRATIONDATE", "Work Permit Expiration Date 工作许可失效日期", 120));
		modelColList.add(this.buildColModelMap("RESIDENCEPERMITISSUEDATE", "Residence Permit Issue Date 居住许可签发日期", 120));
		modelColList
				.add(this.buildColModelMap("RESIDENCEPERMITEXPIRATIONDATE", "Residence Permit Expiration Date 居住许可失效日期", 120));
		modelColList.add(this.buildColModelMap("IDCARDNOORPASSPORTNO", "ID card no. or Passport no. 身份证号/护照号", 120));
		modelColList.add(this.buildColModelMap("IDCARDCOMMENCINGDATE", "ID card commencing date 身份证开始日期", 120));
		modelColList.add(this.buildColModelMap("IDCARDEXPIREDDATE", "ID card expired date 身份证结束日期", 120));
		modelColList.add(this.buildColModelMap("FIDCardAddress", "ID card address 身份证地址", 120));
		modelColList.add(this.buildColModelMap("CURRENTADDRESS", "Current address 现在通讯地址", 120));
		modelColList.add(this.buildColModelMap("Sex", "Sex 性别", 50));
		modelColList.add(this.buildColModelMap("Birthdate", "Birth date 出生日期", 100));
		modelColList.add(this.buildColModelMap("Ethnicity", "Ethnicity 民族", 50));
		modelColList.add(this.buildColModelMap("Hukoutype", "Hukou type 户籍类型", 50));
		modelColList.add(this.buildColModelMap("HouseholdRegistereddate", "Household Registered  date 户口登记日期", 50));
		modelColList.add(this.buildColModelMap("Nativeplace", "Native place 籍贯", 50));
		modelColList.add(this.buildColModelMap("POLITICASTATUS", "Political status 政治面貌", 50));
		modelColList.add(this.buildColModelMap("Maritalstatus", "Marital status 婚姻状况", 50));
		modelColList.add(this.buildColModelMap("Zipcode", "Zip code 邮政编码", 50));
		modelColList.add(this.buildColModelMap("GRADUATEDSCHOOLOFHIGHESTACADEMICDEGREE",
				"Graduated School of Highest Academic Degree (full-time) 全日制最高学历毕业院校", 200));
		modelColList.add(this.buildColModelMap("HIGHESTACADEMICDEGREE", "Highest Academic Degree (full-time) 全日制最高学历", 100));
		modelColList.add(this.buildColModelMap("HIGHESTQUALIFICATIONOFFULLTIMEEDUCATION",
				"Highest Qualification of Full-time Education 全日制最高学历所获学位", 200));
		modelColList.add(this.buildColModelMap("MAJOROFFULLTIMEEDUCATION", "Major of full time education 全日制最高学历所学专业", 120));
		modelColList.add(this.buildColModelMap("GRADUATEDDATE", "Graduated date 毕业日期", 50));
		modelColList.add(this.buildColModelMap("GRADUATESCHOOLOFHIGHESTDEGREEOFPARTTIMEEDUCATION",
				"Graduate School of Highest Degree of Part-time Education 非全日制最高学历毕业院校", 120));
		modelColList.add(this.buildColModelMap("HIGHESTDEGREEOFPARTTIMEEDUCATION",
				"Highest Degree of Part-time Education 非全日制最高学历", 120));
		modelColList.add(this.buildColModelMap("HIGHESTQUALIFICATIONOFPARTTIMEEDUCATION",
				"Highest Qualification of Part-time education 非全日制最高学历所获学位", 120));
		modelColList.add(this.buildColModelMap("MAJOROFPARTTIMEEDUCATION", "Major of Part-time education 非全日制最高学历所学专业", 50));
		modelColList.add(this.buildColModelMap("EMERGENCYCONTACT", "emergency contact 紧急联系人姓名", 50));
		modelColList.add(this.buildColModelMap("TELEPHONEOFEMERGENCY", "telephone of emergency contact 紧急联系人电话", 100));
		modelColList.add(this.buildColModelMap("RELATIONSHIP", "relationship 所属关系", 100));
		modelColList.add(this.buildColModelMap("TERMINATIONDATE", "Termination Date 离职日期", 100));
		modelColList.add(this.buildColModelMap("TERMINATIONREASON", "Termination Reason 离职原因", 100));
		return modelColList;
	}

	private Map<String, Object> buildColModelMap(String index, String label, int width) {
		return this.buildColModelMap(index, label, width, false, false, false);
	}

	private Map<String, Object> buildColModelMap(String index, String label, int width, boolean rowspan, boolean isKey,
			boolean isHedden) {
		Map<String, Object> gridIdMap = new LinkedHashMap<String, Object>();
		gridIdMap.put("index", index);
		gridIdMap.put("name", index);
		gridIdMap.put("label", label);
		gridIdMap.put("width", width);
		if (rowspan) {
			gridIdMap.put("rowspan", rowspan);
		}
		if (isKey) {
			gridIdMap.put("key", isKey);
		}
		if (isHedden) {
			gridIdMap.put("hidden", isHedden);
		}
		return gridIdMap;
	}

	public String getSql(String peronid, String personTypeId, String status, String employmentStartTime,
			String employmentEndTime, String resignationStartTime, String resignationEndTime, String deadline) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"SELECT a.fnumber as PersonNumber , a.CFSurname as LastName , a.CFMiddleNames as MiddleName , a.CFGivenName as FirstName , a.CFLocalName as LocalName ,");
		sql.append(" a.fname_l1 as DisplayName,c.fname_l1 as Department,c.fsimplename as Dept , e.fname_l1 as Job , f.Level , d.CFLongname_l1 as Position , d.fnumber as\r\n" + 
				" PositionCode ,g.fname_l1 as WorkCategory , case when blx.fname_l1 is not null then blx.fname_l1 else lsx.fname_l1  end as PersonType,\r\n" + 
				"case when qzjz.fname_l1 is not null then qzjz.fname_l1 else lsqzjz.fname_l1  end as FullTimeorPartTime , \r\n" + 
				"j.fnumber as LineManager ,\r\n" + 
				" j.fname_l2 as LineManagerName ,b.feffdt as StartTime , b.fleffdt as EndTime , k.FEnterDate as HireDate , k.FTryoutMonth as ProbationPeriod ,\r\n" + 
				" 'months' as ProbationPeriodUnit , k.FPlanFormalDate as ProbationEndDate , l.FOfficePhone as WorkPhone , l.FMobile as CellPhone , l.FEmail as PrimaryEmail\r\n" + 
				" , l.CFPrivateemail as Email , m.fname_l1 as Nationality ,v.FIssueDate as WorkPermitIssueDate, v.CFExpirationDate as WorkPermitExpirationDate,w.FIssueDate \r\n" + 
				"as ResidencePermitIssueDate, w.CFExpirationDate as ResidencePermitExpirationDate,\r\n" + 
				" case when a.FIDCardNO is  not null then a.FIDCardNO when a.FPassportNO is not null then a.FPassportNO  end as IDcardnoorPassportno, a.FIdCardBeginDate as IDcardcommencingdate,a.FIdCardEndDate as IDcardexpireddate,\r\n" + 
				" a.FIDCardAddress,\r\n" + 
				"l.FWORKPLACE as CurrentAddress , case when a.FGender = 1 then 'male' when  a.FGender = 2 then 'female' else '' end as Sex , a.FBirthday as BirthDate ,\r\n" + 
				"mz.FName_L1 as Ethnicity , hj.FName_L1 as HukouType , a.cfhrd as HouseholdRegisteredDate , a.FNativePlace_L2 as Nativeplace ,\r\n" + 
				"zm.FName_L1 as PoliticaStatus , hy.FName_L1 as MaritalStatus , l.FPostalcode as Zipcode,\r\n" + 
				"qrz.CFSchool as GraduatedSchoolofHighestAcademicDegree , qxl.FName_L1 as  HighestAcademicDegree , qxw.FName_L1 as HighestQualificationOfFulltimeEducation ,\r\n" + 
				" qrz.CFMajor as Majoroffulltimeeducation , qrz.CFGraduation as GraduatedDate ,\r\n" + 
				"fqrz.CFSchool as GraduateSchoolofHighestDegreeofParttimeEducation  ,\r\n" + 
				" fqxl.FName_L1  as  HighestDegreeofParttimeEducation ,  fqxw.FName_L1 as HighestQualificationofParttimeeducation , fqrz.CFMajor as MajorofParttimeeducation ,\r\n" + 
				"l.FLinkName as emergencyContact , l.FLinkTelNum as telephoneofemergency , l.CFRelationship as relationship,\r\n" + 
				" case when tui.fbizdate is not null then tui.fbizdate  when depart.fbizdate is not  null then depart.fbizdate else ygls.feffdt  end as TerminationDate,\r\n" + 
				"case when tt.FName_L1 is  not  null then tt.FName_L1 else  a.CFReason end as TerminationReason\r\n" + 
				" FROM t_bd_person a \r\n" + 
				"left join ( SELECT * FROM T_HR_EMPORGRELATION where FASSIGNTYPE  = 1 and FIsLatestInAday = 1  ) b on a.fid = b.FPERSONID\r\n" + 
				"left join T_BD_HRFolk mz on mz.fid=a.FFOLKID\r\n" + 
				"left join T_BD_HRPolitical zm on zm.fid=a.FPoliticalFaceID\r\n" + 
				"left join T_BD_HRWed hy on hy.fid=a.FWedID\r\n" + 
				"left join T_BD_Regpermresidence hj on hj.fid=a.FREGRESIDENCEID\r\n" + 
				"left join t_org_admin c on b.FAdminOrgID = c.FID \r\n" + 
				"left join t_org_position d on b.fpositionid = d.FID \r\n" + 
				"left join t_org_job e on d.FJOBID = e.fid");
		sql.append(" left join ( SELECT a.FPERSONID ,b.FNAME_l1 as Level FROM T_HR_EmpPostRank a left join t_hr_jobgrade b on a.FJOBGRADEID  = b.FID  where a.feffdt<="+"'"+deadline+"'"+" and a.fleffdt >="+"'"+deadline+"' ) f on a.fid = f.FPERSONID");
		sql.append(" left join CT_MP_WorkerCategory g on b.CFWorkercategoryID = g.fid \r\n" + 
				"left join CT_MP_Fullorpart h on a.CFFtorptID = h.fid\r\n" + 
				"left join T_HR_EmployeeClassify i on a.CFPersontypeID = i.FID \r\n" + 
				"left join t_bd_person j on b.CFLineManagerNameI = j.fid\r\n" + 
				"left join (select * from T_HR_EmpLaborRelationHis where FIsLatestInAday = 1  ) k on  a.fid = k.FPERSONID\r\n" + 
				"left join T_HR_PersonContactMethod l on a.fid = l.FPERSONID \r\n" + 
				"left join T_BD_Nationality m on a.FNationalityID = m.fid\r\n" + 
				"left join CT_MP_Fullperson qrz on qrz.FPersonID=a.fid\r\n" + 
				"left join  T_BD_HRDiploma qxl on qrz.CFBackgroundID= qxl.fid\r\n" + 
				"left join  T_BD_HRDegree qxw on qxw.fid=qrz.CFDegreeID\r\n" + 
				"left join CT_MP_Partperson fqrz on fqrz.FPersonID =a.fid\r\n" + 
				"left join T_BD_HRDiploma fqxl on fqrz.CFBackgroundID= fqxl.fid\r\n" + 
				"left join T_BD_HRDegree fqxw on fqxw.fid=fqrz.CFDegreeID\r\n" + 
				"left join T_HR_BDEmployeeType p on k.FLABORRELATIONSTATEID=p.fid\r\n" + 
				"left join  (select a.FCredentialsTypeNO,a.fid,a.fpersonid from T_HR_PersonCredentialsType a left join T_HR_CredentialsType b on a.FCredentialsTypeID=b.fid where b.FNUMBER ='CN01' )r on a.fid=r.fpersonid\r\n" + 
				"left join(SELECT a.fname_l1,c.fpersonid FROM T_HR_VariationReason a left join (\r\n" + 
				"SELECT a.* FROM T_HR_ResignBizBillEntry a right join \r\n" + 
				"(SELECT max(fbizdate)as fbizdate,fpersonid FROM T_HR_ResignBizBillEntry group by fpersonid)b on a.fpersonid=b.fpersonid and a.fbizdate=b.fbizdate ) c\r\n" + 
				"on a.fid=c.FVariationReasonID \r\n" + 
				" )tt on a.fid=tt.fpersonid\r\n" + 
				"\r\n" + 
				"\r\n" + 
				"left join ( SELECT DATEADD(DAY, -1,  max(a.feffdt)) as bizdate,a.fpersonid FROM T_HR_EmpOrgRelation  a left join T_HR_BDEmployeeType b on a.FEmployeeType=b.fid where  b.FinService in('2') group by a.fpersonid) lzrq on\r\n" + 
				"a.fid=lzrq.fpersonid\r\n" + 
				"\r\n" + 
				"left join( SELECT  a.CFExpirationDate,a.FCredentialsTypeNO,a.fpersonid ,a.FIssueDate FROM T_HR_PersonCredentialsType a \r\n" + 
				"INNER join( select max(CFExpirationDate)CFExpirationDate,a.fpersonid from T_HR_PersonCredentialsType a left join T_HR_CredentialsType b on a.FCredentialsTypeID=b.fid where b.FNUMBER ='CWP'  group by a.fpersonid \r\n" + 
				")  b on a.fpersonid=b.fpersonid and a.CFExpirationDate =b.CFExpirationDate inner join T_HR_CredentialsType c  on a.FCredentialsTypeID=c.fid  where  c.FNUMBER ='CWP'\r\n" + 
				" )v on a.fid=v.FPersonID\r\n" + 
				"\r\n" + 
				"left join(SELECT  a.CFExpirationDate,a.FCredentialsTypeNO,a.fpersonid ,a.FIssueDate FROM T_HR_PersonCredentialsType a \r\n" + 
				"INNER join( select max(CFExpirationDate)CFExpirationDate,a.fpersonid from T_HR_PersonCredentialsType a left join T_HR_CredentialsType b on a.FCredentialsTypeID=b.fid where b.FNUMBER ='CFRP'  group by a.fpersonid \r\n" + 
				")  b on a.fpersonid=b.fpersonid and a.CFExpirationDate =b.CFExpirationDate inner join T_HR_CredentialsType c  on a.FCredentialsTypeID=c.fid  where  c.FNUMBER ='CFRP'\r\n" + 
				")w on a.fid=w.FPersonID\r\n" + 
				"left join (SELECT max(fbizdate)as fbizdate,FPERSONID  FROM T_HR_RetireBizBillEntry where fbizdate is not null  group by FPERSONID )tui on a.fid=tui.FPERSONID\r\n" + 
				"left join(SELECT max(fbizdate)as fbizdate,fpersonid FROM T_HR_ResignBizBillEntry  group by fpersonid)depart on a.fid=depart.fpersonid \r\n" + 
				"left join(\r\n" + 
				"SELECT DATEADD(DAY, -1,  max(feffdt)) as feffdt,fpersonid FROM  t_hr_emplaborrelationhis  where flaborrelationstateid=(\r\n" + 
				"SELECT fid FROM T_HR_BDEmployeeType where FNUMBER ='S09')  group by fpersonid )ygls on a.fid=ygls.fpersonid");
		sql.append(" left join T_HR_EmployeeClassify blx on b.CFPersontypeID=blx.fid\r\n" + 
				"left join (SELECT * FROM T_BD_PersonHis where FEFFDT<="+"'"+deadline+"'"+" and FLEFFDT >="+"'"+deadline+"')ryls on ryls.FHistoryRelateID=a.fid\r\n" + 
				"left join  T_HR_EmployeeClassify lsx on lsx.fid=ryls.CFPersontypeID "
				+ "left join CT_MP_Fullorpart qzjz on b.CFFtorptID=qzjz.fid\r\n" + 
				" left join CT_MP_Fullorpart lsqzjz on lsqzjz.fid= ryls.CFFtorptID");
		// 传了多个人员需要拆开拼接sql
		StringBuffer personsql = new StringBuffer();
		String[] personidArry = new String[0];
		if (!peronid.isEmpty()) {

			personidArry = peronid.split(",");
		}
		// 员工类别
		StringBuffer typesql = new StringBuffer();
		String[] typeArry = new String[0];

		// 状态的sql
		StringBuffer statussql = new StringBuffer();
		statussql.append(" and p.FINSERVICE = ");
		statussql.append("'");
		statussql.append(status);
		statussql.append("'");
		// 入职时间段的sql
		StringBuffer empostartsql = new StringBuffer();
		empostartsql.append(" and k.FEnterDate >= ");
		empostartsql.append("'");
		empostartsql.append(employmentStartTime);
		empostartsql.append("'");
		StringBuffer empoendsql = new StringBuffer();
		empoendsql.append(" and k.FEnterDate <= ");
		empoendsql.append("'");
		empoendsql.append(employmentEndTime);
		empoendsql.append("'");
		// 离职时间段的sql
		StringBuffer resstartsql = new StringBuffer();
		resstartsql.append(" and a.CFDimissiondate >= ");
		resstartsql.append("'");
		resstartsql.append(resignationStartTime);
		resstartsql.append("'");
		StringBuffer resendsql = new StringBuffer();
		// resendsql.append(" and t.FBizTime <= ");
		resendsql.append(" and a.CFDimissiondate <= ");
		resendsql.append("'");
		resendsql.append(resignationEndTime);
		resendsql.append("'");
		// 截止时间段的sql // 如果状态是离职 截止时间就和离职时间比较 如果是在职就和在职时间比较
		StringBuffer deadlinesql = new StringBuffer();
		deadlinesql.append(" and b.fleffdt >= ");
		deadlinesql.append("'");
		deadlinesql.append(deadline);
		deadlinesql.append("'");
		deadlinesql.append(" and b.feffdt <=  ");
		deadlinesql.append("'");
		deadlinesql.append(deadline);
		deadlinesql.append("'");
		// 用工关系也开始结束时间也要和截止时间一样
		StringBuffer yggx = new StringBuffer();
		yggx.append(" and k.fleffdt >= ");
		yggx.append("'");
		yggx.append(deadline);
		yggx.append("'");
		yggx.append(" and k.feffdt <=  ");
		yggx.append("'");
		yggx.append(deadline);
		yggx.append("'");

		if (!personTypeId.isEmpty()) {

			typeArry = personTypeId.split(",");
		}
		if (personidArry.length > 0) {
			personsql.append(" where a.fid in ");
			personsql.append("(");
			for (String pid : personidArry) {
				personsql.append("'");
				personsql.append(pid);
				personsql.append("'");
				personsql.append(",");
			}
			// 删除最后一个逗号
			personsql.deleteCharAt(personsql.length() - 1);
			personsql.append(")");
			sql.append(personsql);
			// 和员工类别拼接
			if (typeArry.length > 0) {
				typesql.append(" and g.fid in");
				typesql.append("(");
				for (String type : typeArry) {
					typesql.append("'");
					typesql.append(type);
					typesql.append("'");
					typesql.append(",");
				}
				// 删除最后一个逗号
				typesql.deleteCharAt(typesql.length() - 1);
				typesql.append(")");
				sql.append(typesql);

			}
			// 和状态拼接
			if (status != null && !status.equals("")) {
				sql.append(statussql);
			}
			// 和入职时间段拼接
			if (employmentStartTime != null && !employmentStartTime.equals("")) {
				sql.append(empostartsql);
			}
			if (employmentEndTime != null && !employmentEndTime.equals("")) {
				sql.append(empoendsql);
			}
			// 和离职时间段拼接
			if (resignationStartTime != null && !resignationStartTime.equals("")) {
				sql.append(resstartsql);
			}
			if (resignationEndTime != null && !resignationEndTime.equals("")) {
				sql.append(resendsql);
			}
			// 和截止时间拼接
			if (deadline != null && !deadline.equals("")) {
				sql.append(deadlinesql);
				sql.append(yggx);
			}

		} else if (typeArry.length > 0) {
			typesql.append(" where g.fid in ");
			typesql.append("(");
			for (String type : typeArry) {
				typesql.append("'");
				typesql.append(type);
				typesql.append("'");
				typesql.append(",");
			}
			// 删除最后一个逗号
			typesql.deleteCharAt(typesql.length() - 1);
			typesql.append(")");
			sql.append(typesql);
			// 和状态拼接
			if (status != null && !status.equals("")) {
				sql.append(statussql);
				//sql.append(statussql);
			}
			// 和入职时间段拼接
			if (employmentStartTime != null && !employmentStartTime.equals("")) {
				sql.append(empostartsql);
			}
			if (employmentEndTime != null && !employmentEndTime.equals("")) {
				sql.append(empoendsql);
			}
			// 和离职时间段拼接
			if (resignationStartTime != null && !resignationStartTime.equals("")) {
				sql.append(resstartsql);
			}
			if (resignationEndTime != null && !resignationEndTime.equals("")) {
				sql.append(resendsql);
			}
			// 和截止时间拼接
			if (deadline != null && !deadline.equals("")) {
				sql.append(deadlinesql);
				sql.append(yggx);
			}

		} else if (status != null && !status.equals("")) {
			sql.append(" where p.FINSERVICE =");
			sql.append("'");
			sql.append(status);
			sql.append("'");
			// 和入职时间段拼接
			if (employmentStartTime != null && !employmentStartTime.equals("")) {
				sql.append(empostartsql);
			}
			if (employmentEndTime != null && !employmentEndTime.equals("")) {
				sql.append(empoendsql);
			}
			// 和离职时间段拼接
			if (resignationStartTime != null && !resignationStartTime.equals("")) {
				sql.append(resstartsql);
			}
			if (resignationEndTime != null && !resignationEndTime.equals("")) {
				sql.append(resendsql);
			}

			// 和截止时间拼接
			if (deadline != null && !deadline.equals("")) {
				sql.append(deadlinesql);
				sql.append(yggx);
			}
		} else if (employmentStartTime != null && !employmentStartTime.equals("")) {
			sql.append(" where k.FEnterDate >= ");
			sql.append("'");
			sql.append(employmentStartTime);
			sql.append("'");
			sql.append(empoendsql);
			// 和截止时间拼接
			if (deadline != null && !deadline.equals("")) {
				sql.append(deadlinesql);
				sql.append(yggx);
			}

		} else if (resignationStartTime != null && !resignationStartTime.equals("")) {
			sql.append(" where a.CFDimissiondate >= ");
			sql.append("'");
			sql.append(resignationStartTime);
			sql.append("'");
			sql.append(resendsql);
			// 和截止时间拼接
			if (deadline != null && !deadline.equals("")) {
				sql.append(deadlinesql);
				sql.append(yggx);
			}

		} else if (deadline != null && !deadline.equals("")) {
			sql.append(" where b.fleffdt >= ");
			sql.append("'");
			sql.append(deadline);
			sql.append("'");
			sql.append(" and b.feffdt <=  ");
			sql.append("'");
			sql.append(deadline);
			sql.append("'");
			sql.append(yggx);
		}
		System.out.print("长sql"+sql.toString());
		logger1.error("长长的sql" + sql);
		return sql.toString();

	}

	// 导出数据
	public String toExcelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException {

		// 获取过滤条件
		String fastFilterItems = request.getParameter("fastFilterItems");
		JSONObject tofastFilterItems = new JSONObject(fastFilterItems);
		//JSONObject tofastFilterItems = JSON.parseObject(fastFilterItems);

		// 获取人员
		JSONObject personJson = tofastFilterItems.getJSONObject("person");
		String personid = personJson.getString("values");
		// 获取人员类别
		JSONObject personTypeJson = tofastFilterItems.getJSONObject("personType");
		String personTypeId = personTypeJson.getString("values");
		// 获取状态
		JSONObject statusJson = tofastFilterItems.getJSONObject("status");
		String status = statusJson.getString("values");
		// 入职时间段
		JSONObject periodEmploymentJson = tofastFilterItems.getJSONObject("periodEmployment");
		String pervalues = periodEmploymentJson.getString("values");
		String startDate = null;
		String endDate = null;
		if (pervalues != null && !pervalues.equals("")) {
			JSONObject periodEmploymentValues = periodEmploymentJson.getJSONObject("values");
			startDate = periodEmploymentValues.getString("startDate");
			endDate = periodEmploymentValues.getString("endDate");
		}
		// 离职时间段
		JSONObject periodResignationJson = tofastFilterItems.getJSONObject("periodResignation");
		String resValues = periodResignationJson.getString("values");
		String resStartDate = null;
		String resEndDate = null;
		if (resValues != null && !resValues.equals("")) {
			JSONObject periodResignationValues = periodResignationJson.getJSONObject("values");
			resStartDate = periodResignationValues.getString("startDate");
			resEndDate = periodResignationValues.getString("endDate");
		}
		// 截止时间
		JSONObject deadlineJson = tofastFilterItems.getJSONObject("deadline");
		String deadValues = deadlineJson.getString("values");
		String dataeTime = null;
		if (deadValues != null && !deadValues.equals("")) {
			JSONObject deadVluesJson = deadlineJson.getJSONObject("values");
			dataeTime = deadVluesJson.getString("date");
		}
		// List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
		int num = 0;
		IRowSet rs = DbUtil.executeQuery(this.ctx,
				this.getSql(personid, personTypeId, status, startDate, endDate, resStartDate, resEndDate, dataeTime));
		
		LinkedHashMap<String, String> selectFieldsTypeMap = new LinkedHashMap<String, String>();
		String stringType = "String_@";
		String dateType = "Date_yyyy/MM/dd";
		String numberType = "Number_0_%s";
		selectFieldsTypeMap.put("No", numberType);
		selectFieldsTypeMap.put("PERSONNUMBER", numberType);
		selectFieldsTypeMap.put("LASTNAME", stringType);
		selectFieldsTypeMap.put("MiddleName", stringType);
		selectFieldsTypeMap.put("FirstName", stringType);
		selectFieldsTypeMap.put("LocalName", stringType);
		selectFieldsTypeMap.put("DisplayName", stringType);
		selectFieldsTypeMap.put("Department", stringType);
		selectFieldsTypeMap.put("Dept", stringType);
		selectFieldsTypeMap.put("Job", stringType);
		selectFieldsTypeMap.put("Level", stringType);
		selectFieldsTypeMap.put("Position", stringType);
		selectFieldsTypeMap.put("PositionCode", stringType);
		selectFieldsTypeMap.put("WorkCategory", stringType);
		selectFieldsTypeMap.put("PersonType", stringType);
		selectFieldsTypeMap.put("FULLTIMEORPARTTIME", stringType);
		selectFieldsTypeMap.put("LINEMANAGER", numberType);
		selectFieldsTypeMap.put("LINEMANAGERNAME", stringType);
		selectFieldsTypeMap.put("StartTime", dateType);
		selectFieldsTypeMap.put("EndTime", dateType);
		selectFieldsTypeMap.put("HireDate", dateType);
		selectFieldsTypeMap.put("ProbationPeriod", stringType);
		selectFieldsTypeMap.put("ProbationPeriodUnit", stringType);
		selectFieldsTypeMap.put("ProbationEndDate", dateType);
		selectFieldsTypeMap.put("WORKPHONE", stringType);
		selectFieldsTypeMap.put("CellPhone", stringType);
		selectFieldsTypeMap.put("PRIMARYEMAIL", stringType);
		selectFieldsTypeMap.put("Email", stringType);
		selectFieldsTypeMap.put("Nationality", stringType);
		selectFieldsTypeMap.put("WORKPERMITISSUEDATE", dateType);
		selectFieldsTypeMap.put("WORKPERMITEXPIRATIONDATE", dateType);
		selectFieldsTypeMap.put("RESIDENCEPERMITISSUEDATE", dateType);
		selectFieldsTypeMap.put("RESIDENCEPERMITEXPIRATIONDATE", dateType);
		selectFieldsTypeMap.put("IDCARDNOORPASSPORTNO", stringType);
		selectFieldsTypeMap.put("IDCARDCOMMENCINGDATE", dateType);
		selectFieldsTypeMap.put("IDCARDEXPIREDDATE", dateType);
		selectFieldsTypeMap.put("FIDCardAddress", stringType);
		selectFieldsTypeMap.put("CURRENTADDRESS", stringType);
		selectFieldsTypeMap.put("Sex", stringType);
		selectFieldsTypeMap.put("Birthdate", dateType);
		selectFieldsTypeMap.put("Ethnicity", stringType);
		selectFieldsTypeMap.put("Hukoutype", stringType);
		selectFieldsTypeMap.put("HouseholdRegistereddate", dateType);
		selectFieldsTypeMap.put("Nativeplace", stringType);
		selectFieldsTypeMap.put("POLITICASTATUS", stringType);
		selectFieldsTypeMap.put("Maritalstatus", stringType);
		selectFieldsTypeMap.put("Zipcode", stringType);
		selectFieldsTypeMap.put("GRADUATEDSCHOOLOFHIGHESTACADEMICDEGREE", stringType);
		selectFieldsTypeMap.put("HIGHESTACADEMICDEGREE", stringType);
		selectFieldsTypeMap.put("HIGHESTQUALIFICATIONOFFULLTIMEEDUCATION", stringType);
		selectFieldsTypeMap.put("MAJOROFFULLTIMEEDUCATION", stringType);
		selectFieldsTypeMap.put("GRADUATEDDATE", dateType);
		selectFieldsTypeMap.put("GRADUATESCHOOLOFHIGHESTDEGREEOFPARTTIMEEDUCATION", stringType);
		selectFieldsTypeMap.put("HIGHESTDEGREEOFPARTTIMEEDUCATION", stringType);
		selectFieldsTypeMap.put("HIGHESTQUALIFICATIONOFPARTTIMEEDUCATION", stringType);
		selectFieldsTypeMap.put("MAJOROFPARTTIMEEDUCATION", stringType);
		selectFieldsTypeMap.put("EMERGENCYCONTACT", stringType);
		selectFieldsTypeMap.put("TELEPHONEOFEMERGENCY", stringType);
		selectFieldsTypeMap.put("RELATIONSHIP", stringType);
		selectFieldsTypeMap.put("TERMINATIONDATE", dateType);
		selectFieldsTypeMap.put("TERMINATIONREASON", stringType);
		
		//列名要设置成中文+英文
		LinkedHashMap<String, String> selectChineseMap = new LinkedHashMap<String, String>();
		selectChineseMap.put("NO.序号", numberType);
		selectChineseMap.put("Person Number 工号", numberType);
		selectChineseMap.put("Last Name 姓", stringType);
		selectChineseMap.put("Middle Name 中间名", stringType);
		selectChineseMap.put("First Name 名", stringType);
		selectChineseMap.put("Local Name 本地名称", stringType);
		selectChineseMap.put("Display Name 展示名称", stringType);
		selectChineseMap.put("Department 部门", stringType);
		selectChineseMap.put("Dept. 部门简称", stringType);
		selectChineseMap.put("Job 职务", stringType);
		selectChineseMap.put("Level(L1-L15)", stringType);
		selectChineseMap.put("Position 职位", stringType);
		selectChineseMap.put("Position Code", stringType);
		selectChineseMap.put("Work Category 员工类别", stringType);
		selectChineseMap.put("Person Type 人员类型", stringType);
		selectChineseMap.put("Full Time or Part Time 全职或兼职", stringType);
		selectChineseMap.put("ine Manager 直线经理 （工号）", numberType);
		selectChineseMap.put("Line Manager 直线经理 （姓名）", stringType);
		selectChineseMap.put("Start Time", dateType);
		selectChineseMap.put("End Time", dateType);
		selectChineseMap.put("Hire Date 入职日期", dateType);
		selectChineseMap.put("Probation Period 试用期", stringType);
		selectChineseMap.put("Probation Period Unit 试用期单位", stringType);
		selectChineseMap.put("Probation End Date 试用期结束日期", dateType);
		selectChineseMap.put("Work Phone 工作电话", stringType);
		selectChineseMap.put("Cell Phone 手机", stringType);
		selectChineseMap.put("Email (Primary)工作邮箱", stringType);
		selectChineseMap.put("Email 邮箱", stringType);
		selectChineseMap.put("Nationality 国籍", stringType);
		selectChineseMap.put("Work Permit Issue Date 工作许可签发日期", dateType);
		selectChineseMap.put("Work Permit Expiration Date 工作许可失效日期", dateType);
		selectChineseMap.put("Residence Permit Issue Date 居住许可签发日期", dateType);
		selectChineseMap.put("Residence Permit Expiration Date 居住许可失效日期", dateType);
		selectChineseMap.put("ID card no. or Passport no. 身份证号/护照号", stringType);
		selectChineseMap.put("ID card commencing date 身份证开始日期", dateType);
		selectChineseMap.put("ID card expired date 身份证结束日期", dateType);
		selectChineseMap.put("ID card address 身份证地址", stringType);
		selectChineseMap.put("Current address 现在通讯地址", stringType);
		selectChineseMap.put("Sex 性别", stringType);
		selectChineseMap.put("Birth date 出生日期", dateType);
		selectChineseMap.put("Ethnicity 民族", stringType);
		selectChineseMap.put("Hukou type 户籍类型", stringType);
		selectChineseMap.put("Household Registered  date 户口登记日期", dateType);
		selectChineseMap.put("Native place 籍贯", stringType);
		selectChineseMap.put("Political status 政治面貌", stringType);
		selectChineseMap.put("Marital status 婚姻状况", stringType);
		selectChineseMap.put("Zip code 邮政编码", stringType);
		selectChineseMap.put("Graduated School of Highest Academic Degree (full-time) 全日制最高学历毕业院校", stringType);
		selectChineseMap.put("Highest Academic Degree (full-time) 全日制最高学历", stringType);
		selectChineseMap.put("Highest Qualification of Full-time Education 全日制最高学历所获学位", stringType);
		selectChineseMap.put("Major of full time education 全日制最高学历所学专业", stringType);
		selectChineseMap.put("Graduated date 毕业日期", dateType);
		selectChineseMap.put("Graduate School of Highest Degree of Part-time Education 非全日制最高学历毕业院校", stringType);
		selectChineseMap.put("Highest Degree of Part-time Education 非全日制最高学历", stringType);
		selectChineseMap.put("Highest Qualification of Part-time education 非全日制最高学历所获学位", stringType);
		selectChineseMap.put("Major of Part-time education 非全日制最高学历所学专业", stringType);
		selectChineseMap.put("emergency contact 紧急联系人姓名", stringType);
		selectChineseMap.put("telephone of emergency contact 紧急联系人电话", stringType);
		selectChineseMap.put("relationship 所属关系", stringType);
		selectChineseMap.put("Termination Date 离职日期", dateType);
		selectChineseMap.put("Termination Reason 离职原因", stringType);


		
		PersonRosterListHandler.exportData(response, rs, "EmployeeListReport.xlsx", selectFieldsTypeMap,selectChineseMap);

		return null;

	}

	/**
	 * 
	 * @param response
	 *            response
	 * @param resource
	 *            需要导出的数据源
	 * @param fileName
	 *            导出文件名称
	 * @param selectFieldsTypeMap
	 *            存放查询字段对应导出类型和导出样式 key selectField 导出字段 value
	 *            fieldType_cellStyleMap key 导出查询字段 value 导出类型_导出样式 导出样式 key
	 *            DateType 日期格式 value "yyyy-mm-dd" key NumberType 数字 value '0.0_
	 *            '->保留一位小数 '0_ '->整数 key StringType 文本 value @
	 * @throws SQLException
	 */
	public static void exportData(HttpServletResponse response, Object resource, String fileName,
			LinkedHashMap<String, String> selectFieldsTypeMap,LinkedHashMap<String, String> selectChineseMap) throws SQLException {
		// 创建新的Excel 工作簿
		// Excel2003版本（包含2003）以前使用HSSFWorkbook类，扩展名为.xls
		// Excel2007版本（包含2007）以后使用XSSFWorkbook类，扩展名为.xlsx
		XSSFWorkbook workBook = new XSSFWorkbook();

		// 在Excel工作簿中创建新的工作表，名为"Sheet1"
		XSSFSheet sheet = workBook.createSheet("Sheet1");

		DataFormat format = workBook.createDataFormat();
		// 设置数字格式为整数，不保留小数 (_ )空格是必须要切勿格式
		if (resource != null) {
			if (resource instanceof IRowSet) {
				IRowSet rs = (IRowSet) resource;
				// 列数
				int numColumns = selectFieldsTypeMap.entrySet().size();
				// 创建多行多列的数据
				int i = 0;
				// 创建标题
				XSSFRow titleRow = sheet.createRow(i);
				// 列名
				List<String> keyList = selectFieldsTypeMap.keySet().stream().collect(Collectors.toList());
				List<String> chineseList = selectChineseMap.keySet().stream().collect(Collectors.toList());
				// 创建字体样式
				XSSFFont font = workBook.createFont();
				font.setFontName("宋体"); // 设置字体为 Arial
				font.setFontHeightInPoints((short) 11); // 设置字体大小为 12 点
				font.setColor(IndexedColors.BLACK.getIndex()); // 设置字体颜色为黑色
				font.setBold(true);
				// 创建一个新的单元格样式，并将字体样式应用于该样式
				CellStyle cellStyle = workBook.createCellStyle();
				cellStyle.setFont(font);
					
				
				if (i == 0) {
					
					for (int j = 0; j < numColumns; j++) {
						// 创建列
						XSSFCell cell = titleRow.createCell(j);
						//cell.setCellValue(keyList.get(j));
						cell.setCellValue(chineseList.get(j));
						// 调整列宽
						sheet.setColumnWidth(j+1, 6000);
						// 应用字体样式
						titleRow.getCell(j).setCellStyle(cellStyle);
						
					}
				}

				i++;
				// 序列号 
				int sertize=0;
				// 创建数据
				while (rs.next()) {
					sertize++;
					try {
						XSSFRow dateRow = sheet.createRow(i);
						for (int j = 0; j < numColumns; j++) {
							// 创建列
							XSSFCell cell = dateRow.createCell(j);
							// 由于开发与测试环境的数据库类型与不一致 导致无法从数据库表读取相关字段类型判断 且有相关计算字段
							// 所以采用参数判断
							String column = keyList.get(j);
							String fieldTypeAndcellStyleType = selectFieldsTypeMap.get(column);
							int indexOf = fieldTypeAndcellStyleType.indexOf("_");
							// 字段属性类型
							String fieldType = fieldTypeAndcellStyleType.substring(0, indexOf);
							// 样式类型
							String cellStyleType = fieldTypeAndcellStyleType.substring(indexOf + 1,
									fieldTypeAndcellStyleType.length());
							//如果是序号
							if("No".equals(column)) {
								cell.setCellValue(sertize);
								continue;
							}
							// 不设置格式
							cell.setCellValue(rs.getString(column));
							if ("Date".equals(fieldType)) {
								// 日期
								// cell.setCellValue(rs.getString(column));
								Date date = rs.getDate(column);
								cell.setCellValue(date);
								XSSFCellStyle stylee = workBook.createCellStyle();
								stylee.setDataFormat(format.getFormat(cellStyleType));
								cell.setCellStyle(stylee);
								// cell.setCellValue(rs.getString(column));
							} else if ("Number".equals(fieldType)) {
								// 数值 (小数和整数)
								String value = rs.getString(column);
								cell.setCellValue(value);
								if (value != null && !("").equals(value)) {
									XSSFCellStyle style = workBook.createCellStyle();
									style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
									cell.setCellStyle(style);
									if (cellStyleType.contains(".")) {
										// 小数
										cell.setCellValue(Double.parseDouble(value));
									} else {
										cell.setCellValue(Integer.parseInt(value));
									}
								}
							} else if ("String".equals(fieldType)) {
								// 文本类型
								if (rs.getString(column) != null && !("").equals(rs.getString(column))) {
									// style.setDataFormat(format.getFormat(cellStyleType));
									// cell.setCellStyle(style);
									cell.setCellValue(rs.getString(column));
								}
							}
						}
						i++;
					} catch (Exception e) {
						System.out.println();
						logger1.info("Excel file was created Error!" + e.getMessage());
					}
				}

				try {

					setResponseHeader(response, fileName);
					OutputStream os = response.getOutputStream();
					workBook.write(os);
					workBook.close();
					os.flush();
					os.close();
					logger1.info("Excel file was created successfully!");
				} catch (IOException e) {
					logger1.info("Error while writing to file " + e.getMessage());
					e.printStackTrace();
				}

			}

		}

	}

	private static Logger logger = Logger.getLogger("com.kingdee.shr.customer.gtiit.rpt.PersonRosterListHandler");

	public static void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			try {
				fileName = URLEncoder.encode(fileName, "UTF-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			response.setContentType("application/octet-stream;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

}