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

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Calendar;
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.concurrent.ConcurrentHashMap;

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

import org.springframework.ui.ModelMap;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
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 com.kingdee.shr.base.syssetting.web.json.JSONUtils;
import com.kingdee.shr.customer.gtiit.util.ExportAllUtils;
//import com.kingdee.shr.customer.gtiit.util.FacutilyExportUtils;
import com.kingdee.shr.customer.gtiit.util.RptUtils;

//教员缺勤报告
public class FacultyAbseceReporttoMorisListHandler extends ListHandler {
	Context ctx = SHRContext.getInstance().getContext();
	private int totalCount;
	static BigDecimal hours = new BigDecimal(60 * 60 * 1000);

	public FacultyAbseceReporttoMorisListHandler() {
	}

	public String getGridColModelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws SHRWebException, BOSException, SQLException {

		try {
			String parameter = request.getParameter("postData");
			JSONObject paramJson = JSON.parseObject(parameter);
			JSONObject fastJson = paramJson.getJSONObject("fastFilterItems");
			JSONObject qeryTimeJson = fastJson.getJSONObject("queryTime");
			JSONObject valuesJson = qeryTimeJson.getJSONObject("values");
			String tostartDate = null;
			String toendDate = null;
			if (valuesJson != null && !valuesJson.equals("")) {
				tostartDate = valuesJson.getString("startDate");
				toendDate = valuesJson.getString("endDate");
			}
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Date startDates = sdf.parse(tostartDate);
			Date endDates = sdf.parse(toendDate);
			List<String> colNames = this.getcolNamesAction(startDates, endDates);
			List<Map<String, Object>> colModel = this.getcolModelAction(startDates, endDates);
			LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
			map.put("colNames", colNames);
			map.put("colModel", colModel);
			JSONUtils.writeJson(response, map);
		} catch (ParseException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}

		return null;
	}

