guangzhouyiselie/GDYSL/.svn/pristine/ec/ec2156d6e059c980537e2676b1d242baa4bdfc1b.svn-base

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

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

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

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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 org.json.JSONObject;
import org.springframework.ui.ModelMap;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.base.form.json.JSONException;
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 com.kingdee.shr.base.syssetting.web.json.JSONUtils;
import com.kingdee.shr.customer.gtiit.entity.VacationInfo;

/**
 * @author ISSUSER FacultyAbsenceSummary报表
 */
public class FacultyAbsenceSummaryListHandler extends ListHandler {
	private static Logger logger = Logger
			.getLogger("com.kingdee.shr.customer.gtiit.rpt.FacultyAbsenceSummaryListHandler");

	Context ctx = SHRContext.getInstance().getContext();

	private int totalCount;

	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, JSONException {
		logger.info(">>> FacultyAbsenceSummaryReportHandler...getGridDataAction");
		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 queryTime = tofastFilterItems.getJSONObject("queryTime");
		String pvalues = queryTime.getString("values");
		String startDate = null;
		String endDate = null;
		if (ObjectUtils.isNotEmpty(pvalues)) {
			JSONObject periodEmploymentValues = queryTime.getJSONObject("values");
			// 获取日期参数
			startDate = periodEmploymentValues.getString("startDate");
			endDate = periodEmploymentValues.getString("endDate");
		}

		// 获取审批通过的sql
		String approvedSql = this.approvedSql(startDate, endDate);
		// 获取sercrh的过虑
		String searchFilterItems = request.getParameter("searchFilterItems");
		if (StringUtils.isNoneBlank(searchFilterItems)) {
			String filterItmes = getSearchFilterItems(searchFilterItems);
			approvedSql = approvedSql + " and " + filterItmes;
		}
		// 查询审批通过的
		IRowSet rs = DbUtil.executeQuery(this.ctx, approvedSql);
		// 工作日天数
		List<String> workDaysList = workDays(startDate, endDate);
		// BigDecimal bigDecimalWork = new BigDecimal(workDaysList.size());
		// 获取审批通过计算后的数据
		Map<String, VacationInfo> approvedlMap = executeMethod(rs, startDate, endDate, workDaysList);
		int num = 0;
		List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
		rs.beforeFirst();
		Set<String> numberSet = new HashSet<String>();
		while (rs.next()) {
			if (!numberSet.contains(rs.getString("staffid"))) {
				++num;
				if (num >= rows * (page - 1) + 1 && num <= rows * page) {
					Map<String, Object> map = new HashMap<String, Object>();
					VacationInfo vacationInfo = approvedlMap.get(rs.getString("staffid"));
					map.put("StaffID", rs.getString("staffid"));
					map.put("Name", rs.getString("name"));
					map.put("Title", rs.getString("title"));
					map.put("AcademicTitle", rs.getString("academictitle"));
					map.put("Program", rs.getString("program"));
					map.put("AnnualLeave(Days)", vacationInfo.getAnnualLeave());
					map.put("BusinessTrip(Days)", vacationInfo.getBusinessTrip());
					map.put("OtherLeave(Days)", vacationInfo.getOtherLeave());
					map.put("InCampus(Days)", vacationInfo.getInCampus());
					map.put("AnnualLeave(%)", vacationInfo.getPercentageAnnualLeave());
					map.put("BusinessTrip(%)", vacationInfo.getPercentageBusinessTrip());
					map.put("OtherLeave(%)", vacationInfo.getPercentageOtherLeave());
					map.put("InCampus(%)", vacationInfo.getPercentageInCampus());
					maplist.add(map);
				}
			}
			numberSet.add(rs.getString("staffid"));
		}
		System.out.print("num是" + num);
		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 Map<String, VacationInfo> executeMethod(IRowSet rs, String startDate, String endDate, List<String> list) {
		// 年假id
		Set<String> policySet = getPolicy();
		// 出差假id
		Set<String> businessIdSet = getBusinessId();
		// 其他假期id
		Set<String> otherIdSet = otherId();
		// 计算假期数据
		Map<String, VacationInfo> calcuteVacationMap = calcuteVacation(rs, policySet, otherIdSet, businessIdSet, list);
		// List<String> workDaysList = workDays(startDate, endDate);
		// BigDecimal bigDecimalWork = new BigDecimal(workDaysList.size());
		Map<String, VacationInfo> percentageLeavelMap = percentageLeavel(calcuteVacationMap,
				new BigDecimal(list.size()));

		return percentageLeavelMap;
	}

	public ArrayList<String> getcolNamesAction() throws SHRWebException {
		ArrayList<String> list = new ArrayList<String>();
		list.add("Staff ID");
		list.add("Name");
		list.add("Title");
		list.add("Academic Title");
		list.add("Program");
		list.add("Annual Leave (Days)");
		list.add("Business Trip (Days)");
		list.add("Other Leave (Days)");
		list.add("In Campus (Days)");
		list.add("Annual Leave (%)");
		list.add("Business Trip (%)");
		list.add("Other Leave (%)");
		list.add("In Campus (%)");
		return list;
	}

	public List<Map<String, Object>> getcolModelAction() {
		List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
		modelColList.add(this.buildColModelMap("StaffID", "Staff ID", 120));
		modelColList.add(this.buildColModelMap("Name", "Name", 120));
		modelColList.add(this.buildColModelMap("Title", "Title", 120));
		modelColList.add(this.buildColModelMap("AcademicTitle", "Academic Title", 120));
		modelColList.add(this.buildColModelMap("Program", "Program", 120));
		modelColList.add(this.buildColModelMap("AnnualLeave(Days)", "Annual Leave (Days)", 120));
		modelColList.add(this.buildColModelMap("BusinessTrip(Days)", "Business Trip (Days)", 120));
		modelColList.add(this.buildColModelMap("OtherLeave(Days)", "Other Leave (Days)", 120));
		modelColList.add(this.buildColModelMap("InCampus(Days)", "In Campus (Days)", 120));
		modelColList.add(this.buildColModelMap("AnnualLeave(%)", "Annual Leave (%)", 120));
		modelColList.add(this.buildColModelMap("BusinessTrip(%)", "Business Trip (%)", 120));
		modelColList.add(this.buildColModelMap("OtherLeave(%)", "Other Leave (%)", 120));
		modelColList.add(this.buildColModelMap("InCampus(%)", "In Campus (%)", 120));
		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 approvedSql(String startDate, String endDate) {
		// 获取当前日期
		Date nowDate = new Date();
		String nowdate = this.getDate(nowDate);
		StringBuffer sql = new StringBuffer();

		sql.append(
				"SELECT g.fnumber as staffid,d.fname_l1 as title,d.cflongname_l1 as AcademicTitle , e.fname_l1 as Program,g.fname_l1 as name,g.fid,j.toleavelenth as leavelenth,j.fpolicyid as cfpolicy,g.fid,j.fleavedate\r\n"
						+ " FROM T_HR_EmpOrgRelation a \r\n"
						+ "left join T_ORG_Position d on d.fid  = a.fpositionid  left join T_ORG_BaseUnit e on e.fid = a.FAdminOrgID left join T_ORG_Job f on f.fid=d.fjobid\r\n"
						+ "left join t_bd_person g on g.fid=a.fpersonid\r\n"
						+ "left join T_HR_EmpLaborRelationHis i on i.fpersonid=g.fid\r\n"
						+ "left join  T_HR_BDEmployeeType  h on h.fid=i.flaborrelationstateid\r\n"
						+ "left join (SELECT case when a.fleaveunit=2 then ROUND(a.fleavelength / 7.5, 2) else a.fleavelength end as toleavelenth,a.* FROM T_HR_ATS_LeaveBillDetail a where a.fleavedate >='"
						+ startDate + "' and a.fleavedate <='" + endDate + "')j\r\n"
						+ "on j.fproposerid=a.fpersonid\r\n"
						+ "where f.fnumber='114'and d.fname_l1 not LIKE '%Visiting%'\r\n" + "and i.feffdt<='" + nowdate
						+ "' and i.fleffdt>='" + nowdate + "'\r\n" + "and a.feffdt<='" + nowdate + "' and a.fleffdt>='"
						+ nowdate + "'\r\n" + "and h.finservice='1'");

		System.out.print("summary审批通过的sql" + sql.toString());
		return sql.toString();

	}

	/***
	 * 假期类型是年假 cfpolicy
	 */
	public Set<String> getPolicy() {
		Set<String> policySet = new HashSet<>();
		String sql = "SELECT cfpolicy FROM CT_ATS_AtsLeaveBillDetailSame where cfpolicy in(select fid from T_HR_ATS_HolidayPolicy where fholidaytypeid in (select fid from T_HR_ATS_HolidayType where fnumber  in('JQLX000001Y')));";
		try {
			IRowSet rs = DbUtil.executeQuery(this.ctx, sql);
			while (rs.next()) {
				policySet.add(rs.getString("cfpolicy"));
			}
		} catch (BOSException | SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}

		return policySet;
	}

	/***
	 * 内非年假、出差的 假期类型的id
	 */
	public Set<String> otherId() {
		Set<String> otherSet = new HashSet<>();
		String sql = "SELECT cfpolicy FROM CT_ATS_AtsLeaveBillDetailSame where cfpolicy in(select fid from T_HR_ATS_HolidayPolicy where fholidaytypeid in (select fid from T_HR_ATS_HolidayType where fnumber not in('JQLX000020Y','JQLX000001Y')));";
		try {
			IRowSet rs = DbUtil.executeQuery(this.ctx, sql);
			while (rs.next()) {
				otherSet.add(rs.getString("cfpolicy"));
			}
		} catch (BOSException | SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}

		return otherSet;
	}

	/***
	 * 出差假的id
	 */
	public Set<String> getBusinessId() {
		Set<String> businessSet = new HashSet<>();
		String sql = "SELECT cfpolicy FROM CT_ATS_AtsLeaveBillDetailSame where cfpolicy in(select fid from T_HR_ATS_HolidayPolicy where fholidaytypeid in (select fid from T_HR_ATS_HolidayType where fnumber  in('JQLX000020Y')));";
		try {
			IRowSet rs = DbUtil.executeQuery(this.ctx, sql);
			while (rs.next()) {
				businessSet.add(rs.getString("cfpolicy"));
			}
		} catch (BOSException | SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}

		return businessSet;
	}

	/***
	 * 计算假期数据
	 * 
	 * @param rs
	 *            查询的假期数据
	 * @param policySet
	 *            假期类型是年假 id
	 * @param otherSet
	 * @param days
	 *            天数 businessSet 出差id dateList 工作日期
	 */
	public Map<String, VacationInfo> calcuteVacation(IRowSet rs, Set<String> policySet, Set<String> otherSet,
			Set<String> businessSet, List<String> dateList) {
		// 创建map key是工号 value是存储数据的对象
		Map<String, VacationInfo> vacationMap = new HashMap<>();
		// 将int值转换为BigDecimal

		// BigDecimal intAsBigDecimal = new BigDecimal(days);
		try {
			while (rs.next()) {
				VacationInfo vaction = new VacationInfo();
				// 工号
				String stattId = rs.getString("staffid");
				// 请假日期fleavedate
				String leavedate = getDate(rs.getDate("fleavedate"));
				// 假期长度 leavelenth
				BigDecimal leavelenth = rs.getBigDecimal("leavelenth") == null ? new BigDecimal("0.00")
						: rs.getBigDecimal("leavelenth").setScale(2, RoundingMode.HALF_UP);
				// 假期类型id
				String cfpolicy = rs.getString("cfpolicy");
				// 判断key 是否存在
				if (!vacationMap.containsKey(stattId)) {
					// 判断是否是年假 其他字段先设置为0
					if (policySet.contains(cfpolicy)) {
						vaction.setAnnualLeave(leavelenth);
						vaction.setBusinessTrip(new BigDecimal("0.00"));
						vaction.setOtherLeave(new BigDecimal("0.00"));
					}
					// 出差假
					if (businessSet.contains(cfpolicy)) {
						// 是工作日才计算
						if (dateList.contains(leavedate)) {

							vaction.setBusinessTrip(leavelenth);
						}

						vaction.setAnnualLeave(new BigDecimal("0.00"));
						vaction.setOtherLeave(new BigDecimal("0.00"));
					}
					// 其他假期
					if (otherSet.contains(cfpolicy)) {
						vaction.setOtherLeave(leavelenth);
						vaction.setBusinessTrip(new BigDecimal("0.00"));
						vaction.setAnnualLeave(new BigDecimal("0.00"));
					}
					vacationMap.put(stattId, vaction);
				} else {
					// 存在就把假期要累加起来
					VacationInfo vacationInfo = vacationMap.get(stattId);
					// 判断是否是年假
					if (policySet.contains(cfpolicy)) {
						BigDecimal annualLeave = vacationInfo.getAnnualLeave();
						if(annualLeave!=null) {
							
							vacationInfo.setAnnualLeave(annualLeave.add(leavelenth));
						}else {
							vacationInfo.setAnnualLeave(new BigDecimal(0).add(leavelenth));
						}
					}
					// 出差假
					if (businessSet.contains(cfpolicy)) {
						BigDecimal businessTrip = vacationInfo.getBusinessTrip();
						// 是工作日才计算
						if (dateList.contains(leavedate)) {
							if(businessTrip!=null) {
								vacationInfo.setBusinessTrip(businessTrip.add(leavelenth));
								
							}else {
								vacationInfo.setBusinessTrip(new BigDecimal(0).add(leavelenth));
							}
						}
					}
					// 其他假期
					if (otherSet.contains(cfpolicy)) {
						BigDecimal otherLeave = vacationInfo.getOtherLeave();
						vacationInfo.setOtherLeave(otherLeave.add(leavelenth));
					}
					vacationMap.put(stattId, vacationInfo);
				}
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return vacationMap;
	}

	/***
	 * 计算假期的百分比
	 */
	public Map<String, VacationInfo> percentageLeavel(Map<String, VacationInfo> vacationMap,
			BigDecimal intAsBigDecimal) {
		System.out.print("工作日天数" + intAsBigDecimal);
		// 创建map key是工号 value是存储数据的对象
		Map<String, VacationInfo> peracentageMap = new HashMap<>();
		for (String key : vacationMap.keySet()) {
			VacationInfo vacationInfo = vacationMap.get(key);
			// 年假
			BigDecimal annualLeave = vacationInfo.getAnnualLeave() == null ? new BigDecimal("0.00")
					: vacationInfo.getAnnualLeave();
			// 出差假
			BigDecimal businessTrip = vacationInfo.getBusinessTrip() == null ? new BigDecimal("0.00")
					: vacationInfo.getBusinessTrip();
			// 其他假期
			BigDecimal otherLeave = vacationInfo.getOtherLeave() == null ? new BigDecimal("0.00")
					: vacationInfo.getOtherLeave();
			// 计算工作天数
			BigDecimal inCampus = intAsBigDecimal.subtract(annualLeave).subtract(businessTrip).subtract(otherLeave);
			vacationInfo.setInCampus(inCampus);
			// 年假所占的百分比percentage
			BigDecimal percentageAnnualeave = annualLeave.divide(intAsBigDecimal, 4, RoundingMode.HALF_UP);
			// 出差假所占的百分比
			BigDecimal percentagebusinessTrip = businessTrip.divide(intAsBigDecimal, 4, RoundingMode.HALF_UP);
			// 其他假期所占的百分比
			BigDecimal percentageotherLeave = otherLeave.divide(intAsBigDecimal, 4, RoundingMode.HALF_UP);
			// 工作日期所占的百分比
			// BigDecimal percentageinCampus = inCampus.divide(intAsBigDecimal, 2,
			// RoundingMode.HALF_UP);
			BigDecimal one = new BigDecimal(1);
			BigDecimal percentageinCampus = one.subtract(percentageAnnualeave).subtract(percentagebusinessTrip)
					.subtract(percentageotherLeave);
			vacationInfo.setPercentageAnnualLeave(percentageAnnualeave.multiply(new BigDecimal(100)));
			vacationInfo.setPercentageBusinessTrip(percentagebusinessTrip.multiply(new BigDecimal(100)));
			vacationInfo.setPercentageOtherLeave(percentageotherLeave.multiply(new BigDecimal(100)));
			vacationInfo.setPercentageInCampus(percentageinCampus.multiply(new BigDecimal(100)));
			vacationInfo.setAnnualLeave(annualLeave);
			vacationInfo.setOtherLeave(otherLeave);
			vacationInfo.setBusinessTrip(businessTrip);
			peracentageMap.put(key, vacationInfo);
		}
		return peracentageMap;
	}

	/***
	 * 日期范围类工作日的天数
	 */
	public List<String> workDays(String startDate, String endDate) {
		List<String> workList = new ArrayList<>();
		String sql = "SELECT * FROM  T_HR_ATS_WorkCalendarItem where fdaytype='0' and fdate>='" + startDate
				+ "' and  fdate<='" + endDate + "'";
		try {
			IRowSet executeQuery = DbUtil.executeQuery(this.ctx, sql);
			while (executeQuery.next()) {
				workList.add(getDate(executeQuery.getDate("fdate")));
			}
		} catch (BOSException | SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return workList;
	}

	// 导出
	// 导出数据
	public String toExcelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException {
		// 构建参数去查询数据库
		// 获取过滤条件
		String fastFilterItems = request.getParameter("fastFilterItems");
		JSONObject tofastFilterItems = new JSONObject(fastFilterItems);
		// 获取时间的过滤条件
		JSONObject queryTime = tofastFilterItems.getJSONObject("queryTime");
		String pvalues = queryTime.getString("values");
		String startDate = null;
		String endDate = null;
		if (ObjectUtils.isNotEmpty(pvalues)) {
			JSONObject periodEmploymentValues = queryTime.getJSONObject("values");
			// 获取日期参数
			startDate = periodEmploymentValues.getString("startDate");
			endDate = periodEmploymentValues.getString("endDate");
		}
		// 获取审批通过的sql
		String approvedSql = this.approvedSql(startDate, endDate);
		// 获取sercrh的过虑
		String searchFilterItems = request.getParameter("searchFilterItems");
		if (StringUtils.isNoneBlank(searchFilterItems)) {
			String filterItmes = getSearchFilterItems(searchFilterItems);
			approvedSql = approvedSql + " and " + filterItmes;
		}
		// 查询审批通过的
		IRowSet rs = DbUtil.executeQuery(this.ctx, approvedSql);
		// 工作日天数
		List<String> workDaysList = workDays(startDate, endDate);
		// BigDecimal bigDecimalWork = new BigDecimal(workDaysList.size());
		// 获取审批通过计算后的数据
		Map<String, VacationInfo> approvedlMap = executeMethod(rs, startDate, endDate, workDaysList);
		LinkedHashMap<String, String> selectFieldsTypeMap = new LinkedHashMap<String, String>();
		String stringType = "String_@";
		String numberType = "Number_.";
		selectFieldsTypeMap.put("staffid", stringType);
		selectFieldsTypeMap.put("name", stringType);
		selectFieldsTypeMap.put("title", stringType);
		selectFieldsTypeMap.put("academictitle", stringType);
		selectFieldsTypeMap.put("Program", stringType);
		selectFieldsTypeMap.put("AnnualLeaveDays", numberType);
		selectFieldsTypeMap.put("BusinessTripDays", numberType);
		selectFieldsTypeMap.put("OtherLeaveDays", numberType);
		selectFieldsTypeMap.put("InCampusDays", numberType);
		selectFieldsTypeMap.put("AnnualLeave", numberType);
		selectFieldsTypeMap.put("BusinessTrip", numberType);
		selectFieldsTypeMap.put("OtherLeave", numberType);
		selectFieldsTypeMap.put("InCampus", numberType);
		ArrayList<String> list = this.getcolNamesAction();
		exportData(response, rs, "FacultyAbsenceSummaryReport.xlsx", selectFieldsTypeMap, list, approvedlMap);
		return null;
	}

	// 获取serch的过虑条件返回拼接sql
	public String getSearchFilterItems(String searchFilterItems) {
		// 获取serch的过虑条件
		String filter = null;
		if (StringUtils.isNotBlank(searchFilterItems)) {
			// 姓名
			if (searchFilterItems.indexOf("name") != -1) {
				filter = searchFilterItems.replaceAll("name", "g.fname_l1");
			}
			// 编码
			if (searchFilterItems.indexOf("number") != -1) {
				filter = searchFilterItems.replaceAll("number", "g.fnumber");
			}
		}
		if (filter != null) {
			return filter.replace("(", "").replace(")", "");
		}

		return filter;

	}

	/**
	 *
	 * @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, List<String> list,
			Map<String, VacationInfo> percentageLeavelMap) 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());
				// 创建字体样式
				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(list.get(j));
						// 调整列宽
						sheet.setColumnWidth(j, 5000);
						// 应用字体样式
						titleRow.getCell(j).setCellStyle(cellStyle);
					}
				}

				i++;
				// 创建数据
				Set<String> staffSet = new HashSet<String>();
				rs.beforeFirst();
				while (rs.next()) {
					System.out.print("进来了while循环");
					try {
						if (!staffSet.contains(rs.getString("staffid"))) {
							VacationInfo vacationInfo = percentageLeavelMap.get(rs.getString("staffid"));
							XSSFRow dateRow = sheet.createRow(i);
							System.out.print("IIIII是" + 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 ("AnnualLeaveDays".equals(column)) {
									cell.setCellValue(vacationInfo.getAnnualLeave().toString());
								} else if ("BusinessTripDays".equals(column)) {
									cell.setCellValue(vacationInfo.getBusinessTrip().toString());
								} else if ("OtherLeaveDays".equals(column)) {
									cell.setCellValue(vacationInfo.getOtherLeave().toString());
								} else if ("InCampusDays".equals(column)) {
									cell.setCellValue(vacationInfo.getInCampus().toString());
								} else if ("AnnualLeave".equals(column)) {
									cell.setCellValue(vacationInfo.getPercentageAnnualLeave().setScale(2).toString());
								} else if ("BusinessTrip".equals(column)) {
									cell.setCellValue(vacationInfo.getPercentageBusinessTrip().setScale(2).toString());
								} else if ("OtherLeave".equals(column)) {
									cell.setCellValue(vacationInfo.getPercentageOtherLeave().setScale(2).toString());
								} else if ("InCampus".equals(column)) {
									// setNumerical(workBook, format, key, cell);
									cell.setCellValue(vacationInfo.getPercentageInCampus().setScale(2).toString());
								} else {

									// 不设置格式
									cell.setCellValue(rs.getString(column));
								}

							}
							i++;
						}
						staffSet.add(rs.getString("staffid"));
						// i++;
					} catch (Exception e) {
						logger.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();
					logger.info("Excel file was created successfully!");
				} catch (IOException e) {
					logger.info("Error while writing to file " + e.getMessage());
					e.printStackTrace();
				}
			}
		}
	}

	// /***
	// * 设置格式是数值
	// */
	// public static void setNumerical(XSSFWorkbook workBook,DataFormat
	// format,String cellStyleType,XSSFCell cell,BigDecimal value) {
	// XSSFCellStyle style = workBook.createCellStyle();
	// style.setDataFormat(format.getFormat(String.format(cellStyleType, " ")));
	// cell.setCellStyle(style);
	// if (cellStyleType.contains(".")) {
	// // 小数
	// cell.setCellValue(Double.parseDouble(value.toString()));
	// } else {
	// cell.setCellValue(Integer.parseInt(value.toString()));
	// }
	// }
	//

	/***
	 * 
	 * @param response
	 * @param fileName
	 */
	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();
		}
	}

	/***
	 * 当前日期转成String 格式
	 */
	public String getDate(Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 设置日期格式
		if (date == null) {
			return null;
		}
		return sdf.format(date);
	}
}