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

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

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

import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.json.JSONObject;
import org.slf4j.LoggerFactory;
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.util.ExportAllUtil;
import org.slf4j.Logger;

public class AbsenceSummaryForAllLeaveListHandler extends ListHandler {

    private static final Logger log = LoggerFactory.getLogger(AbsenceSummaryForAllLeaveListHandler.class);

    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;
    }

    /**
     * 构建页面上展示的列字段名
     *
     * @return 列字段名集合
     */
    public List<String> getcolNamesAction() {
        List<String> list = new ArrayList<String>();
        list.add("Dept");
        list.add("Staff No.");
        list.add("Name");
        list.add("Position");
        list.add("Work Category");
        list.add("Person Type");
        list.add("Full Time or Part Time");
        list.add("Hire Date");
        list.add("Ter Date");
        list.add("Status");
        list.add("Annual Entitlement (Days)");
        list.add("Faculty Sick Entitlement (Days)");
        list.add("Faculty Sick Max Entitlement (Days)");
        list.add("Child care Entitlement (Days)");
        list.add("Elderly care Entitlement (Days)");
        list.add("Annual Leave (Days)");
        list.add("Sick Leave (Days)");
        list.add("Marriage Leave (Days)");
        list.add("Paternity Leave (Days)");
        list.add("Maternity Leave (Days)");
        list.add("Bereavement Leave (Days)");
        list.add("Miscarriage Leave (Days)");
        list.add("Leave Of Family Planning Birth Control (Days)");
        list.add("Work-related Injury Leave (Days)");
        list.add("Medical Treatment Leave (Days)");
        list.add("Unpaid Leave (H)");
        list.add("Compensatory Leave (H)");
        list.add("Child Care Leave (Days)");
        list.add("Elderly Care Leave (Days)");
        list.add("Annual Leave Balance (Days)");
        list.add("sick leave balance");
        list.add("Compensatory Leave Balance(H)");
        list.add("Child Care Leave Balance");
        list.add("Elderly Care Leave Balance");
        return list;
    }

    public void getGridDataAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws BOSException, SQLException, SHRWebException, JSONException, org.json.JSONException {
        log.info(">>> Enter AbsenceSummaryForAllLeaveListHandler...getGridDataAction");
        int rows = Integer.parseInt(request.getParameter("rows"));
        int page = Integer.parseInt(request.getParameter("page"));
        // 构建参数写sql去查询数据库
        IRowSet rs = this.getIRowSet(request);
        if (null == rs) {
            return;
        }
        int num = 0;
        List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            ++num;
            if (num >= rows * (page - 1) + 1 && num <= rows * page) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("Dept", rs.getString("Dept"));
                map.put("Staff No.", rs.getString("StaffNo"));
                map.put("Name", rs.getString("Name"));
                map.put("Position", rs.getString("Position"));
                map.put("Work Category", rs.getString("WorkCategory"));
                map.put("Person Type", rs.getString("PersonType"));
                map.put("Full Time or Part Time", rs.getString("FullTimeOrPartTime"));
                map.put("Hire date", StringUtils.isEmpty(rs.getString("HireDate")) ? null : this.getDateString(rs.getString("HireDate")));
                map.put("Ter  Date", StringUtils.isEmpty(rs.getString("TerDate")) ? null : this.getDateString(rs.getString("TerDate")));
                map.put("Status", rs.getString("Status"));
                map.put("Annual Entitlement", rs.getBigDecimal("AnnualEntitlement") != null ? rs.getBigDecimal("AnnualEntitlement").setScale(2, 4) : 0);
                map.put("Faculty Sick Entitlement", rs.getBigDecimal("FacultySickEntitlement") != null ? rs.getBigDecimal("FacultySickEntitlement").setScale(2, 4) : 0);
                map.put("Faculty Sick Max Entitlement", rs.getBigDecimal("FacultySickMaxEntitlement") != null ? rs.getBigDecimal("FacultySickMaxEntitlement").setScale(2, 4) : 0);
                map.put("Child care Entitlement", rs.getBigDecimal("ChildCareEntitlement") != null ? rs.getBigDecimal("ChildCareEntitlement").setScale(2, 4) : 0);
                map.put("Elderly care Entitlement", rs.getBigDecimal("ElderlyCareEntitlement") != null ? rs.getBigDecimal("ElderlyCareEntitlement").setScale(2, 4) : 0);
                map.put("annual leave", rs.getBigDecimal("AnnualLeave") != null ? rs.getBigDecimal("AnnualLeave").setScale(2, 4) : 0);
                map.put("sick leave", rs.getBigDecimal("SickLeave") != null ? rs.getBigDecimal("SickLeave").setScale(2, 4) : 0);
                map.put("marriage leave", rs.getBigDecimal("MarriageLeave") != null ? rs.getBigDecimal("MarriageLeave").setScale(2, 4) : 0);
                map.put("paternity leave", rs.getBigDecimal("PaternityLeave") != null ? rs.getBigDecimal("PaternityLeave").setScale(2, 4) : 0);
                map.put("maternity leave", rs.getBigDecimal("MaternityLeave") != null ? rs.getBigDecimal("MaternityLeave").setScale(2, 4) : 0);
                map.put("bereavement leave", rs.getBigDecimal("BereavementLeave") != null ? rs.getBigDecimal("BereavementLeave").setScale(2, 4) : 0);
                map.put("miscarriage leave", rs.getBigDecimal("MiscarriageLeave") != null ? rs.getBigDecimal("MiscarriageLeave").setScale(2, 4) : 0);
                map.put("leave of family planning birth control", rs.getBigDecimal("LeaveOfFamilyPlanningBirthControl") != null ? rs.getBigDecimal("LeaveOfFamilyPlanningBirthControl").setScale(2, 4) : 0);
                map.put("work-related injury leave", rs.getBigDecimal("WorkRelatedInjuryLeave") != null ? rs.getBigDecimal("WorkRelatedInjuryLeave").setScale(2, 4) : 0);
                map.put("medical treatment leave", rs.getBigDecimal("MedicalTreatmentLeave") != null ? rs.getBigDecimal("MedicalTreatmentLeave").setScale(2, 4) : 0);
                map.put("unpaid leave(H)", rs.getBigDecimal("UnpaidLeaveH") != null ? rs.getBigDecimal("UnpaidLeaveH").setScale(2, 4) : 0);
                map.put("compensatory leave(H)", rs.getBigDecimal("CompensatoryLeaveH") != null ? rs.getBigDecimal("CompensatoryLeaveH").setScale(2, 4) : 0);
                map.put("Child Care Leave", rs.getBigDecimal("ChildCareLeave") != null ? rs.getBigDecimal("ChildCareLeave").setScale(2, 4) : 0);
                map.put("Elderly Care Leave", rs.getBigDecimal("ElderlyCareLeave") != null ? rs.getBigDecimal("ElderlyCareLeave").setScale(2, 4) : 0);
                map.put("Annual Leave Balance", rs.getBigDecimal("AnnualLeaveBalance") != null ? rs.getBigDecimal("AnnualLeaveBalance").setScale(2, 4) : 0);
                map.put("sick leave balance", rs.getBigDecimal("SickLeaveBalance") != null ? rs.getBigDecimal("SickLeaveBalance").setScale(2, 4) : 0);
                map.put("Compensatory Leave Balance(H)", rs.getBigDecimal("CompensatoryLeaveBalanceH") != null ? rs.getBigDecimal("CompensatoryLeaveBalanceH").setScale(2, 4) : 0);
                map.put("Child Care Leave Balance", rs.getBigDecimal("ChildCareLeaveBalance") != null ? rs.getBigDecimal("ChildCareLeaveBalance").setScale(2, 4) : 0);
                map.put("Elderly Care Leave Balance", rs.getBigDecimal("ElderlyCareLeaveBalance") != null ? rs.getBigDecimal("ElderlyCareLeaveBalance").setScale(2, 4) : 0);
                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);
        log.info(">>> complete AbsenceSummaryForAllLeaveListHandler...getGridDataAction!!!");
    }

    /**
     * 构建参数去查询数据库
     *
     * @param request 请求参数
     * @return 数据源
     * @throws BOSException 异常信息
     */
    public IRowSet getIRowSet(HttpServletRequest request) throws BOSException, org.json.JSONException {
        String fastFilterItems = request.getParameter("fastFilterItems");
        JSONObject tofastFilterItems = new JSONObject(fastFilterItems);
        log.info("ASFALH...tofastFilterItems = " + tofastFilterItems);
        JSONObject status = tofastFilterItems.getJSONObject("status");
        log.info("ASFALH...status = " + status);
        String statusValues = status.getString("values");
        log.info("ASFALH...statusValues = " + statusValues);
        JSONObject cutOffDateJSON = tofastFilterItems.getJSONObject("cutOffDate");
        JSONObject cutOffDateValues = cutOffDateJSON.getJSONObject("values");
        if (ObjectUtils.isEmpty(cutOffDateValues)) {
            return null;
        }
        //新增 全职或兼职过滤条件 QW
        JSONObject fullOrPart = tofastFilterItems.getJSONObject("fullOrPart");
        String fullOrPartValues = fullOrPart.getString("values");


        String cutOffDate = cutOffDateValues.getString("date");
        SimpleDateFormat dat = new SimpleDateFormat("yyyy-MM-dd");
        String yearFirstDate = null;
        String year = "";
        try {
            Date cfDate = dat.parse(cutOffDate);
            Calendar ins = Calendar.getInstance();
            ins.setTime(cfDate);
            int yearDate = ins.get(Calendar.YEAR);
            year = String.valueOf(yearDate);
            Date yearFirst = this.getYearFirst(yearDate);
            yearFirstDate = dat.format(yearFirst);
        } catch (ParseException e) {
            // 日期格式不对
            return null;
        }
        // where为空表示不用状态过滤
        String where = "";
        if (ObjectUtils.isNotEmpty(statusValues)) {
            if ("1".equals(statusValues)) {
                // 在职
                where = "  where final.Status='Active' ";
            } else {
                // 离职
                where = "  where final.Status='Negative' ";
            }
        }
        //新增过滤条件 QW
        if (ObjectUtils.isNotEmpty(fullOrPartValues)) {
            if ("1".equals(fullOrPartValues)) {
                // 全职
                where += "   and ftorpt =(select fid from CT_MP_Fullorpart where fnumber = 'FULL') ";
            } else if ("2".equals(fullOrPartValues)) {
                // 兼职
                where += "   and ftorpt =(select fid from CT_MP_Fullorpart where fnumber = 'PART')";
            }
        }
        // 获取时间的过滤条件
        JSONObject queryTime = tofastFilterItems.getJSONObject("queryTime");
        String queryTimeValues = queryTime.getString("values");
        String startDate = null;
        String endDate = null;
        if (ObjectUtils.isNotEmpty(queryTimeValues)) {
            JSONObject periodEmploymentValues = queryTime.getJSONObject("values");
            // 获取日期参数
            startDate = periodEmploymentValues.getString("startDate");
            endDate = periodEmploymentValues.getString("endDate");
            log.info("FASRH...getGridDataAction : startDate = " + startDate + "; endDate = " + endDate);
        }
        // 时间参数为空就不查直接return
        if (null == startDate || null == endDate) {
            return null;
        }
        // 拼接时分秒
        startDate = this.getDateString(startDate) + " 00:00:00";
        endDate = this.getDateString(endDate) + " 23:59:59";
        // 拼接sql
        String sql = getSql(startDate, endDate, cutOffDate, yearFirstDate, year, where);
        log.info("AbsenceSummaryForAllLeaveListHandler...sql---{}", sql);
        log.error("AbsenceSummaryForAllLeaveListHandler...sql---error-"+ sql);
        return DbUtil.executeQuery(this.ctx, sql);
    }

    /**
     * @param startDate     开始时间
     * @param endDate       结束时间
     * @param cutoffDate    查询日期
     * @param firstYearDate 年初
     * @param year          年
     * @param where         条件
     * @return
     */
    private String getSql(String startDate, String endDate, String cutoffDate, String firstYearDate, String year, String where) {
        return "/*dialect*/select final.Dept Dept,\r\n" +
                "final.StaffNo StaffNo,\r\n" +
                "final.Name Name,\r\n" +
                "final.ftorpt ftorpt,\r\n" +//新增字段 QW
                "final.POSITION POSITION,\r\n" +
                "final.WorkCategory WorkCategory,\r\n" +
                "final.PersonType PersonType,\r\n" +
                "final.FullTimeOrPartTime FullTimeOrPartTime,\r\n" +
                "final.HireDate HireDate,\r\n" +
                "final.TerDate TerDate,\r\n" +
                "final.Status Status,\r\n" +
                "case when final.FullTimeOrPartTime='Part Time' then 0 else COALESCE(final.AnnualEntitlement,0) end \r\n" +
                "AnnualEntitlement,\r\n" +
                "case when (final.WorkCategory='General Administrative Staff' or final.WorkCategory='Professional Supervisory Staff' or final.WorkCategory='Senior Administrative Staff') and final.FacultySickEntitlement='0' then 15 else COALESCE(final.FacultySickEntitlement,0) end FacultySickEntitlement,\r\n" +
                "case when (final.WorkCategory='General Administrative Staff' or final.WorkCategory='Professional Supervisory Staff' or final.WorkCategory='Senior Administrative Staff') and final.FacultySickMaxEntitlement='0' then 75 else COALESCE(final.FacultySickMaxEntitlement,0) end FacultySickMaxEntitlement,\r\n" +
                "COALESCE(final.ChildCareEntitlement,0) ChildCareEntitlement,\r\n" +
                "COALESCE(final.ElderlyCareEntitlement,0) ElderlyCareEntitlement,\r\n" +
                "COALESCE(final.AnnualLeave,0)  AnnualLeave,\r\n" +
                "COALESCE(final.SickLeave,0) SickLeave,\r\n" +
                "COALESCE(final.MarriageLeave,0) MarriageLeave,\r\n" +
                "COALESCE(final.PaternityLeave,0) PaternityLeave,\r\n" +
                "COALESCE(final.MaternityLeave,0) MaternityLeave,\r\n" +
                "COALESCE(final.BereavementLeave,0) BereavementLeave,\r\n" +
                "COALESCE(final.MiscarriageLeave,0) MiscarriageLeave,\r\n" +
                "COALESCE(final.LeaveOfFamilyPlanningBirthControl,0) LeaveOfFamilyPlanningBirthControl,\r\n" +
                "COALESCE(final.WorkRelatedInjuryLeave,0) WorkRelatedInjuryLeave,\r\n" +
                "COALESCE(final.MedicalTreatmentLeave,0) MedicalTreatmentLeave,\r\n" +
                "COALESCE(final.UnpaidLeaveH,0) UnpaidLeaveH,\r\n" +
                "COALESCE(final.CompensatoryLeaveH,0) CompensatoryLeaveH,\r\n" +
                "COALESCE(final.ChildCareLeave,0) ChildCareLeave,\r\n" +
                "COALESCE(final.ElderlyCareLeave,0) ElderlyCareLeave,\r\n" +
                "case when final.FullTimeOrPartTime='Part Time' then 0 else COALESCE(final.AnnualLeaveBalance,0) end \r\n" +
                " AnnualLeaveBalance,\r\n" +
                "\r\n" +
                "CASE \r\n" +
                "		WHEN final.WorkCategory= 'Academic Staff' \r\n" +
                "		OR final.WorkCategory= 'Others' THEN\r\n" +
                "			COALESCE ( final.SickLeaveBalance, 0 ) when \r\n" +
                "			(  COALESCE ( final.SickLeaveBalance, 0 ) >case when (final.WorkCategory='General Administrative Staff' or final.WorkCategory='Professional Supervisory Staff' or final.WorkCategory='Senior Administrative Staff') and final.FacultySickMaxEntitlement='0' then 75 else COALESCE(final.FacultySickMaxEntitlement,0) end) then case when (final.WorkCategory='General Administrative Staff' or final.WorkCategory='Professional Supervisory Staff' or final.WorkCategory='Senior Administrative Staff') and final.FacultySickMaxEntitlement='0' then 75 else COALESCE(final.FacultySickMaxEntitlement,0) end \r\n" +
                "			\r\n" +
                "			when ( COALESCE ( final.SickLeaveBalance, 0 ) <=case when (final.WorkCategory='General Administrative Staff' or final.WorkCategory='Professional Supervisory Staff' or final.WorkCategory='Senior Administrative Staff') and final.FacultySickMaxEntitlement='0' then 75 else COALESCE(final.FacultySickMaxEntitlement,0) end) then COALESCE ( final.SickLeaveBalance, 0 ) end SickLeaveBalance,\r\n" +
                "\r\n" +
                "COALESCE(final.CompensatoryLeaveBalanceH,0) CompensatoryLeaveBalanceH,\r\n" +
                "COALESCE(final.ChildCareLeaveBalance,0) ChildCareLeaveBalance,\r\n" +
                "COALESCE(final.ElderlyCareLeaveBalance,0) ElderlyCareLeaveBalance\r\n" +
                "from (\r\n" +
                "select * from \r\n" +
                "(select \r\n" +
                "tbp.fid fpersonid,\r\n" +
                "bcc.fname_l1 AS Dept,\r\n" +
                "	tbp.fnumber AS StaffNo,\r\n" +
                "	tbp.fname_l1 AS Name,\r\n" +
                "	tbp.cfftorptid AS ftorpt,\r\n" +
                "	bd.fname_l1 AS POSITION,\r\n" +
                "	be.fname_l1 AS WorkCategory,\r\n" +
                "	hepc.fname_l1 AS PersonType,\r\n" +
                "	bg.fname_l1 AS FullTimeOrPartTime,\r\n" +
                "	hmrc.joinDate AS HireDate,\r\n" +
                "	bb.fleftdate AS TerDate,\r\n" +
                "\r\n" +
                "  case  when bh.finservice='1' then CAST ('Active' AS VARCHAR ( 10 ))\r\n" +
                "	when bh.finservice='0' then CAST ('Negative' AS VARCHAR ( 10 )) \r\n" +
                "	when bh.finservice='2' then CAST ('Negative' AS VARCHAR ( 10 )) \r\n" +
                "	when bh.finservice='3' then CAST ('Negative' AS VARCHAR ( 10 ))  \r\n" +
                "	when bh.finservice='4' then CAST ('Active' AS VARCHAR ( 10 ))  END AS Status\r\n" +
                " from 	(select * from T_BD_PERSON where fhiredate<='2024-02-25') tbp left join (select bahis.* from T_HR_EmpOrgRelation bahis where bahis.fid in \r\n" +
                "(select hera.fid from  T_HR_EmpOrgRelation hera inner join (select MAX(fleffdt) maxDate,fpersonid fpersonid,fid fid from T_HR_EmpOrgRelation where FIsLatestInAday = '1' and  FAssignType = '1' and (feffdt <='" + cutoffDate + "' and fleffdt>='" + cutoffDate + "')  group by fpersonid,fid) herb on hera.fid=herb.fid)) temp on tbp.fid=temp.fpersonid\r\n" +
                "LEFT JOIN T_ORG_Admin bcc ON bcc.fid= temp.fadminorgid\r\n" +
                "left join T_HR_EmployeeClassify hepc on hepc.fid=temp.cfpersontypeid\r\n" +
                "		LEFT JOIN T_ORG_Position bd ON temp.fpositionid = bd.fid\r\n" +
                "		LEFT JOIN CT_MP_WorkerCategory be ON be.fid = temp.cfworkercategoryid\r\n" +
                "		LEFT JOIN CT_MP_Fullorpart bg ON bg.fid = tbp.CFFtorptID\r\n" +
                "		LEFT JOIN (select hebr.fpersonid fpersonid, bem.finservice finservice from  T_HR_EmpLaborRelation hebr left join T_HR_BDEmployeeType bem on bem.fid=hebr.flaborrelationstateid) bh ON bh.fpersonid = temp.fpersonid\r\n" +
                "		LEFT JOIN (select hpipa.fleftdate fleftdate, hpipa.fpersonid fpersonid from T_HR_PersonPositionHis hpipa inner join (select Max(fleffdt) maxDate,fpersonid fpersonid from  T_HR_PersonPositionHis group by fpersonid) hpipb on hpipa.fleffdt=hpipb.maxDate and hpipa.fpersonid=hpipb.fpersonid\r\n" +
                "	) bb ON temp.fpersonid = bb.fpersonid\r\n" +
                "	left join (select hmra.fpersonid,hmra.fenterdate joinDate  from T_HR_EmpLaborRelationHis hmra inner join  (select Max(fleffdt) maxDate,fpersonid fpersonid from T_HR_EmpLaborRelationHis where fenterdate<='" + cutoffDate + "' group by fpersonid ) hmrb on hmra.fleffdt=hmrb.maxDate and hmra.fpersonid=hmrb.fpersonid) hmrc on hmrc.fpersonid=temp.fpersonid) tep\r\n" +
                "	\r\n" +
                "	left join (\r\n" +
                "	SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(cfentitlementlimit,0) AnnualEntitlement \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "		fcyclebegindate<='" + cutoffDate + "' and fcycleenddate>='" + cutoffDate + "'\r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000001Y' ) \r\n" +
                "			 )\r\n" +
                ") htmab on htmab.fpersonid=tep.fpersonid \r\n" +
                "\r\n" +
                "left join (select hera.fhistoryrelateid fpersonid,hera.cffse FacultySickEntitlement from  T_BD_PersonHis hera inner join (select MAX(fleffdt) maxDate,fhistoryrelateid fpersonid from T_BD_PersonHis where feffdt <='" + cutoffDate + "' and fleffdt>='" + cutoffDate + "'  group by fpersonid) herb on hera.fhistoryrelateid=herb.fpersonid and hera.fleffdt=herb.maxDate) htma on htma.fpersonid=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join(select hera.fhistoryrelateid fpersonid,hera.cffsme FacultySickMaxEntitlement,hera.fleffdt fleffdt  from  T_BD_PersonHis hera inner join (select MAX(fleffdt) maxDate,fhistoryrelateid fpersonid from T_BD_PersonHis where feffdt <='" + cutoffDate + "' and fleffdt>='" + cutoffDate + "'  group by fpersonid) herb on hera.fhistoryrelateid=herb.fpersonid and hera.fleffdt=herb.maxDate) htmb on htmb.fpersonid=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join(SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(sum(fstandardlimit),0) ChildCareEntitlement \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "			fcyclebegindate<='" + cutoffDate + "' and fcycleenddate>='" + cutoffDate + "'\r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000015Y' ) \r\n" +
                "			 ) group by fproposerid) htmc on htmc.fpersonid=tep.fpersonid\r\n" +
                "			 \r\n" +
                "			 left join(SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(sum(fstandardlimit),0) ElderlyCareEntitlement \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "			EXTRACT(YEAR FROM fcyclebegindate)= '" + year + "' \r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000016Y' ) \r\n" +
                "			 ) group by fproposerid) htmd on htmd.fpersonid=tep.fpersonid\r\n" +
                "			 \r\n" +
                "			 left join (	SELECT\r\n" +
                "	tema.FPERSONID,\r\n" +
                "	SUM ( tema.AnnualLeave ) AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "	SELECT\r\n" +
                "		tem.fpersonId FPERSONID,\r\n" +
                "		tem.leavedate leavedate,\r\n" +
                "	CASE\r\n" +
                "			\r\n" +
                "			WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "			SUM ( tem.fleavelength ) \r\n" +
                "			WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "			SUM ( tem.cfleavelenght ) \r\n" +
                "		END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "	distinct	a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			b.fleavedate leavedate,\r\n" +
                "			 b.fleavelength  fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000001Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "		distinct\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			c.cfleavedate leavedate,\r\n" +
                "			0 fleavelength,\r\n" +
                "			c.cfleavelenght  cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000001Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId,\r\n" +
                "tem.leavedate	\r\n" +
                "	) tema \r\n" +
                "GROUP BY\r\n" +
                "	tema.FPERSONID ) htme on htme.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join (	select tema.FPERSONID  FPERSONID,sum(tema.SickLeave) SickLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END SickLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000003Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000003Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID ) htmf on htmf.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join(	select tema.FPERSONID FPERSONID , sum(tema.MarriageLeave) MarriageLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END MarriageLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000005Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000005Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID ) htmh on htmh.FPERSONID=tep.fpersonid\r\n" +
                "	left join(	select tema.FPERSONID FPERSONID,sum(tema.PaternityLeave) PaternityLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END PaternityLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000007Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000007Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmi on htmi.FPERSONID=tep.fpersonid\r\n" +
                "	left join(	select tema.FPERSONID FPERSONID,sum(tema.MaternityLeave) MaternityLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END MaternityLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000006Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000006Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmj on htmj.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join(	select tema.FPERSONID FPERSONID,sum(tema.BereavementLeave) BereavementLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END BereavementLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000011Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000011Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmk on htmk.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join( select tema.FPERSONID FPERSONID,sum(tema.MiscarriageLeave) MiscarriageLeave from (	SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END MiscarriageLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000013Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000013Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) html on html.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join (	select tema.FPERSONID FPERSONID, sum(tema.LeaveOfFamilyPlanningBirthControl) LeaveOfFamilyPlanningBirthControl from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END LeaveOfFamilyPlanningBirthControl \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000008Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000008Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmm on htmm.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join ( select tema.FPERSONID FPERSONID, sum(tema.WorkRelatedInjuryLeave) WorkRelatedInjuryLeave from(	SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END WorkRelatedInjuryLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000009Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000009Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmn on htmn.FPERSONID=tep.fpersonid\r\n" +
                "	left join(	select tema.FPERSONID FPERSONID, sum(tema.MedicalTreatmentLeave) MedicalTreatmentLeave from(SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END MedicalTreatmentLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000012Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000012Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmo on htmo.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join( select tema.FPERSONID FPERSONID,sum(tema.UnpaidLeaveH) UnpaidLeaveH from (	SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END UnpaidLeaveH \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000002Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000002Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmp on htmp.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join (	select tema.FPERSONID FPERSONID, sum(tema.CompensatoryLeaveH) CompensatoryLeaveH from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END CompensatoryLeaveH \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000004Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000004Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) htmq on htmq.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join (SELECT\r\n" +
                "	tema.FPERSONID,\r\n" +
                "	SUM ( tema.AnnualLeave ) ChildCareLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "	SELECT\r\n" +
                "		tem.fpersonId FPERSONID,\r\n" +
                "		tem.leavedate leavedate,\r\n" +
                "	CASE\r\n" +
                "			\r\n" +
                "			WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "			SUM ( tem.fleavelength ) \r\n" +
                "			WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "			SUM ( tem.cfleavelenght ) \r\n" +
                "		END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "	distinct	a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			b.fleavedate leavedate,\r\n" +
                "			 b.fleavelength  fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000015Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "		distinct\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			c.cfleavedate leavedate,\r\n" +
                "			0 fleavelength,\r\n" +
                "			c.cfleavelenght  cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000015Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId,\r\n" +
                "tem.leavedate	\r\n" +
                "	) tema \r\n" +
                "GROUP BY\r\n" +
                "	tema.FPERSONID) htmqa on htmqa.FPERSONID=tep.fpersonid\r\n" +
                "		left join (SELECT\r\n" +
                "	tema.FPERSONID,\r\n" +
                "	SUM ( tema.AnnualLeave ) ElderlyCareLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "	SELECT\r\n" +
                "		tem.fpersonId FPERSONID,\r\n" +
                "		tem.leavedate leavedate,\r\n" +
                "	CASE\r\n" +
                "			\r\n" +
                "			WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "			SUM ( tem.fleavelength ) \r\n" +
                "			WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "			SUM ( tem.cfleavelenght ) \r\n" +
                "		END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "	distinct	a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			b.fleavedate leavedate,\r\n" +
                "			 b.fleavelength  fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000016Y' ) \r\n" +
                "			AND b.fleavedate >= '" + startDate + "' \r\n" +
                "			AND b.fleavedate <= '" + endDate + "' \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "		distinct\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			c.cfleavedate leavedate,\r\n" +
                "			0 fleavelength,\r\n" +
                "			c.cfleavelenght  cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000016Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + startDate + "' \r\n" +
                "			AND cfleavedate <= '" + endDate + "' \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId,\r\n" +
                "tem.leavedate	\r\n" +
                "	) tema \r\n" +
                "GROUP BY\r\n" +
                "	tema.FPERSONID) htmqb on htmqb.FPERSONID=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join(select tempa.fpersonid fpersonid,COALESCE(tempa.freallimit,0)-COALESCE(tempb.AnnualLeave,0) AnnualLeaveBalance from (select tbp.fid fpersonid,COALESCE(temp.freallimit,0) freallimit  from T_BD_PERSON tbp left join (\r\n" +
                "SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(freallimit,0) freallimit \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "			fcyclebegindate<='" + cutoffDate + "' and fcycleenddate>='" + cutoffDate + "'\r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000001Y' ) \r\n" +
                "			 )) temp on tbp.fid=temp.fpersonid) tempa left join ( SELECT\r\n" +
                "	tema.FPERSONID,\r\n" +
                "	SUM ( tema.AnnualLeave ) AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "	SELECT\r\n" +
                "		tem.fpersonId FPERSONID,\r\n" +
                "		tem.leavedate leavedate,\r\n" +
                "	CASE\r\n" +
                "			\r\n" +
                "			WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "			SUM ( tem.fleavelength ) \r\n" +
                "			WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "			SUM ( tem.cfleavelenght ) \r\n" +
                "		END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "	distinct	a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			b.fleavedate leavedate,\r\n" +
                "			 b.fleavelength  fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000001Y' ) \r\n" +
                "			AND b.fleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND b.fleavedate <= '" + cutoffDate + "' \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "		distinct\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			c.cfleavedate leavedate,\r\n" +
                "			0 fleavelength,\r\n" +
                "			c.cfleavelenght  cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000001Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND cfleavedate <= '" + cutoffDate + "' \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId,\r\n" +
                "tem.leavedate	\r\n" +
                "	) tema \r\n" +
                "GROUP BY\r\n" +
                "	tema.FPERSONID ) tempb on tempa.fpersonid=tempb.FPERSONID\r\n" +
                ") htmw on htmw.fpersonid=tep.fpersonid\r\n" +
                "\r\n" +
                "left join(select tempa.fpersonid fpersonid,COALESCE(tempa.FacultySickEntitlement,0)-COALESCE(tempb.SickLeave,0) SickLeaveBalance from (select tbp.fid fpersonid,COALESCE(sick.limitCount,0) FacultySickEntitlement from T_BD_PERSON tbp left join (SELECT\r\n" +
                "	cfpersonid fpersonid,\r\n" +
                "	SUM (\r\n" +
                "	COALESCE ( cfinitiallimit, 0 ) + COALESCE ( cfrealitygrantlimit, 0 )) limitCount \r\n" +
                "FROM\r\n" +
                "	CT_CUS_SickLeaveLimit \r\n" +
                "WHERE\r\n" +
                "	cfmonth <= date_trunc('month', '" + cutoffDate + "'::DATE) - INTERVAL '1 day'\r\n" +
                "GROUP BY\r\n" +
                "	cfpersonid) sick on sick.fpersonid=tbp.fid) tempa \r\n" +
                "	left join(select tema.FPERSONID FPERSONID,sum(tema.SickLeave) SickLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END SickLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000003Y' ) \r\n" +
                "			AND b.fleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000003Y' ) \r\n" +
                "			AND cfleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID) tempb on tempb.FPERSONID=tempa.fpersonid) htmx on htmx.fpersonid=tep.fpersonid\r\n" +
                "	\r\n" +
                "	left join(select tempa.fpersonid fpersonid, COALESCE(tempa.CompensatoryLeava)-COALESCE(tempb.AnnualLeave,0) CompensatoryLeaveBalanceH from (select tbp.fid fpersonid,COALESCE(child.CompensatoryLeava,0) CompensatoryLeava from T_BD_PERSON tbp left join(SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(freallimit,0) CompensatoryLeava \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "			EXTRACT(YEAR FROM fcyclebegindate)= '" + year + "'\r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000004Y' ) \r\n" +
                "			 )) child on child.fpersonid=tbp.fid) tempa left join( select tema.FPERSONID FPERSONID,sum(tema.AnnualLeave)  AnnualLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000004Y' ) \r\n" +
                "			AND b.fleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND b.fleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000004Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND cfleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID\r\n" +
                ") tempb on tempb.FPERSONID=tempa.fpersonid) htmy on htmy.fpersonid=tep.fpersonid\r\n" +
                "\r\n" +
                "left join(select tempa.fpersonid fpersonid, COALESCE(tempa.ChildCareLeave)-COALESCE(tempb.AnnualLeave,0) ChildCareLeaveBalance from (select tbp.fid fpersonid,COALESCE(child.ChildCareLeave,0) ChildCareLeave from T_BD_PERSON tbp left join(SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(sum(freallimit),0) ChildCareLeave \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "				(fcyclebegindate, fcycleenddate) overlaps (to_date('" + firstYearDate + "','yyyy-MM-dd'), to_date('" + cutoffDate + "','yyyy-MM-dd'))\r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000015Y' ) \r\n" +
                "			 ) group by fproposerid) child on child.fpersonid=tbp.fid) tempa left join( select tema.FPERSONID FPERSONID,sum(tema.AnnualLeave) AnnualLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000015Y' ) \r\n" +
                "			AND b.fleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND b.fleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000015Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND cfleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID\r\n" +
                ") tempb on tempb.FPERSONID=tempa.fpersonid) htmz on htmz.fpersonid=tep.fpersonid\r\n" +
                "\r\n" +
                "left join(select tempa.fpersonid fpersonid, COALESCE(tempa.ElderlyCareLeave)-COALESCE(tempb.AnnualLeave,0) ElderlyCareLeaveBalance from (select tbp.fid fpersonid,COALESCE(child.ElderlyCareLeave,0) ElderlyCareLeave from T_BD_PERSON tbp left join(SELECT\r\n" +
                "			fproposerid fpersonid,\r\n" +
                "			COALESCE(sum(freallimit),0) ElderlyCareLeave \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_HolidayLimit \r\n" +
                "		WHERE\r\n" +
                "					(fcyclebegindate, fcycleenddate) overlaps (to_date('" + firstYearDate + "','yyyy-MM-dd'), to_date('" + cutoffDate + "','yyyy-MM-dd'))\r\n" +
                "			AND fstatus = '1' \r\n" +
                "			AND (\r\n" +
                "				fholidaypolicyid IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000016Y' ) \r\n" +
                "			 ) group by fproposerid) child on child.fpersonid=tbp.fid) tempa left join( select tema.FPERSONID FPERSONID,sum(tema.AnnualLeave) AnnualLeave from (SELECT\r\n" +
                "	tem.fpersonId FPERSONID,\r\n" +
                "CASE\r\n" +
                "		\r\n" +
                "		WHEN tem.status IN ( '3', '4' ) THEN\r\n" +
                "		SUM ( tem.fleavelength ) \r\n" +
                "		WHEN tem.status IN ( '1', '2' ) THEN\r\n" +
                "		SUM ( tem.cfleavelenght ) \r\n" +
                "	END AnnualLeave \r\n" +
                "FROM\r\n" +
                "	(\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			b.fproposerid fpersonId,\r\n" +
                "			SUM ( b.fleavelength ) fleavelength,\r\n" +
                "			0 cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN T_HR_ATS_LeaveBillDetail b ON b.FLEAVEBILLID = a.fid \r\n" +
                "		WHERE\r\n" +
                "			b.FPOLICYID IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000016Y' ) \r\n" +
                "			AND b.fleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND b.fleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			b.fproposerid \r\n" +
                "		) UNION\r\n" +
                "		(\r\n" +
                "		SELECT\r\n" +
                "			a.fbillstate status,\r\n" +
                "			c.cfproposer fpersonId,\r\n" +
                "			0 fleavelength,\r\n" +
                "			SUM ( c.cfleavelenght ) cfleavelenght \r\n" +
                "		FROM\r\n" +
                "			T_HR_ATS_LeaveBill a\r\n" +
                "			LEFT JOIN CT_ATS_AtsLeaveBillDetailSame c ON c.cfleavebill= a.fid \r\n" +
                "		WHERE\r\n" +
                "			c.cfpolicy IN ( SELECT ua.fid FROM T_HR_ATS_HolidayPolicy ua LEFT JOIN T_HR_ATS_HolidayType ub ON ub.FID = ua.FHOLIDAYTYPEID WHERE ub.FNUMBER = 'JQLX000016Y' ) \r\n" +
                "			AND c.cfleavedate >= '" + firstYearDate + "' \r\n" +
                "			AND cfleavedate <= '" + cutoffDate + "' \r\n" +
                "		GROUP BY\r\n" +
                "			a.fbillstate,\r\n" +
                "			c.cfproposer \r\n" +
                "		) \r\n" +
                "	) tem \r\n" +
                "GROUP BY\r\n" +
                "	tem.status,\r\n" +
                "	tem.fpersonId) tema group by tema.FPERSONID\r\n" +
                ") tempb on tempb.FPERSONID=tempa.fpersonid) htmma on htmma.fpersonid=tep.fpersonid\r\n" +
                "\r\n" +
                ") final " + where;
    }

    public String getDateString(String dateStr) {
        return dateStr.substring(0, 10).toString();
    }

    public List<Map<String, Object>> getcolModelAction() {
        List<Map<String, Object>> modelColList = new ArrayList<Map<String, Object>>();
        modelColList.add(this.buildColModelMap("Dept", "Dept", 120));
        modelColList.add(this.buildColModelMap("Staff No.", "Staff No.", 120));
        modelColList.add(this.buildColModelMap("Name", "Name", 120));
        modelColList.add(this.buildColModelMap("Position", "Position", 120));
        modelColList.add(this.buildColModelMap("Work Category", "Work Category", 120));
        modelColList.add(this.buildColModelMap("Person Type", "Person Type", 120));
        modelColList.add(this.buildColModelMap("Full Time or Part Time", "Full Time or Part Time", 120));
        modelColList.add(this.buildColModelMap("Hire date", "Hire date", 120));
        modelColList.add(this.buildColModelMap("Ter  Date", "Ter  Date", 120));
        modelColList.add(this.buildColModelMap("Status", "Status", 120));
        modelColList.add(this.buildColModelMap("Annual Entitlement", "Annual Entitlement", 120));
        modelColList.add(this.buildColModelMap("Faculty Sick Entitlement", "Faculty Sick Entitlement", 120));
        modelColList.add(this.buildColModelMap("Faculty Sick Max Entitlement", "Faculty Sick Max Entitlement", 120));
        modelColList.add(this.buildColModelMap("Child care Entitlement", "Child care Entitlement", 120));
        modelColList.add(this.buildColModelMap("Elderly care Entitlement", "Elderly care Entitlement", 120));
        modelColList.add(this.buildColModelMap("annual leave", "annual leave", 120));
        modelColList.add(this.buildColModelMap("sick leave", "sick leave", 120));
        modelColList.add(this.buildColModelMap("marriage leave", "marriage leave", 120));
        modelColList.add(this.buildColModelMap("paternity leave", "paternity leave", 120));
        modelColList.add(this.buildColModelMap("maternity leave", "maternity leave", 120));
        modelColList.add(this.buildColModelMap("bereavement leave", "bereavement leave", 120));
        modelColList.add(this.buildColModelMap("miscarriage leave", "miscarriage leave", 120));
        modelColList.add(this.buildColModelMap("leave of family planning birth control", "leave of family planning birth control", 120));
        modelColList.add(this.buildColModelMap("work-related injury leave", "work-related injury leave", 120));
        modelColList.add(this.buildColModelMap("medical treatment leave", "medical treatment leave", 120));
        modelColList.add(this.buildColModelMap("unpaid leave(H)", "unpaid leave(H)", 120));
        modelColList.add(this.buildColModelMap("compensatory leave(H)", "compensatory leave(H)", 120));
        modelColList.add(this.buildColModelMap("Child Care Leave", "Child Care Leave", 120));
        modelColList.add(this.buildColModelMap("Elderly Care Leave", "Elderly Care Leave", 120));
        modelColList.add(this.buildColModelMap("Annual Leave Balance", "Annual Leave Balance", 120));
        modelColList.add(this.buildColModelMap("sick leave balance", "sick leave balance", 120));
        modelColList.add(this.buildColModelMap("Compensatory Leave Balance(H)", "Compensatory Leave Balance(H)", 120));
        modelColList.add(this.buildColModelMap("Child Care Leave Balance", "Child Care Leave Balance", 120));
        modelColList.add(this.buildColModelMap("Elderly Care Leave Balance", "Elderly Care Leave Balance", 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 toExcelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws BOSException, SQLException, SHRWebException, org.json.JSONException {
        // 获取数据库查询出来的数据源
        IRowSet rs = this.getIRowSet(request);
        if (null == rs) {
            return null;
        }
        LinkedHashMap<String, String> selectFieldsTypeMap = new LinkedHashMap<String, String>();
        String stringType = "String_@";
        String stringDateType = "StringDate_@";
        String numberType = "Number_.";
        selectFieldsTypeMap.put("Dept", stringType);
        selectFieldsTypeMap.put("StaffNo", stringType);
        selectFieldsTypeMap.put("Name", stringType);
        selectFieldsTypeMap.put("Position", stringType);
        selectFieldsTypeMap.put("WorkCategory", stringType);
        selectFieldsTypeMap.put("PersonType", stringType);
        selectFieldsTypeMap.put("FullTimeOrPartTime", stringType);
        selectFieldsTypeMap.put("HireDate", stringDateType);
        selectFieldsTypeMap.put("TerDate", stringDateType);
        selectFieldsTypeMap.put("Status", stringType);
        selectFieldsTypeMap.put("AnnualEntitlement", numberType);
        selectFieldsTypeMap.put("FacultySickEntitlement", numberType);
        selectFieldsTypeMap.put("FacultySickMaxEntitlement", numberType);
        selectFieldsTypeMap.put("ChildCareEntitlement", numberType);
        selectFieldsTypeMap.put("ElderlyCareEntitlement", numberType);
        selectFieldsTypeMap.put("AnnualLeave", numberType);
        selectFieldsTypeMap.put("SickLeave", numberType);
        selectFieldsTypeMap.put("MarriageLeave", numberType);
        selectFieldsTypeMap.put("PaternityLeave", numberType);
        selectFieldsTypeMap.put("MaternityLeave", numberType);
        selectFieldsTypeMap.put("BereavementLeave", numberType);
        selectFieldsTypeMap.put("MiscarriageLeave", numberType);
        selectFieldsTypeMap.put("LeaveOfFamilyPlanningBirthControl", numberType);
        selectFieldsTypeMap.put("WorkRelatedInjuryLeave", numberType);
        selectFieldsTypeMap.put("MedicalTreatmentLeave", numberType);
        selectFieldsTypeMap.put("UnpaidLeaveH", numberType);
        selectFieldsTypeMap.put("CompensatoryLeaveH", numberType);
        selectFieldsTypeMap.put("ChildCareLeave", numberType);
        selectFieldsTypeMap.put("ElderlyCareLeave", numberType);
        selectFieldsTypeMap.put("AnnualLeaveBalance", numberType);
        selectFieldsTypeMap.put("SickLeaveBalance", numberType);
        selectFieldsTypeMap.put("CompensatoryLeaveBalanceH", numberType);
        selectFieldsTypeMap.put("ChildCareLeaveBalance", numberType);
        selectFieldsTypeMap.put("ElderlyCareLeaveBalance", numberType);
        List<String> list = this.getcolNamesAction();
        ExportAllUtil.exportData(response, rs, "AbsenceSummaryForAllLeaveList.xlsx", selectFieldsTypeMap, list);
        return null;
    }

    /**
     * 获取某年第一天日期
     *
     * @param year 年份
     * @return Date
     */
    public Date getYearFirst(int year) {
        Calendar calendar = Calendar.getInstance();
        calendar.clear();
        calendar.set(Calendar.YEAR, year);
        return calendar.getTime();
    }

    /**
     * 获取某年最后一天日期
     *
     * @param year 年份
     * @return Date
     */
    public Date getYearLast(int year) {
        Calendar calendar = Calendar.getInstance();
        calendar.clear();
        calendar.set(Calendar.YEAR, year);
        calendar.roll(Calendar.DAY_OF_YEAR, -1);
        return calendar.getTime();
    }
}
