guangzhouyiselie/GDYSL/websrc/com/kingdee/shr/customer/gtiit/handler/BatchSubmitShemeBillMySubmissionEditHandlerEx.java

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

import java.math.BigDecimal;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

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

import com.kingdee.bos.dao.IObjectCollection;
import com.kingdee.bos.dao.IObjectValue;
import com.kingdee.bos.framework.DynamicObjectFactory;
import com.kingdee.bos.framework.IDynamicObject;
import com.kingdee.bos.metadata.data.SortType;
import com.kingdee.bos.metadata.entity.*;
import com.kingdee.bos.util.BOSObjectType;
import com.kingdee.eas.basedata.person.IPerson;
import com.kingdee.eas.basedata.person.PersonFactory;
import com.kingdee.eas.basedata.person.PersonInfo;
import com.kingdee.eas.framework.DataBaseInfo;
import com.kingdee.shr.compensation.CmpEmpORelationCollection;
import com.kingdee.shr.compensation.CmpEmpORelationFactory;
import com.kingdee.shr.compensation.CmpEmpORelationInfo;
import com.kingdee.shr.compensation.ICmpEmpORelation;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.ui.ModelMap;

import com.google.common.collect.Maps;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.dao.ormapping.ObjectUuidPK;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.framework.CoreBaseInfo;
import com.kingdee.eas.util.app.ContextUtil;
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.exception.ShrWebBizException;
import com.kingdee.shr.base.syssetting.web.json.JSONUtils;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillEntryCollection;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillEntryInfo;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillFactory;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillInfo;
import com.kingdee.shr.compensation.app.integrate.CalSubmitSchemeFactory;
import com.kingdee.shr.compensation.app.integrate.CalSubmitSchemeInfo;
import com.kingdee.shr.compensation.util.columnModel.CmpColumnModels;
import com.kingdee.shr.compensation.web.handler.integrate.BatchSubmitShemeBillMySubmissionEditHandler;
import com.kingdee.shr.customer.gtiit.util.DateTimeUtils;
import com.kingdee.bos.metadata.query.util.CompareType;


/**
 * 扩展员工自助-我要提报表单handler
 *
 * @author xiaoxin
 */