	public void getGridDataAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException, ParseException {
		int rows = Integer.parseInt(request.getParameter("rows"));
		int page = Integer.parseInt(request.getParameter("page"));
		//List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
		int num = 0;
		// 获取报表查询过滤条件
		List<String> qfNames = new ArrayList<>();
		qfNames.add("queryTime");
		String parameter = request.getParameter("fastFilterItems");
		JSONObject paramJson = JSON.parseObject(parameter);
		Map<String, Object> rptSQLFilterate = RptUtils.getRptSQLFilterate(paramJson, qfNames);

		JSONObject queyTimeJson = paramJson.getJSONObject("queryTime");
		String pervalues = queyTimeJson.getString("values");
		String tostartDate = null;
		String toendDate = null;
		if (pervalues != null && !pervalues.equals("")) {
			JSONObject queryTimeValues = queyTimeJson.getJSONObject("values");
			tostartDate = queryTimeValues.getString("startDate");
			toendDate = queryTimeValues.getString("endDate");

		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date startDates = sdf.parse(tostartDate);
		// String formattoendDate = sdf.format(toendDate);
		Date endDates = sdf.parse(toendDate);
		// 获取过滤条件的开始时间和结束时间
		List<String> fiterDate = this.getDateList(startDates, endDates, "dd/MM");
		// 获取报表数据源
		String sqlInfo = getSql(rptSQLFilterate);
		IRowSet rs = DbUtil.executeQuery(this.ctx, sqlInfo);

		// key 人员工号 value 1.存放部门等相关信息 key为 界面name value值
		// 2.key 每天的日期 value 为 假期类型 xx小时 ,假期类型 xx小时
	//	LinkedHashMap<String, Map<String, Object>> allDataMap = new LinkedHashMap<>();
		//SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd");
		//SimpleDateFormat sdf3 = new SimpleDateFormat("dd/MM");
		//boolean first = true;
		//SimpleDateFormat sdf4 = new SimpleDateFormat("yy/MM");
		// key是工号 value存储日期和请假类型
		//Map<String, Map<String, String>> numberMap = new HashMap<>();
		// Set存放人员的工号
		Set<String> numberSet = new HashSet<>();
	
		Map<String, Map<String, String>> numberMap = this.getAllmap(rs, numberSet, fiterDate);
		List<Map<String, Object>> tomaplist = new ArrayList<Map<String, Object>>();
		rs.beforeFirst();
		while (rs.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			// 工号
			String fnumber = rs.getString("StaffID");
			// 姓名
			String name = rs.getString("name");
			// 请假开始时间
			//Date fbegintime = rs.getDate("FBEGINTIME");
			// 请假结束时间
			//Date fendtime = rs.getDate("FENDTIME");
			// 岗位 title
			String title = rs.getString("title");
			// 请假类型
			//String atsType = rs.getString("atsType");
			// Academic
			String academic = rs.getString("AcademicTitle");
			// Program 部门
			String program = rs.getString("program");
			// 获取请假开始日期和结束日期的所有时间段
			//List<String> dateList = this.getDateList(fbegintime, fendtime, "dd/MM");
			// 用掉一个Set就去除一个对应元素
			if (numberSet.contains(fnumber)) {
				Map<String, String> tofnumberMap = numberMap.get(fnumber);
				map.put("StaffID", fnumber);
				map.put("Name", name);
				map.put("Title", title);
				map.put("Academic Title", academic);
				map.put("Program", program);
				for (Map.Entry<String, String> entry : tofnumberMap.entrySet()) {
					// 截取工号和姓名
					String key = entry.getKey();
					String subKey = key.substring(0, 5);
					String value = entry.getValue();
					// String tokey = key.substring(5);
					map.put(subKey, value);

				}
				numberSet.remove(fnumber);

				tomaplist.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", tomaplist);
		JSONUtils.writeJson(response, gridDataMap);
	}

	/**
	 * 拆分两个日期中的规定上班时间
	 *
	 * @param start
	 *            请假开始日期
	 * @param end
	 *            请假结束日期
	 * @return LinkedHashMap<Date, Date>
	 */
	public static List<LinkedHashMap<String, Date>> splitDateRange(Date start, Date end) {
		List<LinkedHashMap<String, Date>> rs = new ArrayList<>();
		LinkedHashMap<String, Date> dateMap = new LinkedHashMap<>();
		// 获取两个日期之间的天数
		long daysBetween = (end.getTime() - start.getTime()) / (24 * 60 * 60 * 1000); // 转换为天数差
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(start);
		int year = calendar.get(Calendar.YEAR); // 获取年份
		int month = calendar.get(Calendar.MONTH); // 获取月份（注意，月份是从0开始的，所以要加1）
		int day = calendar.get(Calendar.DAY_OF_MONTH); // 获取日期
		// 当天的午休时间
		calendar.set(year, month, day, 12, 00, 0); // 年 月（从0开始） 日 时 分 秒
		Date lunchStartDate = calendar.getTime();
		dateMap.put("lunchStartDate", lunchStartDate);
		calendar.set(year, month, day, 13, 30, 0); // 年 月（从0开始） 日 时 分 秒
		Date lunchEndDate = calendar.getTime();
		dateMap.put("lunchEndDate", lunchEndDate);

		// 单天 直接返回
		if (daysBetween == 0) {
			calendar.set(year, month, day, 8, 30, 0); // 年 月（从0开始） 日 时 分 秒
			Date toDayMinStartTime = calendar.getTime();
			if (start.compareTo(toDayMinStartTime) < 1) {
				start = toDayMinStartTime;
			}
			calendar.set(year, month, day, 17, 30, 0); // 年 月（从0开始） 日 时 分 秒
			Date toDayMinEndTime = calendar.getTime();
			if (end.compareTo(toDayMinEndTime) == 1) {
				end = toDayMinEndTime;
			}
			dateMap.put("startDate", start);
			dateMap.put("endDate", end);

			rs.add(dateMap);
			return rs;
		} else {
			// 多天初始话第一天晚上
			calendar.set(year, month, day, 17, 30, 0); // 年 月（从0开始） 日 时 分 秒
			dateMap.put("startDate", start);
			dateMap.put("endDate", calendar.getTime());
			rs.add(dateMap);
		}
		// 拆分日期范围并添加到Map中（从第二天开始）
		for (int i = 1; i <= daysBetween; i++) { // 从第二天开始计算，所以循环从1开始而不是从0开始
			LinkedHashMap<String, Date> nextDateMap = new LinkedHashMap<>();
			// 对应的第二到第N天的上下班时间
			Calendar calendarStart = Calendar.getInstance();
			calendarStart.set(year, month, day, 8, 30, 0); // 年 月（从0开始） 日 时 分 秒
			calendarStart.add(Calendar.DAY_OF_MONTH, 1); // 加一天
			Date newStartDate = calendarStart.getTime();
			// 当天结束日期
			Calendar calendarEnd = Calendar.getInstance();
			calendarEnd.set(year, month, day, 17, 30, 0); // 年 月（从0开始） 日 时 分 秒
			calendarEnd.add(Calendar.DAY_OF_MONTH, 1); // 加一天
			Date newEndDate = calendarEnd.getTime();
			// 获取第二天到第N天的 年月日
			year = calendarStart.get(Calendar.YEAR); // 获取年份
			month = calendarStart.get(Calendar.MONTH); // 获取月份（注意，月份是从0开始的，所以要加1）
			day = calendarStart.get(Calendar.DAY_OF_MONTH); // 获取日期
			// 加一天
			Date newLunchStartDate = new Date(lunchStartDate.getTime() + (24 * 60 * 60 * 1000));
			nextDateMap.put("lunchStartDate", newLunchStartDate);
			lunchStartDate = newLunchStartDate;

			Date newLunchEndDate = new Date(lunchEndDate.getTime() + (24 * 60 * 60 * 1000));
			nextDateMap.put("lunchEndDate", newLunchEndDate);
			lunchEndDate = newLunchEndDate;
			if (newEndDate.compareTo(end) < 1) {
				nextDateMap.put("startDate", newStartDate);
				nextDateMap.put("endDate", newEndDate);
				rs.add(nextDateMap);
			} else {
				nextDateMap.put("startDate", newStartDate);
				nextDateMap.put("endDate", end);
				rs.add(nextDateMap);
			}
		}
		return rs;
	}

	/**
	 * @param startTime
	 *            请假开始时间
	 * @param endTime
	 *            请假结束时间
	 * @param lunchStart
	 *            午休开始时间
	 * @param lunchEnd
	 *            午休结束时间
	 * @return 当天的请假 小时数
	 */
	public static double getLeaveHoursBetween(Date startTime, Date endTime, Date lunchStart, Date lunchEnd) {
		// 请假总时长
		long workDiffInMilliseconds = Math.abs(endTime.getTime() - startTime.getTime());
		BigDecimal work = new BigDecimal(workDiffInMilliseconds);
		BigDecimal workHour = work.divide(hours, 2, RoundingMode.HALF_UP);

		// 1. 开始-结束(半天) 2.开始到午休 3.午休到结束 4 开始到结束(整天)
		if (startTime.before(lunchStart) && endTime.after(lunchEnd)) {
			// 2.请假开始时间午休前 请假结束日期午休结束后
			long diffInMilliseconds = Math.abs(lunchEnd.getTime() - lunchStart.getTime());
			BigDecimal noWork = new BigDecimal(diffInMilliseconds);
			BigDecimal noWorkHours = noWork.divide(hours, 2, RoundingMode.HALF_UP);
			return workHour.subtract(noWorkHours).doubleValue();
		} else if (startTime.after(lunchStart) && startTime.before(lunchEnd) && endTime.after(lunchEnd)) {
			// 3. 请假开始时间午休中 请假结束日期是午休后 (需剔除午休请假时间 午休接受时间-请假开始时间)
			// 非工作时间
			long diffInMilliseconds = Math.abs(lunchEnd.getTime() - startTime.getTime());
			BigDecimal noWork = new BigDecimal(diffInMilliseconds);
			BigDecimal noWorkHours = noWork.divide(hours, 2, RoundingMode.HALF_UP);
			return workHour.subtract(noWorkHours).doubleValue();
		} else if (startTime.before(lunchStart) && endTime.before(lunchEnd) && endTime.after(lunchStart)) {
			// 4.开始时间在午休前 结束时间在午休中
			long diffInMilliseconds = Math.abs(endTime.getTime() - lunchStart.getTime());
			BigDecimal noWork = new BigDecimal(diffInMilliseconds);
			BigDecimal noWorkHours = noWork.divide(hours, 2, RoundingMode.HALF_UP);
			return workHour.subtract(noWorkHours).doubleValue();
		} else {
			return workHour.doubleValue();
		}

	}

	/**
	 * 获取两个日期中的所有日期
	 * 
	 * @param start
	 * @param end
	 * @return
	 */
	private List<Date> getBetweenDates(Date start, Date end) {
		List<Date> result = new ArrayList<Date>();
		Calendar tempStart = Calendar.getInstance();
		tempStart.setTime(start);
		tempStart.add(Calendar.DAY_OF_YEAR, 1);

		Calendar tempEnd = Calendar.getInstance();
		tempEnd.setTime(end);
		while (tempStart.before(tempEnd)) {
			result.add(tempStart.getTime());
			tempStart.add(Calendar.DAY_OF_YEAR, 1);
		}
		return result;
	}

	public ArrayList<String> getcolNamesAction(Date startDate, Date endDate) throws SHRWebException {
		ArrayList<String> list = new ArrayList<String>();
		list.add("StaffID");
		list.add("Name");
		list.add("Title");
		list.add("Academic Title");
		list.add("Program");
		List<String> dateList = this.getDateList(startDate, endDate, "dd/MM");
		for (String localDate : dateList) {
			list.add(localDate);
		}
		return list;
	}

	/**
	 * 
	 * @param staffId
	 *            工号
	 * @param data
	 *            存放转换后的数据
	 * @param type
	 *            假期类型
	 * @param sdf
	 *            yyyy-MM-dd
	 * @param sdf2
	 *            MM/dd
	 * @param start
	 *            请假开始时间 yyyy-MM-dd HH:MM
	 * @param end
	 *            请假结束时间 yyyy-MM-dd HH:MM
	 * @param lunchStart
	 *            午休开始时间 yyyy-MM-dd HH:MM
	 * @param lunchEnd
	 *            午休结束时间 yyyy-MM-dd HH:MM
	 * @return 存放转换后的数据
	 */
	private static void getDate(String staffId, Map<String, Object> data, String type, SimpleDateFormat sdf,
			SimpleDateFormat sdf2, Date start, Date end, Date lunchStart, Date lunchEnd) {
		// 获取请假小时数
		double leaveHoursBetween = getLeaveHoursBetween(start, end, lunchStart, lunchEnd);
		// 请假日期
		String key = sdf2.format(start);
		// 判断是否在同一天
		if (data.containsKey(key)) {
			Map<String, Double> holidayTypeAndHours = (Map<String, Double>) data.get(key);
			// 判断当天是否存在同一假勤类型的请假单 多张请假单 一个类型
			if (holidayTypeAndHours.containsKey(type)) {
				// 获取请假类型的的请假时间
				Double holidayHoursHis = holidayTypeAndHours.get(type);
				holidayTypeAndHours.put(type, holidayHoursHis + leaveHoursBetween);
			} else {
				holidayTypeAndHours = new HashMap<String, Double>();
				holidayTypeAndHours.put(type, leaveHoursBetween);
			}

			data.put(key, holidayTypeAndHours);
		} else {
			Map<String, Double> holidayTypeAndHours = new HashMap<>();
			// key是请假类型 vlues是请假小时数
			holidayTypeAndHours.put(type, leaveHoursBetween);
			// 可以是开始时间的yymm value的key是请假类型 value是请假小时数
			data.put(key, holidayTypeAndHours);
		}
	}

	/**
	 * mapkey是日期 vlue为考勤类型 可能会有多个
	 * 
	 * @param startDate
	 *            // 请假开始时间
	 * @param endDate
	 *            // 请假结束时间
	 * @param checkType
	 *            // 请假类型
	 * @return
	 */

//	public void getCheck(Date startDate, Date endDate, String format, String checkType) {
//		// 获取请假时间和请假结束时间之间的所有日期
//		List<String> dateList = this.getDateList(startDate, endDate, format);
//
//	}

	/**
	 * 获取开始日期和结束日期之间的所有日期
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<String> getDateList(Date startDate, Date endDate, String format) {
		List<String> dateList = new ArrayList<String>();
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format);
		LocalDate startLocal = Instant.ofEpochMilli(startDate.getTime()).atZone(ZoneId.systemDefault()).toLocalDate();
		LocalDate endLocal = Instant.ofEpochMilli(endDate.getTime()).atZone(ZoneId.systemDefault()).toLocalDate();
		// 计算开始日期和结束日期之间的天数
		long daysBetween = ChronoUnit.DAYS.between(startLocal, endLocal);
		for (int i = 0; i <= daysBetween; i++) {
			LocalDate date = startLocal.plusDays(i);
			String tofromat = date.format(formatter);
			dateList.add(tofromat);
		}
		return dateList;
	}

	// 请假类型的转换
	public String getLeaveType(String type) {
		String leavetype = null;
		if (type.equals("年假")) {
			leavetype = "A";
		} else if (type.equals("出差")) {
			leavetype = "B";
		} else {
			leavetype = "O";
		}

		return leavetype;
	}

	public List<Map<String, Object>> getcolModelAction(Date startDate, Date endDate) {
		List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
		modelColList.add(this.buildColModelMap("StaffID", "StaffID", 120));
		modelColList.add(this.buildColModelMap("Name", "Name", 120));
		modelColList.add(this.buildColModelMap("Title", "Title", 120));
		modelColList.add(this.buildColModelMap("Academic Title", "Academic Title", 120));
		modelColList.add(this.buildColModelMap("Program", "Program", 120));
		List<String> dateList = this.getDateList(startDate, endDate, "dd/MM");
		for (String localDate : dateList) {
			String local = localDate;
			modelColList.add(this.buildColModelMap(localDate, localDate, 60));
		}
		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;
	}

	// map存放工号和加勤数据
	public Map<String, Map<String, String>> getAllmap(IRowSet rs, Set<String> numberSet, List<String> fiterDate)
			throws SQLException {
		// key是工号 value存储日期和请假类型
		boolean first = true;
		Map<String, Map<String, String>> numberMap = new HashMap<>();
		while (rs.next()) {
			// key是日期+工号,value是请假类型
			Map<String, String> dateMap = new ConcurrentHashMap<>();
			// Map<String, Object> map = new HashMap<String, Object>();
			// 工号
			String fnumber = rs.getString("StaffID");
			numberSet.add(fnumber);

			// 请假开始时间
			Date fbegintime = rs.getDate("FBEGINTIME");
			// 请假结束时间
			Date fendtime = rs.getDate("FENDTIME");

			// 请假类型
			String atsType = rs.getString("ATSTYPE");

			// 获取请假开始日期和结束日期的所有时间段
			List<String> dateList = this.getDateList(fbegintime, fendtime, "dd/MM");
			// 获取假期类型返回的字符
			String leaveType = this.getLeaveType(atsType);

			// 判断是否是第一次循环 第一次玄幻先添加
			if (first) {
				first = false;
				// map.put("StaffID", fnumber);
				// map.put("Name", name);
				// map.put("Title", fnumber);
				// map.put("Academic Title", academic);
				// map.put("Program", program);
				for (String fiterdate : fiterDate) {

					for (String datelist : dateList) {
						// 过滤条件的日期段和请假时间的日期段比较，判断日期是否在一个时间段内
						if (fiterdate.equals(datelist)) {
							dateMap.put(datelist + fnumber, leaveType);
						}

					}
				}
				numberMap.put(fnumber, dateMap);

			} else {
				// 判断是否是同一个人
				if (numberMap.containsKey(fnumber)) {
					Map<String, String> todatemap = numberMap.get(fnumber);
					synchronized (todatemap) {
						for (Map.Entry<String, String> entry : todatemap.entrySet()) {
							// 截取工号和时间
							String key = entry.getKey();
							// 工号
							String subKey = key.substring(0, 5);
							// 时间
							String value = entry.getValue();
							//String tokey = key.substring(5);
							// 判断是否是同一个人

							for (String datelist : dateList) {
								// 判断是否是同一天
								if (subKey.equals(datelist)) {
									// 如果请假类型不相等就更新vlue
									if (!value.equals(leaveType)) {
										todatemap.put(key, value + leaveType);
									}
									// 如果不是同一天key和value都要更新
								} else {
									todatemap.put(datelist + fnumber, leaveType);
								}
							}

						}
					}
					numberMap.put(fnumber, todatemap);

				} else {
					// 如果不是同一个人

					for (String fiterdate : fiterDate) {

						for (String datelist : dateList) {
							// 过滤条件的日期段和请假时间的日期段比较，判断日期是否在一个时间段内
							if (fiterdate.equals(datelist)) {
								dateMap.put(datelist + fnumber, leaveType);
							}

						}
					}
					numberMap.put(fnumber, dateMap);
				}
			}

		}
		return numberMap;
	}


	private String getSql(Map<String, Object> params) {

		StringBuffer sql = new StringBuffer();
		Map<String, Object> contractDateMap = (Map<String, Object>) params.get("queryTime");
		Object startDate = contractDateMap.get("startDate");
		Object endDate = contractDateMap.get("endDate");
		sql.append(
				"SELECT  b.FPERSONID,b.FBEGINTIME,b.FENDTIME,d.fnumber as StaffID,d.fname_l1 name,e.FName_L1 as Program,c.FName_L1 as Title,p.FName_L1 as AcademicTitle,\r\n"
						+ "f.fnumber holidayTypeNumber,f.fname_l2 as atsType, f.fname_l1 HolidayName FROM T_HR_ATS_LeaveBill a \r\n"
						+ "left join T_HR_ATS_LeaveBillentry b on a.fid=b.fbillid \r\n"
						+ "		left join (select b.fpersonid, d.cflongname_l1 positionName,e.fname_l1 depName  ,at.FName_l1 as AcademicTitle,c.CFAcademicTitleID,d.FName_L1  from \r\n"
						+ "		(SELECT FPERSONID,max(FleFFDT) as maxDate FROM T_HR_EmpOrgRelation where fassignType = '1' group by FPERSONID ) b \r\n"
						+ "		left join T_HR_EmpOrgRelation c on b.fpersonid = c.fpersonid and b.maxdate=FleFFDT \r\n"
						+ "		left join CT_MP_AcademicTitle at on at.fid = c.CFAcademicTitleID\r\n"
						+ "		left join T_ORG_Position d on c.fpositionid=d.fid \r\n"
						+ "		left join T_ORG_BaseUnit e on e.fid = c.FAdminOrgID \r\n"
						+ "		left join CT_MP_WorkerCategory f on f.fid=c.CFWorkercategoryID where c.fassignType = '1' and f.fnumber = 'GTIIT_FACULTY') c \r\n"
						+ "		on b.fpersonid = c.fpersonid \r\n"
						+ "	left join T_BD_Person d on d.fid=b.fpersonid \r\n"
						+ "left join T_HR_ATS_HolidayPolicy e on e.fid=b.fpolicyid \r\n"
						+ "left join T_HR_ATS_HolidayType f on f.fid=e.fholidaytypeid\r\n"
						+ "left join CT_MP_AcademicTitle p on c.CFAcademicTitleID=p.fid");

		sql.append(" where b.FBeginTime>=");
		sql.append("'");
		sql.append(startDate);
		sql.append("'");
		sql.append(" and b.FEndTime<=");
		sql.append("'");
		sql.append(endDate);
		sql.append("'");
		System.out.println(sql.toString());

		return sql.toString();

	}

	// 导出
	public String toExcelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws BOSException, SQLException, SHRWebException, ParseException {
		// 获取报表查询过滤条件
		List<String> qfNames = new ArrayList<>();
		qfNames.add("queryTime");
		String parameter = request.getParameter("fastFilterItems");
		JSONObject paramJson = JSON.parseObject(parameter);
		Map<String, Object> rptSQLFilterate = RptUtils.getRptSQLFilterate(paramJson, qfNames);

		JSONObject queyTimeJson = paramJson.getJSONObject("queryTime");
		String pervalues = queyTimeJson.getString("values");
		String tostartDate = null;
		String toendDate = null;
		if (pervalues != null && !pervalues.equals("")) {
			JSONObject queryTimeValues = queyTimeJson.getJSONObject("values");
			tostartDate = queryTimeValues.getString("startDate");
			toendDate = queryTimeValues.getString("endDate");

		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date startDates = sdf.parse(tostartDate);
		Date endDates = sdf.parse(toendDate);
		// 转换日期格式为dd/MM/yyyy
        SimpleDateFormat tosdf = new SimpleDateFormat("dd/MM/yyyy");  
        String formatstartDate = tosdf.format(startDates);  
        String formatendDate = tosdf.format(endDates); 
		// 获取过滤条件的开始时间和结束时间
		List<String> fiterDate = this.getDateList(startDates, endDates, "dd/MM");
		// 获取报表数据源
		String sqlInfo = getSql(rptSQLFilterate);
		IRowSet rs = DbUtil.executeQuery(this.ctx, sqlInfo);
		
		LinkedHashMap<String, String> selectFieldsTypeMap = new LinkedHashMap<String, String>();
		String stringType = "String_@";
		selectFieldsTypeMap.put("StaffID", stringType);
		selectFieldsTypeMap.put("name", stringType);
		selectFieldsTypeMap.put("title", stringType);
		selectFieldsTypeMap.put("AcademicTitle", stringType);
		selectFieldsTypeMap.put("program", stringType);
		List<String> dateList = this.getDateList(startDates, endDates, "dd/MM");
		for (String localDate : dateList) {
			selectFieldsTypeMap.put(localDate, stringType);
		}
		Set<String>numberSet=new HashSet<>();
		while (rs.next()) {
			// key是日期+工号,value是请假类型
//			Map<String, String> dateMap = new ConcurrentHashMap<>();
			// Map<String, Object> map = new HashMap<String, Object>();
			// 工号
			String fnumber = rs.getString("StaffID");
			numberSet.add(fnumber);
		}
		// 获取对应人员的工号和考勤数据
		rs.beforeFirst();
		Map<String, Map<String, String>> allmap = this.getAllmap(rs, numberSet, fiterDate);
		ArrayList<String> list = this.getcolNamesAction(startDates, endDates);
		ExportAllUtils.morisExportData(response, rs, "FacultyAbsenceReport.xlsx", selectFieldsTypeMap, list,allmap,numberSet,
				formatstartDate,formatendDate);
		return null;

	}

}