public class BatchSubmitShemeBillMySubmissionEditHandlerEx extends BatchSubmitShemeBillMySubmissionEditHandler {
    private static Logger logger =
            Logger.getLogger("com.kingdee.shr.customer.gtiit.handler.BatchSubmitShemeBillMySubmissionEditHandlerEx");

    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    /**
     * 获取开始结束时间
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws BOSException
     * @throws SHRWebException
     */
    public void getDateTimeAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws BOSException, SHRWebException {
        String personId = request.getParameter("personId");
        String type = request.getParameter("type");
        String adminOrgId = request.getParameter("adminOrgId");
        String positionId = request.getParameter("positionId");
        String hourlywage = request.getParameter("hourlywage");
        Context ctx = SHRContext.getInstance().getContext();
        if (StringUtils.isEmpty(personId) || StringUtils.isEmpty(type)) {
            throw new ShrWebBizException("员工编码或任职类型不能为空！！");
        }
        //返回信息
        Map map = new HashMap();
        //员工信息
        IPerson iPerson = PersonFactory.getLocalInstance(ctx);
        IDynamicObject iDynamicObject = DynamicObjectFactory.getLocalInstance(ctx);
        try {
            PersonInfo personInfo = iPerson.getPersonInfo(new ObjectUuidPK(personId));
            //主要任职
            if (type.equals("Full")) {
                //EE23D375=全职或兼职
                DataBaseInfo ftorptInfo = (DataBaseInfo) personInfo.get("ftorpt");
                if (ftorptInfo == null) {
                    throw new ShrWebBizException("请在员工信息维护全职或兼职字段！！");
                }
                IObjectValue ftorpt = iDynamicObject.getValue(BOSObjectType.create("EE23D375"), new ObjectUuidPK(ftorptInfo.getId()));
                //入职日期
                Date hireDate = personInfo.getHireDate();
                map.put("startDate", sdf.format(hireDate));
                if (ftorpt.get("number").equals("FULL")) {
                    //全职  获取入职日期以及合同信息最新的结束日期   D8595360=合同信息
                    //查询条件
                    FilterInfo filterInfo = new FilterInfo();
                    FilterItemCollection filterItems = filterInfo.getFilterItems();
                    filterItems.add(new FilterItemInfo("person", personId));
                    //排序
                    SorterItemCollection sc = new SorterItemCollection();
                    SorterItemInfo sorterItem = new SorterItemInfo("ExpiredDate");
                    sorterItem.setSortType(SortType.DESCEND);
                    sc.add(sorterItem);
                    //排序获取第一条最新的数据
                    EntityViewInfo entityViewInfo = EntityViewInfo.getInstance(filterInfo, null, sc);
                    IObjectCollection collection = iDynamicObject.getCollection(BOSObjectType.create("D8595360"), entityViewInfo);
                    //结束时间
                    Date expiredDate = null;
                    if (collection.size() > 0) {
                        IObjectValue pcontractinfo = collection.getObject(0);
                        expiredDate = (Date) pcontractinfo.get("ExpiredDate");

                    }
                    map.put("endDate", expiredDate == null ? "" : sdf.format(expiredDate));
                } else if (ftorpt.get("number").equals("PART")) {
                    //兼职  获取入职日期以及员工信息兼职类人员任职结束时间
                    Date jobEndTime = (Date) personInfo.get("jobEndTime");
                    map.put("endDate", jobEndTime == null ? "" : sdf.format(jobEndTime));
                }
            } else if (type.equals("Part")) {//兼职任职
                if (StringUtils.isEmpty(adminOrgId) || StringUtils.isEmpty(positionId)) {
                    throw new ShrWebBizException("组织ID或职位ID不能为空！！");
                }
                //发薪档案任职分录
                ICmpEmpORelation iCmpEmpORelation = CmpEmpORelationFactory.getLocalInstance(ctx);
                FilterInfo filterInfo = new FilterInfo();
                FilterItemCollection filterItems = filterInfo.getFilterItems();
                filterItems.add(new FilterItemInfo("empOrgRelation.person", personInfo.getId().toString()));
                filterItems.add(new FilterItemInfo("empOrgRelation.adminOrg", adminOrgId));
                filterItems.add(new FilterItemInfo("empOrgRelation.position", positionId));
                filterItems.add(new FilterItemInfo("empOrgRelation.hourlywage", StringUtils.isEmpty(hourlywage) ? null : hourlywage));
                EntityViewInfo entityViewInfo = EntityViewInfo.getInstance(filterInfo, null, null);
                logger.error("发薪档案任职分录查询person:" + personInfo.getId().toString() + ";adminOrg:" + adminOrgId + "; position:" + positionId + "; hourlywage:" + hourlywage);


                CmpEmpORelationCollection cmpEmpORelationCollection = iCmpEmpORelation.getCmpEmpORelationCollection(entityViewInfo);
                logger.error("查询到CmpEmpORelationCollection数量:" + cmpEmpORelationCollection.size());
                if (cmpEmpORelationCollection.size() > 1) {
                    throw new ShrWebBizException("员工任职信息数据异常，请排查是否存在多条[人员、组织、职位、时薪]数据！！");
                }
                if (null != cmpEmpORelationCollection && cmpEmpORelationCollection.size() == 1) {
                    CmpEmpORelationInfo cmpEmpORelationInfo = cmpEmpORelationCollection.get(0);
                    //生效日期
                    Date effectDate = cmpEmpORelationInfo.getEffectDate();
                    //失效日期
                    Date leffectDate = cmpEmpORelationInfo.getLeffectDate();
                    map.put("startDate", sdf.format(effectDate));
                    map.put("endDate", sdf.format(leffectDate));
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new ShrWebBizException(e);
        }
        JSONUtils.writeJson(response, map);
    }


    @Override
    public String getEditGridColModelAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws SHRWebException {
        String submitSchemeId = request.getParameter("submitSchemeId");
        if (StringUtils.isEmpty(submitSchemeId)) {
            JSONUtils.writeJson(response, modelMap);
            return null;
        } else {
            boolean dynamicColmunRequired = this.isDynamicColmunRequired(request);
            Map<String, Object> params = Maps.newHashMap();
            params.put("datasource", Integer.valueOf(request.getParameter("datasource")));
            params.put("costTypeId", request.getParameter("costTypeId"));
            params.put("dynamicColmunRequired", dynamicColmunRequired);
            params.put("hrOrgUnitId", request.getParameter("hrOrgUnitId"));
            Context ctx = SHRContext.getInstance().getContext();
            CmpColumnModels cmpColumnModels = new BaseSubmitBillEntryGenerator().getEntryColumnModels(ctx, submitSchemeId, params);
            modelMap.put("colNames", cmpColumnModels.getColNames());
            modelMap.put("colModel", cmpColumnModels.getColModel());
            modelMap.put("defaultSortname", (Object) null);

            CalSubmitSchemeInfo schemeInfo;
            try {
                schemeInfo = CalSubmitSchemeFactory.getLocalInstance(ctx).getCalSubmitSchemeInfo(new ObjectUuidPK(submitSchemeId));
            } catch (EASBizException var11) {
                throw new ShrWebBizException(var11.getMessage(), var11);
            } catch (BOSException var12) {
                throw new SHRWebException(var12.getMessage(), var12);
            }

            modelMap.put("description", schemeInfo.getDescription());
            JSONUtils.writeJson(response, modelMap);
            return null;
        }
    }

    @Override
    protected void beforeSubmit(HttpServletRequest request, HttpServletResponse response, CoreBaseInfo model)
            throws SHRWebException {
        super.beforeSubmit(request, response, model);
        Context ctx = SHRContext.getInstance().getContext();
        BatchSubmitShemeBillInfo info = (BatchSubmitShemeBillInfo) model;
        if (StringUtils.isNotBlank(info.getString("id"))) {
            try {
                info = BatchSubmitShemeBillFactory.getLocalInstance(ctx).getBatchSubmitShemeBillInfo(new ObjectUuidPK(info.getString("id")));
            } catch (EASBizException e) {
                e.printStackTrace();
            } catch (BOSException e) {
                e.printStackTrace();
            }
        }
        String sql = "select cfistakecheck,CFIsCurrentDepart from T_HR_SCalSubmitScheme where fid = '" + info.getSubmitScheme().getId().toString() + "'";
        //是否参与校验
        boolean isPlanCheck = false;
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
            while (iRowSet.next()) {
                if (StringUtils.equals("1", iRowSet.getString("cfistakecheck"))) {
                    isPlanCheck = true;
                }
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        checkPosition(info);
        checkDateOverlap(info);
        if (isPlanCheck) {
            checkPositionCount(info);
            checkHours(info);
        }
    }

    /**
     * 校验职位不允许提交法定节假日那天
     *
     * @param info
     * @throws ShrWebBizException
     */
    public void checkPosition(BatchSubmitShemeBillInfo info) throws ShrWebBizException {
        Context ctx = SHRContext.getInstance().getContext();
        String billId = info.getId().toString();
        String sql = "select b.FNAME_L1 from T_HR_SchemeBillEntry a left join T_ORG_Position b on a.FPOSITIONID = b.FID "
                + "where a.FBILLID = '" + billId + "' "
                + "and b.FNAME_L1 = (SELECT fname_l2 FROM T_HR_SHRRSVITEM0 where fstate = '1' and FNUMBER = '1007') "
                + "and EXISTS (SELECT d.CFWORKMULTIPLE FROM T_HR_ATS_LegalHoliday c "
                + "left join T_HR_ATS_LegalHolidayItem d on c.fid = d.FGROUPID where c.FSTATE = '1' "
                + "and d.CFWORKMULTIPLE = '3' and d.FSTARTDATE <= a.feffectdate and d.FENDDATE >= a.feffectdate)";
        logger.error("查询职位对应日期是否在法定节假日SQL：" + sql);
        try {
            boolean flag = false;
            String positionName = "";
            IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
            while (rowSet.next()) {
                flag = true;
                positionName = rowSet.getString("FNAME_L1");
            }
            if (flag) {
                throw new ShrWebBizException("This position (" + positionName + ") does not allow submission of documents that occur on statutory holidays");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (BOSException e) {
            e.printStackTrace();
        }
    }

    /**
     * 校验该员工本月的开始时间和结束时间有没有重叠
     *
     * @param info
     * @throws ShrWebBizException
     */
    public void checkDateOverlap(BatchSubmitShemeBillInfo info) throws ShrWebBizException {
        Context ctx = SHRContext.getInstance().getContext();
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
        BatchSubmitShemeBillEntryCollection entryColl = info.getEntry();
        String billId = info.getId().toString();
        logger.error("单据ID：" + billId);
        Date effectDate = entryColl.get(0).getEffectDate();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(effectDate);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        Date startDate = calendar.getTime();
        calendar.add(Calendar.MONTH, 1);
        Date endDate = calendar.getTime();
        String startDateStr = DateTimeUtils.dateFormat(startDate, "yyyy-MM-dd");
        String endDateStr = DateTimeUtils.dateFormat(endDate, "yyyy-MM-dd");

        boolean flag = false;
        String sql = "SELECT a.*,b.billNumber FROM T_HR_SchemeBillEntry a , "
                + "(SELECT a.fnumber billNumber,b.* FROM T_HR_SBatchSubmitShemeBill a "
                + "left join T_HR_SchemeBillEntry b on a.fid = b.fbillid "
                + "where a.FBILLSTATE in ('1','2','3') and b.FPERSONID = '" + personId + "' "
                + "and b.FEFFECTDATE >= '" + startDateStr + "' and b.FEFFECTDATE < '" + endDateStr + "' and a.FDATASOURCE = '1') b "
                + "where a.FBILLID = '" + billId + "' and a.cfstarttime < b.CFENDTIME "
                + "and a.cfendtime > b.CFSTARTTIME";
        logger.error("查询是否有时间段重叠数据SQL：" + sql);
        try {
            IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
            String billNumber = "";
            while (rowSet.next()) {
                billNumber = rowSet.getString("billNumber");
                flag = true;
            }
            if (flag) {
                throw new ShrWebBizException("The start and end times overlap with another document (" + billNumber + ")");
            }
        } catch (BOSException e1) {
            e1.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 校验员工每月提交工时不能超过最大工时数
     *
     * @param info
     * @throws ShrWebBizException
     */
    public void checkHours(BatchSubmitShemeBillInfo info) throws ShrWebBizException {
        Context ctx = SHRContext.getInstance().getContext();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
        BatchSubmitShemeBillEntryCollection entryColl = info.getEntry();
        //上月工时数
        BigDecimal lastHours = BigDecimal.ZERO;
        //当月工时数
        BigDecimal currentHour = BigDecimal.ZERO;
        Calendar calendar = Calendar.getInstance();
        int currentYear = calendar.get(Calendar.YEAR);
        int currentMonth = calendar.get(Calendar.MONTH);
        for (int i = 0; i < entryColl.size(); i++) {
            BatchSubmitShemeBillEntryInfo entryInfo = entryColl.get(i);
            Date effectDate = entryInfo.getEffectDate();
            calendar.setTime(effectDate);
            if (currentYear == calendar.get(Calendar.YEAR) && currentMonth == calendar.get(Calendar.MONTH)) {
                currentHour = currentHour.add(entryInfo.getBigDecimal("totalWorkHours"));
            } else {
                lastHours = lastHours.add(entryInfo.getBigDecimal("totalWorkHours"));
            }
        }

        try {
            BigDecimal maxHour = BigDecimal.ZERO;
            String paramSql = "SELECT FNUMBER,fname_l2 name FROM T_HR_SHRRSVITEM0 where fstate = '1' and FNUMBER = '1003'";
            IRowSet paramRow = DbUtil.executeQuery(ctx, paramSql);
            while (paramRow.next()) {
                //最大工时数
                if (paramRow.getString("FNUMBER").equals("1003")) {
                    maxHour = paramRow.getObject("name") == null ? BigDecimal.ZERO : paramRow.getBigDecimal("name");
                }
            }

            if (lastHours.compareTo(BigDecimal.ZERO) != 0) {
                calendar = Calendar.getInstance();
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                Date endDate = calendar.getTime();
                calendar.add(Calendar.MONTH, -1);
                Date startDate = calendar.getTime();
                BigDecimal totalHour = BigDecimal.ZERO;
                String hourSql = "SELECT sum(b.cftotalworkhours) as totalHour FROM T_HR_SBatchSubmitShemeBill a left join T_HR_SchemeBillEntry b on a.fid=b.fbillid where  b.fpersonid='" + personId + "' and b.feffectdate>='" + sdf.format(startDate) + "' and b.feffectdate<'" + sdf.format(endDate) + "' and a.FBILLSTATE in ('1','2','3')";
                IRowSet iRowSet = DbUtil.executeQuery(ctx, hourSql);
                while (iRowSet.next()) {
                    totalHour = iRowSet.getObject("totalHour") == null ? BigDecimal.ZERO : iRowSet.getBigDecimal("totalHour");
                }
                totalHour = totalHour.add(lastHours);

                BigDecimal adjustHour = BigDecimal.ZERO;
                //获取调整工时数
                String adjustSql = "/*dialect*/SELECT cfadjustinghours FROM CT_MP_Ptworkinghours where fpersonid='" + personId + "' and DATE_TRUNC('month', cfeffectivedate)<='" + sdf.format(startDate) + "' and cfenddate>='" + sdf.format(startDate) + "' order by FLastUpdateTime desc limit 1";
                logger.error("获取调整工时SQL：" + adjustSql);
                IRowSet adjustRow = DbUtil.executeQuery(ctx, adjustSql);
                while (adjustRow.next()) {
                    adjustHour = adjustRow.getObject("cfadjustinghours") == null ? BigDecimal.ZERO : adjustRow.getBigDecimal("cfadjustinghours");
                }
                if (totalHour.compareTo(maxHour.add(adjustHour)) > 0) {
                    throw new ShrWebBizException("The total reported working hours for the previous month cannot exceed the maximum number of working hours");
                }
            }

            if (currentHour.compareTo(BigDecimal.ZERO) != 0) {
                calendar = Calendar.getInstance();
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                Date startDate = calendar.getTime();
                calendar.add(Calendar.MONTH, 1);
                Date endDate = calendar.getTime();
                BigDecimal totalHour = BigDecimal.ZERO;
                String hourSql = "SELECT sum(b.cftotalworkhours) as totalHour FROM T_HR_SBatchSubmitShemeBill a left join T_HR_SchemeBillEntry b on a.fid=b.fbillid where  b.fpersonid='" + personId + "' and b.feffectdate>='" + sdf.format(startDate) + "' and b.feffectdate<'" + sdf.format(endDate) + "' and a.FBILLSTATE in ('1','2','3')";
                IRowSet iRowSet = DbUtil.executeQuery(ctx, hourSql);
                while (iRowSet.next()) {
                    totalHour = iRowSet.getObject("totalHour") == null ? BigDecimal.ZERO : iRowSet.getBigDecimal("totalHour");
                }
                totalHour = totalHour.add(lastHours);

                BigDecimal adjustHour = BigDecimal.ZERO;
                //获取调整工时数
                String adjustSql = "/*dialect*/SELECT cfadjustinghours FROM CT_MP_Ptworkinghours where fpersonid='" + personId + "' and DATE_TRUNC('month', cfeffectivedate)<='" + sdf.format(startDate) + "' and cfenddate>='" + sdf.format(startDate) + "' order by FLastUpdateTime desc limit 1";
                logger.error("获取调整工时SQL：" + adjustSql);
                IRowSet adjustRow = DbUtil.executeQuery(ctx, adjustSql);
                while (adjustRow.next()) {
                    adjustHour = adjustRow.getObject("cfadjustinghours") == null ? BigDecimal.ZERO : adjustRow.getBigDecimal("cfadjustinghours");
                }
                if (totalHour.compareTo(maxHour.add(adjustHour)) > 0) {
                    throw new ShrWebBizException("The total reported working hours for the current month cannot exceed the maximum number of working hours");
                }
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 校验员工该职位每个月只能提交一次
     *
     * @param info
     * @throws ShrWebBizException
     */
    public void checkPositionCount(BatchSubmitShemeBillInfo info) throws ShrWebBizException {
        Context ctx = SHRContext.getInstance().getContext();
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
        BatchSubmitShemeBillEntryCollection entryColl = info.getEntry();
        //是否有上月发生日期
        boolean isLastMonth = false;
        //是否有当月发生日期
        boolean isCurrentMonth = false;
        String positionId = "";
        String superiorId = "";
        Calendar calendar = Calendar.getInstance();
        int currentYear = calendar.get(Calendar.YEAR);
        int currentMonth = calendar.get(Calendar.MONTH);
        for (int i = 0; i < entryColl.size(); i++) {
            BatchSubmitShemeBillEntryInfo entryInfo = entryColl.get(i);
            Date effectDate = entryInfo.getEffectDate();
            calendar.setTime(effectDate);
            if (currentYear == calendar.get(Calendar.YEAR) && currentMonth == calendar.get(Calendar.MONTH)) {
                isCurrentMonth = true;
            } else {
                isLastMonth = true;
            }
            positionId = entryInfo.getPosition().getId().toString();
            superiorId = entryInfo.getString("superiorId");
        }
        logger.error("职位ID：" + positionId + ", 直接上级ID：" + superiorId);

        try {
            if (isLastMonth) {
                calendar = Calendar.getInstance();
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                String endDate = DateTimeUtils.dateFormat(calendar.getTime(), "yyyy-MM-dd");
                calendar.add(Calendar.MONTH, -1);
                String startDate = DateTimeUtils.dateFormat(calendar.getTime(), "yyyy-MM-dd");

                int count = 0;
                String sql = "select count(1) count from T_HR_SBatchSubmitShemeBill a left join T_HR_SchemeBillEntry b on a.fid=b.fbillid where b.FPERSONID  = '" + personId + "' and b.FPOSITIONID = '" + positionId + "' and b.CFSuperiorID = '" + superiorId + "' and a.fbillstate in ('1','2','3') and a.fdatasource = '1' and b.FEFFECTDATE >= '" + startDate + "' and b.FEFFECTDATE < '" + endDate + "'";
                logger.error("查询员工该职位当月提交次数SQL：" + sql);
                IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
                while (iRowSet.next()) {
                    count = iRowSet.getInt("count");
                }
                logger.error("员工该职位当月提交次数：" + count);
                if (count > 0) {
                    throw new ShrWebBizException("The employee submits the position once within a month");
                }
            }

            if (isCurrentMonth) {
                calendar = Calendar.getInstance();
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                String startDate = DateTimeUtils.dateFormat(calendar.getTime(), "yyyy-MM-dd");
                calendar.add(Calendar.MONTH, 1);
                String endDate = DateTimeUtils.dateFormat(calendar.getTime(), "yyyy-MM-dd");

                int count = 0;
                String sql = "select count(1) count from T_HR_SBatchSubmitShemeBill a left join T_HR_SchemeBillEntry b on a.fid=b.fbillid where b.FPERSONID  = '" + personId + "' and b.FPOSITIONID = '" + positionId + "' and b.CFSuperiorID = '" + superiorId + "' and a.fbillstate in ('1','2','3') and a.fdatasource = '1' and b.FEFFECTDATE >= '" + startDate + "' and b.FEFFECTDATE < '" + endDate + "'";
                logger.error("查询员工该职位当月提交次数SQL：" + sql);
                IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
                while (iRowSet.next()) {
                    count = iRowSet.getInt("count");
                }
                logger.error("员工该职位当月提交次数：" + count);
                if (count > 0) {
                    throw new ShrWebBizException("The employee submits the position once within a month");
                }
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 校验XX号之后不允许提交
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void checkSumbitDateAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        int day = 0;
        Calendar calendar = Calendar.getInstance();
        int thisDay = calendar.get(Calendar.DAY_OF_MONTH);
        String resultMessage = "";
        try {
            String paramSql = "SELECT FNUMBER,fname_l2 name FROM T_HR_SHRRSVITEM0 where fstate = '1' and FNUMBER = '1004'";
            IRowSet paramRow = DbUtil.executeQuery(ctx, paramSql);
            while (paramRow.next()) {
                if (paramRow.getString("FNUMBER").equals("1004")) {
                    day = paramRow.getObject("name") == null ? 0 : paramRow.getInt("name");
                }
            }
            if (day != 0 && thisDay > day) {
                resultMessage = "It is not allowed to submit the previous month's work hours after the " + day + "th of each month";
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.put("resultMessage", resultMessage);
        JSONUtils.writeJson(response, modelMap);
    }


    /**
     * 根据人员id获取它的直属上级id和时薪
     *
     * @param request
     * @param response
     * @param modelMap
     * @return
     * @throws SHRWebException
     */
    public String getPersonCareerDataAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        String personId = request.getParameter("personId");
        String parentId = "";
        String workHour = "";
        String sql = "select re.FPERSONID,rl.cflinemanagernameI linemanagername,rl.cfhourlywage from (SELECT FPERSONID,max(FEFFDT) as maxDate FROM T_HR_EmpOrgRelation where fassignType = '1' group by  FPERSONID) as re  left join T_HR_EmpOrgRelation rl on re.fpersonid = rl.fpersonid and re.maxDate = rl.feffdt and rl.fassignType = '1' where re.fpersonid='" + personId + "'";
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
            while (iRowSet.next()) {
                parentId = iRowSet.getString("linemanagername");
                workHour = iRowSet.getString("cfhourlywage");
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.put("parentId", parentId);
        modelMap.put("workHour", workHour);
        JSONUtils.writeJson(response, modelMap);
        return null;
    }

    /**
     * 获取当前员工的最新入职日期
     *
     * @param request
     * @param response
     * @param modelMap
     * @return
     * @throws SHRWebException
     */
    public String getInductionDateAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
        String sql = "select b.fenterdate from (SELECT FPERSONID,max(FEFFDT) as maxDate FROM T_HR_EmpLaborRelationHis group by fpersonid) a left join T_HR_EmpLaborRelationHis b on a.fpersonid=b.fpersonid and a.maxdate=b.FEFFDT where a.fpersonid='" + personId + "'";
        String enterDate = "";
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
            while (iRowSet.next()) {
                enterDate = iRowSet.getString("fenterdate");
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.put("enterDate", enterDate);
        JSONUtils.writeJson(response, modelMap);
        return null;
    }

    /**
     * 查询人员对应职位的最新的合同开始和结束日期
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void getContractDateAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
        //职位id
        String positionId = request.getParameter("positionId");
        Context ctx = SHRContext.getInstance().getContext();
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
//		String sql = "SELECT b.cfcommencingdate,b.cfexpireddate FROM (SELECT max(cfcommencingdate) maxdate,fpersonid,cfpositionid FROM CT_MP_Pcontractinfo group by fpersonid,cfpositionid) a left join CT_MP_Pcontractinfo b on a.maxdate=b.cfcommencingdate and a.fpersonid=b.fpersonid and a.cfpositionid=b.cfpositionid where a.fpersonid='"+personId+"' and a.cfpositionid='"+positionId+"' and b.cftype!='1'";
        String sql = "SELECT cfcommencingdate,cfexpireddate,cftype FROM CT_MP_Pcontractinfo where fpersonid='" + personId + "' and cfpositionid='" + positionId + "' order by cfcommencingdate asc";
        logger.error("查询合同信息SQL：" + sql);
        Date beginContractDate = null;
        Date endContractDate = null;
        String contractType = "";
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
			/*while(iRowSet.next()) {
				//合同开始日期
				beginContractDate = iRowSet.getDate("cfcommencingdate");
				//合同结束日期
				endContractDate = iRowSet.getDate("cfexpireddate");
			}*/
            List<Map<String, Object>> list = new ArrayList<>();
            while (iRowSet.next()) {
                //合同开始日期
                Date beginDate = iRowSet.getDate("cfcommencingdate");
                //合同结束日期
                Date endDate = iRowSet.getDate("cfexpireddate");
                //合同类型
                String type = iRowSet.getString("cftype");
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("beginDate", beginDate);
                map.put("endDate", endDate);
                map.put("type", type);
                list.add(map);
            }

            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);
                Date beginDate = (Date) map.get("beginDate");
                Date endDate = (Date) map.get("endDate");
                String type = (String) map.get("type");

                //获取连续时间段的开始日期和结束日期，如果中间合同类型发生变动或者日期中断，则重新计算
                if (StringUtils.equals(contractType, type)) {
                    Calendar beginCalendar = Calendar.getInstance();
                    Calendar endCalendar = Calendar.getInstance();
                    beginCalendar.setTime(beginDate);
                    beginCalendar.add(Calendar.DAY_OF_MONTH, -1);
                    endCalendar.setTime(endContractDate);

                    boolean isYear = beginCalendar.get(Calendar.YEAR) == endCalendar.get(Calendar.YEAR);
                    boolean isMonth = beginCalendar.get(Calendar.MONTH) == endCalendar.get(Calendar.MONTH);
                    boolean isDay = beginCalendar.get(Calendar.DAY_OF_MONTH) == endCalendar.get(Calendar.DAY_OF_MONTH);
                    boolean flag = isYear && isMonth && isDay;

                    if (flag) {
                        endContractDate = endDate;
                    } else {
                        beginContractDate = beginDate;
                        endContractDate = endDate;
                    }
                } else {
                    beginContractDate = beginDate;
                    endContractDate = endDate;
                    contractType = type;
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (BOSException e) {
            e.printStackTrace();
        }
        modelMap.put("beginContractDate", beginContractDate);
        modelMap.put("endContractDate", endContractDate);
        JSONUtils.writeJson(response, modelMap);
    }

    /**
     * 校验月提交总工时不能大于最大工时(废弃)
     *
     * @param request
     * @param response
     * @param modelMap
     * @return
     * @throws SHRWebException
     */
    public String checkEscalationDateAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
            throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
        BigDecimal hours = new BigDecimal(request.getParameter("hours"));
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();
        int thisDay = calendar.get(Calendar.DAY_OF_MONTH);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        Date endDate = calendar.getTime();
        calendar.add(Calendar.MONTH, -1);
        Date stratDate = calendar.getTime();

        try {
            BigDecimal maxHour = BigDecimal.ZERO;
            int day = 0;
            String paramSql = "SELECT FNUMBER,fname_l2 name FROM T_HR_SHRRSVITEM0 where fstate = '1' and (FNUMBER = '1003' or FNUMBER = '1004')";
            IRowSet paramRow = DbUtil.executeQuery(ctx, paramSql);
            while (paramRow.next()) {
                //最大工时数
                if (paramRow.getString("FNUMBER").equals("1003")) {
                    maxHour = paramRow.getObject("name") == null ? BigDecimal.ZERO : paramRow.getBigDecimal("name");
                }
                if (paramRow.getString("FNUMBER").equals("1004")) {
                    day = paramRow.getObject("name") == null ? 0 : paramRow.getInt("name");
                }
            }
            //校验XX号之后不允许提交
            if (thisDay > day) {
                modelMap.put("resultMessage", "It is not allowed to submit the previous month's work hours after the " + day + "th of each month");
                JSONUtils.writeJson(response, modelMap);
                return null;
            }

            BigDecimal totalHour = BigDecimal.ZERO;
            String hourSql = "SELECT sum(b.cftotalworkhours) as totalHour FROM T_HR_SBatchSubmitShemeBill a left join T_HR_SchemeBillEntry b on a.fid=b.fbillid where  b.fpersonid='" + personId + "' and b.feffectdate>='" + sdf.format(stratDate) + "' and b.feffectdate<'" + sdf.format(endDate) + "' and a.FBILLSTATE in ('1','2','3')";
            IRowSet iRowSet = DbUtil.executeQuery(ctx, hourSql);
            while (iRowSet.next()) {
                totalHour = iRowSet.getObject("totalHour") == null ? BigDecimal.ZERO : iRowSet.getBigDecimal("totalHour");
            }
            totalHour = totalHour.add(hours);

            BigDecimal adjustHour = BigDecimal.ZERO;
            //获取调整工时数
            String adjustSql = "SELECT cfadjustinghours FROM CT_MP_Ptworkinghours where fpersonid='" + personId + "' and cfeffectivedate>='" + sdf.format(stratDate) + "' and cfeffectivedate<'" + sdf.format(endDate) + "'";
            IRowSet adjustRow = DbUtil.executeQuery(ctx, adjustSql);
            while (adjustRow.next()) {
                adjustHour = adjustRow.getObject("cfadjustinghours") == null ? BigDecimal.ZERO : adjustRow.getBigDecimal("cfadjustinghours");
            }
            if (totalHour.compareTo(maxHour.add(adjustHour)) > 0) {
                modelMap.put("resultMessage", "The total working hours in a month cannot exceed the maximum number of hours");
                JSONUtils.writeJson(response, modelMap);
            }

        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 汇总分录的工时和金额到单头
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void countHourAmountAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
        String billId = request.getParameter("billId");
        if (StringUtils.isEmpty(billId)) {
            return;
        }
        Context ctx = SHRContext.getInstance().getContext();
        try {
            BatchSubmitShemeBillInfo batchSubmitShemeBillInfo = BatchSubmitShemeBillFactory.getLocalInstance(ctx).getBatchSubmitShemeBillInfo(new ObjectUuidPK(billId));
            BatchSubmitShemeBillEntryCollection entryColl = batchSubmitShemeBillInfo.getEntry();
            BigDecimal sumHours = BigDecimal.ZERO;
            BigDecimal sumAmount = BigDecimal.ZERO;
            for (int i = 0; i < entryColl.size(); i++) {
                BatchSubmitShemeBillEntryInfo entryInfo = entryColl.get(i);
                BigDecimal hour = entryInfo.getBigDecimal("totalWorkHours") == null ? BigDecimal.ZERO : entryInfo.getBigDecimal("totalWorkHours");
                BigDecimal amount = entryInfo.getBigDecimal("totalAmount") == null ? BigDecimal.ZERO : entryInfo.getBigDecimal("totalAmount");
                sumHours = sumHours.add(hour);
                sumAmount = sumAmount.add(amount);
            }
            batchSubmitShemeBillInfo.put("totalWorkHours", sumHours);
            batchSubmitShemeBillInfo.put("totalAmount", sumAmount);
            BatchSubmitShemeBillFactory.getLocalInstance(ctx).save(batchSubmitShemeBillInfo);
        } catch (EASBizException e) {
            e.printStackTrace();
        } catch (BOSException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据提报日期判断法定节假日和休息日
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void workMultipleAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        String otDate = request.getParameter("otDate");
        logger.error("提报日期：" + otDate);
        Date parseDate = DateTimeUtils.parseDate(otDate, "yyyy-MM-dd");
        otDate = DateTimeUtils.dateFormat(parseDate, "yyyy-MM-dd");
        String sql = "SELECT b.CFWORKMULTIPLE FROM T_HR_ATS_LegalHoliday a left join T_HR_ATS_LegalHolidayItem b on a.fid = b.FGROUPID where a.FSTATE = '1' and b.FSTARTDATE <= '" + otDate + "' and b.FENDDATE >= '" + otDate + "'";
        logger.error("查询加班类型SQL：" + sql);
        Map<String, String> returnMap = new HashMap<String, String>();
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
            boolean flag = false;
            while (iRowSet.next()) {
                if (StringUtils.equals(iRowSet.getString("CFWORKMULTIPLE"), "3")) {
                    //法定节假日加班
                    flag = true;
                }
            }
            if (flag) {
                returnMap.put("returnType", "0");
            } else {
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(parseDate);
                int dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK);
                if (dayOfWeek == Calendar.SATURDAY || dayOfWeek == Calendar.SUNDAY) {
                    //休息日加班
                    returnMap.put("returnType", "1");
                }
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JSONUtils.writeJson(response, returnMap);
    }

    /**
     * 根据人员档案获取当月是否离职或兼职失效
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void checkDepartDateAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();
        String cmpOrgRelationId = request.getParameter("cmpOrgRelationId");
        Calendar calendar = Calendar.getInstance();
        int currentYear = calendar.get(Calendar.YEAR);
        int currentMonth = calendar.get(Calendar.MONTH) + 1;
        boolean flag = false;
        String cmpSql = "select b.fassigntype,b.fpositionid from T_HR_SCmpEmpORelation a left join T_HR_EmpOrgRelation b on a.FEMPORGRELATIONID = b.fid where a.fid ='" + cmpOrgRelationId + "'";
        logger.error("查询员工档案信息SQL：" + cmpSql);
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, cmpSql);
            //任职类型
            String assignType = "";
            //职位id
            String positionId = "";
            while (iRowSet.next()) {
                assignType = iRowSet.getString("fassigntype");
                positionId = iRowSet.getString("fpositionid");
            }
            //主要任职时
            if (StringUtils.equals(assignType, "1")) {
                String resignSql = "select year(b.FBIZDATE) year,month(b.FBIZDATE) month from T_HR_ResignBizBill a left join T_HR_ResignBizBillentry b on a.fid=b.fbillid where b.FPERSONID ='" + personId + "' and a.FBILLSTATE = '3' order by b.FBIZDATE desc";
                logger.error("查询员工离职单SQL：" + resignSql);
                IRowSet resignRow = DbUtil.executeQuery(ctx, resignSql);
                int resignYear = 0;
                int resignMonth = 0;
                while (resignRow.next()) {
                    resignYear = resignRow.getInt("year");
                    resignMonth = resignRow.getInt("month");
                }
                if (currentYear == resignYear && currentMonth == resignMonth) {
                    flag = true;
                }
            } else {
                String empSql = "select top 1 year(fleffdt) year,month(fleffdt) month from T_HR_EmpOrgRelation where fpositionid = '" + positionId + "' and fpersonid='" + personId + "' order by feffdt desc";
                logger.error("查询员工兼职SQL：" + empSql);
                int leffdtYear = 0;
                int leffdtMonth = 0;
                IRowSet empRow = DbUtil.executeQuery(ctx, empSql);
                while (empRow.next()) {
                    leffdtYear = empRow.getInt("year");
                    leffdtMonth = empRow.getInt("month");
                }
                if (currentYear == leffdtYear && currentMonth == leffdtMonth) {
                    flag = true;
                }
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.put("flag", flag);
        JSONUtils.writeJson(response, modelMap);
    }

    /**
     * 员工同一职位一个月内只能提交一次
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void checkSubmitCountAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        String positionId = request.getParameter("positionId");
        String personId = ContextUtil.getCurrentUserInfo(ctx).getPerson().getId().toString();

        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        String startDate = DateTimeUtils.dateFormat(calendar.getTime(), "yyyy-MM-dd");
        calendar.add(Calendar.MONTH, 1);
        String endDate = DateTimeUtils.dateFormat(calendar.getTime(), "yyyy-MM-dd");

        int count = 0;
        String sql = "SELECT count(1) count FROM T_HR_SBatchSubmitShemeBill where fid in (select distinct a.fid from T_HR_SBatchSubmitShemeBill a left join T_HR_SchemeBillEntry b on a.fid=b.fbillid where a.FPROPOSERID  = '" + personId + "' and b.FPOSITIONID = '" + positionId + "' and a.fbillstate in ('1','2','3') and a.fdatasource = '1' and a.FSUBMITTIME >= '" + startDate + "' and a.FSUBMITTIME < '" + endDate + "')";
        logger.error("查询员工该职位当月提交次数SQL：" + sql);
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
            while (iRowSet.next()) {
                count = iRowSet.getInt("count");
            }
            logger.error("员工该职位当月提交次数：" + count);
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.put("count", count);
        JSONUtils.writeJson(response, modelMap);
    }

    /**
     * 判断是否参与校验
     *
     * @param request
     * @param response
     * @param modelMap
     * @throws SHRWebException
     */
    public void isTakeCheckAction(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException {
        Context ctx = SHRContext.getInstance().getContext();
        //提报方案ID
        String calSchemeId = request.getParameter("calSchemeId");
        logger.error("提报方案ID：" + calSchemeId);
        //是否参与校验
        boolean isPlanCheck = false;
        //当月离职处理
        boolean isCurrentDepart = false;
        String sql = "select cfistakecheck,CFIsCurrentDepart from T_HR_SCalSubmitScheme where fid = '" + calSchemeId + "'";
        logger.error("查询提报方案SQL：" + sql);
        try {
            IRowSet iRowSet = DbUtil.executeQuery(ctx, sql);
            while (iRowSet.next()) {
                if (StringUtils.equals("1", iRowSet.getString("cfistakecheck"))) {
                    isPlanCheck = true;
                }
                if (StringUtils.equals("1", iRowSet.getString("CFIsCurrentDepart"))) {
                    isCurrentDepart = true;
                }
            }
        } catch (BOSException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.put("isPlanCheck", isPlanCheck);
        modelMap.put("isCurrentDepart", isCurrentDepart);
        JSONUtils.writeJson(response, modelMap);
    }


}