package com.kingdee.shr.customer.gtiit.osf; import com.kingdee.bos.BOSException; import com.kingdee.bos.Context; import com.kingdee.bos.bsf.service.app.IHRMsfService; import com.kingdee.bos.dao.IObjectPK; import com.kingdee.bos.dao.ormapping.ObjectUuidPK; import com.kingdee.bos.util.BOSUuid; import com.kingdee.eas.basedata.org.AdminOrgUnitInfo; import com.kingdee.eas.basedata.org.CtrlUnitInfo; import com.kingdee.eas.basedata.org.HROrgUnitInfo; import com.kingdee.eas.basedata.org.PositionInfo; import com.kingdee.eas.basedata.person.PersonInfo; import com.kingdee.eas.common.EASBizException; import com.kingdee.eas.custom.bd.param.ParamCollection; import com.kingdee.eas.custom.bd.param.ParamFactory; import com.kingdee.eas.custom.facade.leave.LeaveAllowanceFacadeFactory; import com.kingdee.eas.framework.CoreBaseCollection; import com.kingdee.eas.hr.ats.HolidayLimitFactory; import com.kingdee.eas.hr.ats.HolidayLimitInfo; import com.kingdee.eas.hr.ats.HolidayLimitStatus; import com.kingdee.eas.hr.ats.HolidayPolicyInfo; import com.kingdee.eas.hr.ats.HolidayTypeUnitEnum; import com.kingdee.eas.util.app.ContextUtil; import com.kingdee.eas.util.app.DbUtil; import com.kingdee.jdbc.rowset.IRowSet; import com.kingdee.util.DateTimeUtils; import java.math.BigDecimal; import java.math.RoundingMode; import java.sql.SQLException; import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.lang3.StringUtils; public class LeaveAllowanceOSFService implements IHRMsfService { private static final String WC_General_Administrative_Staff = "General Administrative Staff"; private static final String WC_Professional_Supervisory_Staff = "Professional Supervisory Staff"; private static final String WC_Senior_Administrative_Staff = "Senior Administrative Staff"; private static final String WC_Academic_Staff = "Academic Staff"; private static final String WC_Others = "Others"; private static final int IV_General_Administrative_Staff = 12; private static final int IV_Professional_Supervisory_Staff = 15; private static final int IV_Senior_Administrative_Staff = 18; private static final int IV_Year = 2; private static final int IV_YearAddValue = 1; private static final int IV_MAXADD = 8; private static final int MONTH_NUM_12 = 12; private static final String PARAM_ANNUALLEAVE_CONVERSIONMETHOD = "param_001"; private static final String PARAM_ANNUALLEAVE_INTEGERMETHOD = "param_002"; private static final String PARAM_ANNUALLEAVE_INTEGERPERCISION = "param_003"; private static final String PARAM_ANNUALLEAVE_CONVERTDATE = "param_004"; private static final String PARAM_ANNUALLEAVE_GENERATEDAY = "param_005"; private static final String PARAM_NURSINGLEAVE_LIMIT = "param_006"; private static final String PARAM_PARENTALLEAVE_LIMIT = "param_007"; private static final String PARAM_NURSINGANDPARENTALLEAVE_CONVERSIONMETHOD = "param_008"; private static final String PARAM_NURSINGANDPARENTALLEAVE_INTEGERMETHOD = "param_009"; private static final String PARAM_NURSINGANDPARENTALLEAVE_INTEGERPERCISION = "param_010"; private static final String PARAM_NURSINGANDPARENTALLEAVE_UPDATESQL = "param_011"; private static final String CONVERSIONMETHOD_BYDAY = "D"; private static final String CONVERSIONMETHOD_BYMONTH = "M"; private static final String INTEGERMETHOD_UP = "U"; private static final String INTEGERMETHOD_DOWN = "D"; private static final double INTEGERPERCISION_HALF = (double)0.5F; private static final double INTEGERPERCISION_ONE = (double)1.0F; private static String annualLeaveConversionMethod; private static String annualLeaveIntegerMethod; private static double annualLeaveIntegerPrecision; private static String annualLeaveConvertDate; private static String annualLeaveGenerateDay; private static int nursingLeaveLimit; private static int parentalLeaveLimit; private static String nursingAndParentalLeaveConversionMethod; private static String nursingAndParentalLeaveIntegerMethod; private static double nursingAndParentalLeaveIntegerPrecision; private static String nursingAndParentalLeaveUpdateSql; public Object process(Context context, Map map) throws EASBizException, BOSException { String year = (String)map.get("year"); String personNum = (String)map.get("personNum"); String Date = (String)map.get("currentDate"); String mack = (String)map.get("mack"); if ("ParentalLeave".equals(mack)) { this.createNursingOrParentalLeave(context, Integer.parseInt(year), personNum, "JQLX000015Y", parentalLeaveLimit, "C"); } else if ("NursingLeave".equals(mack)) { this.createNursingOrParentalLeave(context, Integer.parseInt(year), personNum, "JQLX000016Y", nursingLeaveLimit, "P"); } else if("updateHolidaypolicy".equals(mack)) { updateHolidaypolicy(context,Integer.parseInt(year)); } else { SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd"); Date currentDate = null; try { currentDate = sf.parse(Date); } catch (ParseException e) { e.printStackTrace(); } this.generateAnnualLeave(context, Integer.parseInt(year), personNum, currentDate); } return null; } private void initialGlobalParam(Context ctx) throws BOSException { Map param = new HashMap(); ParamCollection paramCollection = ParamFactory.getLocalInstance(ctx).getParamCollection(); for(int i = 0; i < paramCollection.size(); ++i) { param.put(paramCollection.get(i).getNumber(), paramCollection.get(i).getValue()); } annualLeaveConversionMethod = (String)param.get("param_001"); annualLeaveIntegerMethod = (String)param.get("param_002"); annualLeaveIntegerPrecision = Double.valueOf((String)param.get("param_003")); annualLeaveConvertDate = (String)param.get("param_004"); annualLeaveGenerateDay = (String)param.get("param_005"); nursingLeaveLimit = Integer.parseInt((String)param.get("param_006")); parentalLeaveLimit = Integer.parseInt((String)param.get("param_007")); nursingAndParentalLeaveConversionMethod = (String)param.get("param_008"); nursingAndParentalLeaveIntegerMethod = (String)param.get("param_009"); nursingAndParentalLeaveIntegerPrecision = Double.valueOf((String)param.get("param_010")); nursingAndParentalLeaveUpdateSql = (String)param.get("param_011"); } private synchronized static void updateHolidaypolicy(Context ctx,int year) throws BOSException { String updateHolidaypolicy = "/*dialect*/MERGE INTO T_HR_ATS_HolidayLimit AS hl \n USING ( \n select\n hl.fid,\n af.fproposerid, hpf.fid AS holiday_policy_id \n from \n T_HR_ATS_AtsHolidayFile af \n left JOIN T_HR_ATS_HolidayPolicy hpf ON hpf.fholidaypolicysetid = af.fholidaypolicysetid \n left JOIN T_HR_ATS_HolidayType ht ON ht.FID = hpf.fholidaytypeid \n left join T_HR_ATS_HolidayLimit hl on af.FPROPOSERID =hl.fproposerid \n where ht.FNUMBER = 'JQLX000001Y' and hl.fholidaypolicyid in( \n select hp.fid from T_HR_ATS_HolidayPolicy hp \n inner JOIN T_HR_ATS_HolidayType ht ON ht.FID = hp.fholidaytypeid \n where ht.FNUMBER = 'JQLX000001Y' \n ) \n and to_char(hl.fcyclebegindate,'yyyy')='" + year + "' \n ) AS src ON hl.fid = src.fid and to_char(hl.fcyclebegindate,'yyyy') ='" + year + "' \n WHEN MATCHED THEN \n UPDATE SET hl.fname_l3=hl.fholidaypolicyid,hl.fholidaypolicyid = src.holiday_policy_id"; DbUtil.execute(ctx, updateHolidaypolicy); StringBuilder sqlLastCarryforward = new StringBuilder(); sqlLastCarryforward.append(" /*dialect*/merge into T_HR_ATS_HolidayLimit hln "); sqlLastCarryforward.append(" using ( "); sqlLastCarryforward.append(" select hln.fid,hlo.cfcarryforward from T_HR_ATS_HolidayLimit hln "); sqlLastCarryforward.append(" inner join( "); sqlLastCarryforward.append(" select ht.fname_l2,hl.FPROPOSERID,hl.fholidaypolicyid, "); sqlLastCarryforward.append(" max(COALESCE(hl.cfcarryforward,0)) cfcarryforward "); sqlLastCarryforward.append(" from T_HR_ATS_HolidayLimit hl "); sqlLastCarryforward.append(" inner join (select "); sqlLastCarryforward.append(" hp.fid,ht.fname_l2 "); sqlLastCarryforward.append(" from "); sqlLastCarryforward.append(" T_HR_ATS_HolidayPolicy hp "); sqlLastCarryforward.append(" inner JOIN T_HR_ATS_HolidayType ht ON ht.FID = hp.fholidaytypeid "); sqlLastCarryforward.append(" where "); sqlLastCarryforward.append(" ht.FNUMBER = 'JQLX000001Y') ht on ht.fid=hl.fholidaypolicyid "); sqlLastCarryforward.append(" where to_char(hl.fcyclebegindate, 'yyyy') = '" + (year - 1) + "' "); sqlLastCarryforward.append(" group by ht.fname_l2,hl.FPROPOSERID,hl.fholidaypolicyid "); sqlLastCarryforward.append(" ) hlo on hln.FPROPOSERID = hlo.FPROPOSERID "); sqlLastCarryforward.append(" where hln.fholidaypolicyid = hlo.fholidaypolicyid "); sqlLastCarryforward.append(" and to_char(hln.fcyclebegindate, 'yyyy') ='" + year + "' "); sqlLastCarryforward.append(" ) hlo on hln.fid=hlo.fid "); sqlLastCarryforward.append(" WHEN MATCHED THEN "); sqlLastCarryforward.append(" update set hln.cflastCarryforward = hlo.cfcarryforward "); DbUtil.execute(ctx, sqlLastCarryforward.toString()); } private void generateAnnualLeave(Context ctx, int year, String personNum, Date currentDate) throws BOSException { this.initialGlobalParam(ctx); try { Date nowDate = new Date(); Map holidayPolicyMap = new HashMap(); String holidayPolicySql = "select hps.FNUMBER, hp.fid from T_HR_ATS_HolidayPolicy hp left join T_HR_ATS_HolidayPolicySet hps on hp.FHOLIDAYPOLICYSETID = hps.fid where FHOLIDAYTYPEID = (select fid from T_HR_ATS_HolidayType where FNUMBER = 'JQLX000001Y')"; IRowSet holidayPolicyRowSet = DbUtil.executeQuery(ctx, holidayPolicySql); while(holidayPolicyRowSet.next()) { holidayPolicyMap.put(holidayPolicyRowSet.getString("FNUMBER"), holidayPolicyRowSet.getString("fid")); } String currentDateStr = DateTimeUtils.format(currentDate, "yyyy-MM-dd"); Date yearFirstDay = DateTimeUtils.parseDate(DateTimeUtils.getYear(currentDate) + "-01-01"); Date yearLastDay = DateTimeUtils.parseDate(DateTimeUtils.getYear(currentDate) + "-12-31"); List personNumberList = new ArrayList(); CoreBaseCollection addList = new CoreBaseCollection(); CoreBaseCollection updateList = new CoreBaseCollection(); List deleteList = new ArrayList(); List> reCalList = new ArrayList(); String personNumStr = ""; if (StringUtils.isNotEmpty(personNum)) { String[] personNumArr = personNum.split(","); for(int i = 0; i < personNumArr.length; ++i) { personNumberList.add("'" + personNumArr[i] + "'"); } personNumStr = String.join(",", personNumberList); } String dbType = ContextUtil.getDbType(ctx); String sql = " /*dialect*/select * from (select row_number() over (partition by p.fid order by p.fid,eo.feffdt desc,l.fid) as rn,p.fid,p.fnumber,eo.FADMINORGID,eo.FHRORGUNITID,eo.FPOSITIONID,p.cfftorptid fullOrPartId,fp.fnumber fullOrPartNumber, p.cffae annualleavelimit,case when rbe.fid is null then eo.cfworkercategoryid else rbe.CFWcategoryID end workercategoryid,wc.fname_l2 workercategoryname,p.fhiredate, (to_char(to_date('" + currentDateStr + "','yyyy-MM-dd'),'yyyy') - to_char(p.fhiredate,'yyyy')+1)joinDateYear,case when rbe.fid is null then null else rbe.FBIZDATE end leaveDate,l.fid limitId,coalesce(l.FADDORSUBLIMIT,0) FADDORSUBLIMIT,coalesce(l.FUSEDLIMIT,0) FUSEDLIMIT,coalesce(l.FREEZELIMIT,0) FREEZELIMIT,coalesce(l.cflastcarryforward,0) cflastcarryforward,coalesce(l.cfcarryforward,0) cfcarryforward,coalesce(l.CFEntitlementLimit,0) CFEntitlementLimit,coalesce(l.CFInitializedLimit,0) CFInitializedLimit, ebe.FPROBATION,ebe.FPLANFORMALDATE,hp.fid holidayPolicyId,l.fisleftconvert isleftconvert from T_HR_ATS_AtsHolidayFile f left join T_BD_Person p on f.fproposerid = p.fid left join T_HR_EmpOrgRelation eo on p.fid = eo.fpersonid and eo.fleffdt='2199-12-31 00:00:00' left join CT_MP_Fullorpart fp on fp.fid = p.cfftorptid left join T_HR_ATS_HolidayType ht on ht.FNUMBER = 'JQLX000001Y' left join T_HR_ATS_HolidayPolicy hp on ht.FID =hp.fholidaytypeid and f.fholidaypolicysetid = hp.fholidaypolicysetid left join T_HR_ATS_HolidayLimit l on hp.fid = l.FHOLIDAYPOLICYID and l.FPROPOSERID = p.fid and l.FCYCLEBEGINDATE >= p.fhiredate and l.FCYCLEBEGINDATE <= p.FLEFFDT and l.fyear = " + year + " left join ( select rownum rownumber,ebe.FPERSONID, ebe.FPROBATION,ebe.FPLANFORMALDATE, ebe.FBIZDATE from T_HR_EmpEnrollBizBillEntry ebe inner join (select re1.fpersonid,max(re1.fbizdate) fbizdate,count(fid) from T_HR_EmpEnrollBizBillEntry re1 group by re1.fpersonid) re1 on ebe.fpersonid=re1.fpersonid and re1.fbizdate=ebe.fbizdate left join T_HR_EmpEnrollBizBill eb on ebe.FBILLID = eb.FID where eb.FBILLSTATE = 3 order by ebe.fbizdate desc) ebe on ebe.FPERSONID = p.fid and ebe.FBIZDATE = p.fhiredate left join( select rownum rownumber,rbe.fid, rbe.FPERSONID, rbe.FENTERDATE, rbe.CFWcategoryID, rbe.FBIZDATE from T_HR_ResignBizBillEntry rbe inner join (select re1.fpersonid,max(re1.fbizdate) fbizdate,count(fid) from T_HR_ResignBizBillEntry re1 group by re1.fpersonid) re1 on rbe.fpersonid=re1.fpersonid and re1.fbizdate=rbe.fbizdate left join T_HR_ResignBizBill rb on rb.fid = rbe.FBILLID where rb.FBILLSTATE = 3 order by rbe.fbizdate desc ) rbe on rbe.FPERSONID = p.fid and p.fhiredate = rbe.FENTERDATE left join CT_MP_WorkerCategory wc on (wc.fid = eo.cfworkercategoryid or wc.fid = rbe.CFWcategoryID) where f.fattendfilestate in( 1,2) and ht.FNUMBER = 'JQLX000001Y' and fp.fnumber = 'FULL' and eo.fassigntype = 1 and eo.feffdt <= to_date('" + currentDateStr + "','yyyy-MM-dd') and to_char(p.fhiredate, 'yyyy')<=" + year + " and to_char(p.FLEFFDT,'yyyy')>=" + year + " "; if (StringUtils.isNotEmpty(personNumStr)) { sql = sql + " and p.fnumber in (" + personNumStr + ") "; } sql = sql + " order by l.fid ) temp where temp.rn = 1 "; IRowSet iRowSet = DbUtil.executeQuery(ctx, sql); while(iRowSet.next()) { String holidayPolicyId = iRowSet.getString("holidayPolicyId"); if (StringUtils.isEmpty(holidayPolicyId)) { holidayPolicyId = (String)holidayPolicyMap.values().stream().findFirst().get(); } else { String limitId = iRowSet.getString("limitId"); int annualleavelimit = iRowSet.getInt("annualleavelimit"); String workercategoryname = iRowSet.getString("workercategoryname"); int joinDateYear = iRowSet.getInt("joinDateYear"); Date joinDate = iRowSet.getDate("fhiredate"); Date fplanformaldate = iRowSet.getDate("FPLANFORMALDATE"); Date leaveDate = iRowSet.getDate("leaveDate"); BigDecimal standardLimit = BigDecimal.ZERO; BigDecimal convertedLimit = BigDecimal.ZERO; BigDecimal entitlementLimit = BigDecimal.ZERO; int isLeave = 0; BigDecimal realLimit = BigDecimal.ZERO; BigDecimal remainLimit = BigDecimal.ZERO; BigDecimal entitlementLimitOld = iRowSet.getBigDecimal("CFEntitlementLimit"); BigDecimal initializedLimit = iRowSet.getBigDecimal("CFInitializedLimit"); BigDecimal lastCarryforward = iRowSet.getBigDecimal("CFLastCarryforward"); BigDecimal carryforward = iRowSet.getBigDecimal("CFCarryforward"); BigDecimal addOrSubLimit = iRowSet.getBigDecimal("FADDORSUBLIMIT"); BigDecimal usedLimit = iRowSet.getBigDecimal("FUSEDLIMIT"); BigDecimal freezeLimit = iRowSet.getBigDecimal("FreezeLimit"); int isleftconvert = iRowSet.getInt("isleftconvert"); if (!StringUtils.equalsIgnoreCase(workercategoryname, "General Administrative Staff") && !StringUtils.equalsIgnoreCase(workercategoryname, "Professional Supervisory Staff") && !StringUtils.equalsIgnoreCase(workercategoryname, "Senior Administrative Staff")) { if (StringUtils.equalsIgnoreCase(workercategoryname, "Academic Staff") || StringUtils.equalsIgnoreCase(workercategoryname, "Others")) { convertedLimit = BigDecimal.valueOf((long)annualleavelimit); } } else if (annualleavelimit > 0) { convertedLimit = BigDecimal.valueOf((long)(annualleavelimit + (joinDateYear - 1) / 2 * 1)); if (convertedLimit.compareTo(BigDecimal.valueOf(30L)) > 0) { convertedLimit = BigDecimal.valueOf(30L); } } else { if (StringUtils.equalsIgnoreCase(workercategoryname, "General Administrative Staff")) { convertedLimit = BigDecimal.valueOf((long)(12 + (joinDateYear - 1) / 2 * 1)); if (convertedLimit.compareTo(BigDecimal.valueOf(20L)) > 0) { convertedLimit = BigDecimal.valueOf(20L); } } if (StringUtils.equalsIgnoreCase(workercategoryname, "Professional Supervisory Staff")) { convertedLimit = BigDecimal.valueOf((long)(15 + (joinDateYear - 1) / 2 * 1)); if (convertedLimit.compareTo(BigDecimal.valueOf(23L)) > 0) { convertedLimit = BigDecimal.valueOf(23L); } } if (StringUtils.equalsIgnoreCase(workercategoryname, "Senior Administrative Staff")) { convertedLimit = BigDecimal.valueOf((long)(18 + (joinDateYear - 1) / 2 * 1)); if (convertedLimit.compareTo(BigDecimal.valueOf(26L)) > 0) { convertedLimit = BigDecimal.valueOf(26L); } } } entitlementLimit = convertedLimit; if (leaveDate != null && DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(leaveDate) && joinDate.compareTo(leaveDate) < 0) { if (StringUtils.equals("D", annualLeaveConversionMethod)) { if (DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(joinDate)) { convertedLimit = convertedLimit.multiply(BigDecimal.valueOf((long)(DateTimeUtils.getMonth(leaveDate) - DateTimeUtils.getMonth(joinDate) + 1))).divide(BigDecimal.valueOf(12L), 10, 4); } else { convertedLimit = convertedLimit.multiply(BigDecimal.valueOf((long)DateTimeUtils.getMonth(leaveDate))).divide(BigDecimal.valueOf(12L), 10, 4); } } else if (DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(joinDate)) { convertedLimit = convertedLimit.multiply(BigDecimal.valueOf((long)(DateTimeUtils.getMonth(leaveDate) - DateTimeUtils.getMonth(joinDate) + 1))).divide(BigDecimal.valueOf(12L), 10, 4); } else { convertedLimit = convertedLimit.multiply(BigDecimal.valueOf((long)DateTimeUtils.getMonth(leaveDate))).divide(BigDecimal.valueOf(12L), 10, 4); } } else if (DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(joinDate)) { if (StringUtils.equals("D", annualLeaveConversionMethod)) { convertedLimit = convertedLimit.multiply(BigDecimal.valueOf((long)(12 - DateTimeUtils.getMonth(joinDate) + 1))).divide(BigDecimal.valueOf(12L), 10, 4); } else { convertedLimit = convertedLimit.multiply(BigDecimal.valueOf((long)(12 - DateTimeUtils.getMonth(joinDate) + 1))).divide(BigDecimal.valueOf(12L), 10, 4); } } BigDecimal standardLimitInteger = convertedLimit.setScale(0, RoundingMode.FLOOR); BigDecimal standardLimitDecimal = convertedLimit.subtract(standardLimitInteger); if (Double.doubleToLongBits((double)0.5F) == Double.doubleToLongBits(annualLeaveIntegerPrecision)) { if (StringUtils.equals("U", annualLeaveIntegerMethod)) { if (standardLimitDecimal.compareTo(BigDecimal.valueOf((double)0.5F)) > 0) { convertedLimit = convertedLimit.setScale(0, RoundingMode.CEILING); } else if (standardLimitDecimal.compareTo(BigDecimal.ZERO) > 0) { convertedLimit = standardLimitInteger.add(BigDecimal.valueOf((double)0.5F)); } else { convertedLimit = standardLimitInteger; } } else if (standardLimitDecimal.compareTo(BigDecimal.valueOf((double)0.5F)) > 0) { convertedLimit = standardLimitInteger.add(BigDecimal.valueOf((double)0.5F)); } else { convertedLimit = convertedLimit.setScale(0, RoundingMode.FLOOR); } } else if (StringUtils.equals("U", annualLeaveIntegerMethod)) { convertedLimit = convertedLimit.setScale(0, RoundingMode.CEILING); } else { convertedLimit = convertedLimit.setScale(0, RoundingMode.FLOOR); } if (entitlementLimit.compareTo(entitlementLimitOld) != 0 && year <= DateTimeUtils.getYear(nowDate)) { lastCarryforward = BigDecimal.ZERO; Map param = new HashMap(); param.put("year", year); param.put("personNum", iRowSet.getString("fnumber")); reCalList.add(param); } standardLimit = convertedLimit.add(initializedLimit).add(lastCarryforward).subtract(carryforward); realLimit = standardLimit.add(addOrSubLimit); remainLimit = realLimit.subtract(usedLimit).subtract(freezeLimit); BigDecimal addOrSub = BigDecimal.ZERO; if (remainLimit.compareTo(BigDecimal.valueOf(30L)) > 0) { addOrSub = BigDecimal.valueOf(30L).subtract(remainLimit); remainLimit = remainLimit.add(addOrSub); } if (StringUtils.isNotEmpty(limitId)) { HolidayLimitInfo holidayLimitInfo = new HolidayLimitInfo(); holidayLimitInfo.setId(BOSUuid.read(limitId)); holidayLimitInfo.setStandardLimit(standardLimit); addOrSubLimit = addOrSubLimit.add(addOrSub); holidayLimitInfo.setAddOrSubLimit(addOrSubLimit); holidayLimitInfo.setBigDecimal("convertedLimit", convertedLimit); holidayLimitInfo.setBigDecimal("entitlementLimit", entitlementLimit); holidayLimitInfo.setRealLimit(realLimit); holidayLimitInfo.setRemainLimit(remainLimit); holidayLimitInfo.setBigDecimal("lastCarryforward", lastCarryforward); holidayLimitInfo.setBigDecimal("Carryforward", carryforward); if (leaveDate != null && DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(leaveDate) && joinDate.compareTo(leaveDate) < 0) { holidayLimitInfo.setCycleEndDate(leaveDate); holidayLimitInfo.setDelayDate(leaveDate); holidayLimitInfo.setIsLeftConvert(true); } else { if (DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(joinDate)) { holidayLimitInfo.setCycleBeginDate(joinDate); int fprobation = iRowSet.getInt("FPROBATION"); if (fprobation > 0) { holidayLimitInfo.setEffectDate(iRowSet.getDate("FPLANFORMALDATE")); } else { holidayLimitInfo.setEffectDate(holidayLimitInfo.getCycleBeginDate()); } } holidayLimitInfo.setIsLeftConvert(false); } if (StringUtils.isNotEmpty(iRowSet.getString("FADMINORGID"))) { AdminOrgUnitInfo adminOrgUnitInfo = new AdminOrgUnitInfo(); adminOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FADMINORGID"))); holidayLimitInfo.setAdminOrgUnit(adminOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FPOSITIONID"))) { PositionInfo positionInfo = new PositionInfo(); positionInfo.setId(BOSUuid.read(iRowSet.getString("FPOSITIONID"))); holidayLimitInfo.setPosition(positionInfo); } holidayLimitInfo.setLastUpdateUser(ContextUtil.getCurrentUserInfo(ctx)); holidayLimitInfo.setLastUpdateTime(new Timestamp(nowDate.getTime())); if (leaveDate == null || 1 != isleftconvert || leaveDate.getTime() > holidayLimitInfo.getCycleEndDate().getTime()) { updateList.add(holidayLimitInfo); } } else { HolidayLimitInfo holidayLimitInfo = new HolidayLimitInfo(); holidayLimitInfo.setStandardLimit(standardLimit); holidayLimitInfo.setBigDecimal("convertedLimit", convertedLimit); holidayLimitInfo.setBigDecimal("entitlementLimit", entitlementLimit); holidayLimitInfo.setRealLimit(realLimit); holidayLimitInfo.setRemainLimit(remainLimit); holidayLimitInfo.setFreezeLimit(BigDecimal.ZERO); holidayLimitInfo.setUsedLimit(BigDecimal.ZERO); holidayLimitInfo.setAddOrSubLimit(addOrSub); holidayLimitInfo.setPreOverdraftLimit(BigDecimal.ZERO); if (DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(joinDate)) { holidayLimitInfo.setCycleBeginDate(joinDate); int fprobation = iRowSet.getInt("FPROBATION"); if (fprobation > 0) { holidayLimitInfo.setEffectDate(iRowSet.getDate("FPLANFORMALDATE")); } else { holidayLimitInfo.setEffectDate(holidayLimitInfo.getCycleBeginDate()); } } else if (fplanformaldate != null && DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(fplanformaldate)) { holidayLimitInfo.setCycleBeginDate(yearFirstDay); int fprobation = iRowSet.getInt("FPROBATION"); if (fprobation > 0) { holidayLimitInfo.setEffectDate(iRowSet.getDate("FPLANFORMALDATE")); } else { holidayLimitInfo.setEffectDate(holidayLimitInfo.getCycleBeginDate()); } } else { holidayLimitInfo.setCycleBeginDate(yearFirstDay); holidayLimitInfo.setEffectDate(holidayLimitInfo.getCycleBeginDate()); } if (leaveDate != null && DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(leaveDate)) { holidayLimitInfo.setCycleEndDate(leaveDate); holidayLimitInfo.setDelayDate(leaveDate); holidayLimitInfo.setIsLeftConvert(true); } else { holidayLimitInfo.setCycleEndDate(yearLastDay); holidayLimitInfo.setDelayDate(yearLastDay); } holidayLimitInfo.setYear(DateTimeUtils.getYear(yearFirstDay)); HolidayPolicyInfo holidayPolicyInfo = new HolidayPolicyInfo(); holidayPolicyInfo.setId(BOSUuid.read(holidayPolicyId)); holidayLimitInfo.setHolidayPolicy(holidayPolicyInfo); holidayLimitInfo.setHolidayUnit(HolidayTypeUnitEnum.day); holidayLimitInfo.setEnableCycle(true); PersonInfo personInfo = new PersonInfo(); personInfo.setId(BOSUuid.read(iRowSet.getString("fid"))); holidayLimitInfo.setProposer(personInfo); holidayLimitInfo.setStatus(HolidayLimitStatus.audited); if (StringUtils.isNotEmpty(iRowSet.getString("FADMINORGID"))) { AdminOrgUnitInfo adminOrgUnitInfo = new AdminOrgUnitInfo(); adminOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FADMINORGID"))); holidayLimitInfo.setAdminOrgUnit(adminOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FHRORGUNITID"))) { HROrgUnitInfo hrOrgUnitInfo = new HROrgUnitInfo(); hrOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FHRORGUNITID"))); holidayLimitInfo.setHrOrgUnit(hrOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FPOSITIONID"))) { PositionInfo positionInfo = new PositionInfo(); positionInfo.setId(BOSUuid.read(iRowSet.getString("FPOSITIONID"))); holidayLimitInfo.setPosition(positionInfo); } CtrlUnitInfo ctrlUnitInfo = new CtrlUnitInfo(); ctrlUnitInfo.setId(BOSUuid.read("00000000-0000-0000-0000-000000000000CCE7AED4")); holidayLimitInfo.setCU(ctrlUnitInfo); holidayLimitInfo.setCycleDate(yearLastDay); holidayLimitInfo.setCreator(ContextUtil.getCurrentUserInfo(ctx)); holidayLimitInfo.setCreateTime(new Timestamp(nowDate.getTime())); holidayLimitInfo.setLastUpdateUser(ContextUtil.getCurrentUserInfo(ctx)); holidayLimitInfo.setLastUpdateTime(new Timestamp(nowDate.getTime())); if (leaveDate == null || 1 != isleftconvert || leaveDate.getTime() > holidayLimitInfo.getCycleEndDate().getTime()) { addList.add(holidayLimitInfo); } } } } if (addList.size() > 0) { HolidayLimitFactory.getLocalInstance(ctx).addnewBatchData(addList); } if (updateList.size() > 0) { HolidayLimitFactory.getLocalInstance(ctx).updateBatchData(updateList); } if (deleteList.size() > 0) { IObjectPK[] iObjectPKS = new IObjectPK[deleteList.size()]; for(int i = 0; i < deleteList.size(); ++i) { iObjectPKS[i] = new ObjectUuidPK((String)deleteList.get(i)); } HolidayLimitFactory.getLocalInstance(ctx).deleteBatchData(iObjectPKS); } if (reCalList.size() > 0) { for(int i = 0; i < reCalList.size(); ++i) { int yearP = (Integer)((Map)reCalList.get(i)).get("year"); String personNumP = (String)((Map)reCalList.get(i)).get("personNum"); String lastYearSql = "/*dialect*/select p.fid,l.fid limitId,l.FSTANDARDLIMIT,l.FADDORSUBLIMIT,l.FUSEDLIMIT,l.FREEZELIMIT, coalesce(l.CFInitializedLimit, 0) CFInitializedLimit,coalesce(l.CFConvertedLimit, 0) CFConvertedLimit, coalesce(l.cflastcarryforward,0) cflastcarryforward,coalesce(l.cfcarryforward,0) cfcarryforward from t_bd_person p\n left join T_HR_ATS_AtsHolidayFile f on f.fproposerid = p.fid \n left join T_HR_ATS_HolidayPolicySet hps on f.fholidaypolicysetid = hps.fid \n left join T_HR_ATS_HolidayPolicy hp on hps.fid = hp.fholidaypolicysetid \n left join T_HR_ATS_HolidayType ht on ht.FNUMBER = 'JQLX000001Y' and ht.fid = hp.FHOLIDAYTYPEID \n left join T_HR_ATS_HolidayLimit l on hp.fid = l.FHOLIDAYPOLICYID and l.FPROPOSERID = p.fid and l.FCYCLEBEGINDATE >= p.fhiredate and l.FCYCLEBEGINDATE <= p.FLEFFDT and l.fyear = " + (yearP - 1) + "\n where f.fattendfilestate = 1 and p.fnumber = '" + personNumP + "' and l.fid is not null and ht.FNUMBER = 'JQLX000001Y' "; if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { lastYearSql = "/*dialect*/select p.fid,l.fid limitId,l.FSTANDARDLIMIT,l.FADDORSUBLIMIT,l.FUSEDLIMIT,l.FREEZELIMIT, isnull(l.CFInitializedLimit, 0) CFInitializedLimit,isnull(l.CFConvertedLimit, 0) CFConvertedLimit, isnull(l.cflastcarryforward,0) cflastcarryforward,isnull(l.cfcarryforward,0) cfcarryforward from t_bd_person p\n left join T_HR_ATS_AtsHolidayFile f on f.fproposerid = p.fid \n left join T_HR_ATS_HolidayPolicySet hps on f.fholidaypolicysetid = hps.fid \n left join T_HR_ATS_HolidayPolicy hp on hps.fid = hp.fholidaypolicysetid \n left join T_HR_ATS_HolidayType ht on ht.FNUMBER = 'JQLX000001Y' and ht.fid = hp.FHOLIDAYTYPEID \n left join T_HR_ATS_HolidayLimit l on hp.fid = l.FHOLIDAYPOLICYID and l.FPROPOSERID = p.fid and l.FCYCLEBEGINDATE >= p.fhiredate and l.FCYCLEBEGINDATE <= p.FLEFFDT and l.fyear = " + (yearP - 1) + "\n where f.fattendfilestate = 1 and p.fnumber = '" + personNumP + "' and l.fid is not null and ht.FNUMBER = 'JQLX000001Y' "; } IRowSet lastYearRowSet = DbUtil.executeQuery(ctx, lastYearSql); if (lastYearRowSet.next()) { HolidayLimitInfo lastYearHolidayLimitInfo = new HolidayLimitInfo(); BigDecimal lastYearConvertedLimit = lastYearRowSet.getBigDecimal("CFConvertedLimit"); BigDecimal lastYearInitializedLimit = lastYearRowSet.getBigDecimal("CFInitializedLimit"); BigDecimal lastYearLastCarryforward = lastYearRowSet.getBigDecimal("CFLastCarryforward"); BigDecimal lastYearCarryforward = BigDecimal.ZERO; BigDecimal lastYearStandardLimit = lastYearConvertedLimit.add(lastYearInitializedLimit).add(lastYearLastCarryforward).subtract(lastYearCarryforward); lastYearHolidayLimitInfo.setId(BOSUuid.read(lastYearRowSet.getString("limitId"))); lastYearHolidayLimitInfo.setBigDecimal("Carryforward", lastYearCarryforward); lastYearHolidayLimitInfo.setLastUpdateUser(ContextUtil.getCurrentUserInfo(ctx)); lastYearHolidayLimitInfo.setLastUpdateTime(new Timestamp(nowDate.getTime())); LeaveAllowanceFacadeFactory.getLocalInstance(ctx).carryForwardLeave(yearP, true, personNumP); } } } } catch (SQLException e) { throw new BOSException(e); } catch (ParseException e) { throw new BOSException(e); } catch (EASBizException e) { throw new BOSException(e); } } private void createNursingOrParentalLeave(Context ctx, int year, String personNum, String policyNumber, int initialLimit, String cnLeave) throws BOSException { try { this.initialGlobalParam(ctx); String personFamilyTempTable = this.createPersonFamilyTempTable(ctx, cnLeave); Date nowDate = new Date(); Date currentDate = new Date(); if (year != 0) { currentDate = DateTimeUtils.parseDate(year + "-12-31"); } else { year = DateTimeUtils.getYear(currentDate); } String yearFirstDayStr = DateTimeUtils.getYear(currentDate) + "-01-01"; Date yearFirstDay = DateTimeUtils.parseDate(yearFirstDayStr); String yearLastDayStr = DateTimeUtils.getYear(currentDate) + "-12-31"; Date yearLastDay = DateTimeUtils.parseDate(yearLastDayStr); List personNumberList = new ArrayList(); CoreBaseCollection addList = new CoreBaseCollection(); CoreBaseCollection updateList = new CoreBaseCollection(); String personNumStr = ""; if (StringUtils.isNotEmpty(personNum)) { String[] personNumArr = personNum.split(","); for(int i = 0; i < personNumArr.length; ++i) { personNumberList.add("'" + personNumArr[i] + "'"); } personNumStr = String.join(",", personNumberList); } String clearQuotaSql = " update T_HR_ATS_HolidayLimit set FStandardLimit = 0, FRealLimit = FAddOrSubLimit,FRemainLimit = FAddOrSubLimit-FUsedLimit-FreezeLimit where fid in ( select l.fid from T_HR_ATS_HolidayLimit l left join T_HR_ATS_HolidayPolicy hp on hp.fid = l.FHOLIDAYPOLICYID left join T_HR_ATS_HolidayType ht on hp.FHOLIDAYTYPEID = ht.fid left join t_bd_person p on p.fid = l.FPROPOSERID where ht.FNUMBER = '" + policyNumber + "' and l.FYEAR = " + year + " and l.FCYCLEBEGINDATE >= p.fhiredate and l.FCYCLEBEGINDATE <= p.FLEFFDT "; if (StringUtils.isNotEmpty(personNumStr)) { clearQuotaSql = clearQuotaSql + " and p.fnumber in (" + personNumStr + ")) "; } else { clearQuotaSql = clearQuotaSql + " ) "; } DbUtil.execute(ctx, clearQuotaSql); String deleteZeroQuotaSql = "delete from T_HR_ATS_HolidayLimit where FHOLIDAYPOLICYID in (select hp.fid from T_HR_ATS_HolidayPolicy hp left join T_HR_ATS_HolidayType ht on hp.FHOLIDAYTYPEID = ht.fid where ht.FNUMBER = '" + policyNumber + "') and FYEAR = " + year + " and FStandardLimit = 0 and FRealLimit = 0 and FAddOrSubLimit = 0 and FRemainLimit = 0 and FUsedLimit = 0 and FreezeLimit = 0 "; if (StringUtils.isNotEmpty(personNumStr)) { deleteZeroQuotaSql = deleteZeroQuotaSql + " and FProposerID in (select fid from t_bd_person where fnumber in (" + personNumStr + ") ) "; } DbUtil.execute(ctx, deleteZeroQuotaSql); String sql = "/*dialect*/select f.FPERSONID,p.fnumber, case when to_char(f.CFStartTime,'yyyy') < " + year + " then to_date('" + year + "-01-01','yyyy-MM-dd') else f.CFStartTime end CFStartTime, case when f.CFEndTime is null or to_char(f.CFEndTime,'yyyy') > " + year + " then to_date('" + year + "-12-31','yyyy-MM-dd') else f.CFEndTime end CFEndTime , case when rbe.fid is null then null else rbe.FBIZDATE end leaveDate,p.fhiredate,hf.FADMINORGUNITID, hf.FHRORGUNITID ,hf.FPOSITIONID, l.fid limitId,ebe.FPROBATION, ebe.FPLANFORMALDATE,hp.fid holidayPolicyId,l.FADDORSUBLIMIT,l.FUSEDLIMIT,l.FREEZELIMIT,l.FCYCLEBEGINDATE,l.FCYCLEENDDATE from " + personFamilyTempTable + " f inner join t_bd_person p on p.fid = f.FPERSONID inner join T_HR_ATS_AtsHolidayFile hf on hf.fproposerid = f.FPERSONID inner join T_HR_ATS_HolidayPolicySet hps on hf.fholidaypolicysetid = hps.fid inner join T_HR_ATS_HolidayPolicy hp on hps.fid = hp.fholidaypolicysetid inner join T_HR_ATS_HolidayType ht on ht.FNUMBER = '" + policyNumber + "' and ht.fid = hp.FHOLIDAYTYPEID left join T_HR_ATS_HolidayLimit l on hp.fid = l.FHOLIDAYPOLICYID and l.FPROPOSERID = f.FPERSONID and l.FYEAR = " + year + " and l.FCYCLEBEGINDATE >= p.fhiredate and l.FCYCLEENDDATE >= p.fhiredate and (case when to_char(f.CFStartTime,'yyyy') < " + year + " then to_date('" + year + "-01-01','yyyy-MM-dd') else f.CFStartTime end) <= l.FCYCLEBEGINDATE and (case when f.CFEndTime is null or to_char(f.CFEndTime,'yyyy') > " + year + " then to_date('" + year + "-12-31','yyyy-MM-dd') else f.CFEndTime end) >=l.FCYCLEBEGINDATE left join ( select rownum rownumber,ebe.FPERSONID, ebe.FPROBATION,ebe.FPLANFORMALDATE, ebe.FBIZDATE from T_HR_EmpEnrollBizBillEntry ebe inner join (select re1.fpersonid,max(re1.fbizdate) fbizdate,count(fid) from T_HR_EmpEnrollBizBillEntry re1 group by re1.fpersonid) re1 on ebe.fpersonid=re1.fpersonid and re1.fbizdate=ebe.fbizdate left join T_HR_EmpEnrollBizBill eb on ebe.FBILLID = eb.FID and eb.FBILLSTATE = 3 order by ebe.fbizdate desc) ebe on ebe.FPERSONID = p.fid and ebe.FBIZDATE = p.fhiredate left join( select rownum rownumber,rbe.fid, rbe.FPERSONID, rbe.FENTERDATE, rbe.CFWcategoryID, rbe.FBIZDATE from T_HR_ResignBizBillEntry rbe inner join (select re1.fpersonid,max(re1.fbizdate) fbizdate,count(fid) from T_HR_ResignBizBillEntry re1 group by re1.fpersonid) re1 on rbe.fpersonid=re1.fpersonid and re1.fbizdate=rbe.fbizdate left join T_HR_ResignBizBill rb on rb.fid = rbe.FBILLID and rb.FBILLSTATE = 3 order by rbe.fbizdate desc ) rbe on rbe.FPERSONID = p.fid and p.fhiredate = rbe.FENTERDATE where ht.FNUMBER = '" + policyNumber + "' and hf.fattendfilestate = 1 and (case when to_char(f.CFStartTime,'yyyy') < " + year + " then to_date('" + year + "-01-01','yyyy-MM-dd') else f.CFStartTime end) <= to_date('" + yearLastDayStr + "','yyyy-MM-dd') and (case when f.CFEndTime is null or to_char(f.CFEndTime,'yyyy') > " + year + " then to_date('" + year + "-12-31','yyyy-MM-dd') else f.CFEndTime end) >= to_date('" + yearFirstDayStr + "','yyyy-MM-dd') and to_char(p.fhiredate,'yyyy')<=" + year + " and to_char(p.FLEFFDT,'yyyy')>=" + year; if (StringUtils.isNotEmpty(personNumStr)) { sql = sql + " and p.fnumber in (" + personNumStr + ") "; } IRowSet iRowSet = DbUtil.executeQuery(ctx, sql); while(iRowSet.next()) { String holidayPolicyId = iRowSet.getString("holidayPolicyId"); if (!StringUtils.isEmpty(holidayPolicyId)) { BigDecimal standardLimit = BigDecimal.valueOf((long)initialLimit); String limitId = iRowSet.getString("limitId"); java.sql.Date joinDate = iRowSet.getDate("fhiredate"); java.sql.Date fplanformaldate = iRowSet.getDate("FPLANFORMALDATE"); java.sql.Date leaveDate = iRowSet.getDate("leaveDate"); java.sql.Date cfStartTime = iRowSet.getDate("CFStartTime"); java.sql.Date effectDate = iRowSet.getDate("CFStartTime"); java.sql.Date cfEndTime = iRowSet.getDate("CFEndTime"); if (DateTimeUtils.getYear(joinDate) == DateTimeUtils.getYear(cfStartTime) && joinDate.getTime() > cfStartTime.getTime()) { cfStartTime = joinDate; effectDate = iRowSet.getDate("FPLANFORMALDATE") != null ? iRowSet.getDate("FPLANFORMALDATE") : joinDate; } else if (fplanformaldate != null && DateTimeUtils.getYear(fplanformaldate) == DateTimeUtils.getYear(cfStartTime) && fplanformaldate.getTime() > cfStartTime.getTime()) { effectDate = fplanformaldate; } if (leaveDate != null && DateTimeUtils.getYear(currentDate) == DateTimeUtils.getYear(leaveDate) && DateTimeUtils.getYear(leaveDate) == DateTimeUtils.getYear(cfEndTime) && cfEndTime.getTime() > leaveDate.getTime()) { cfEndTime = leaveDate; } if (cfStartTime.getTime() <= cfEndTime.getTime()) { standardLimit = standardLimit.multiply(BigDecimal.valueOf((long)(DateTimeUtils.getMonth(cfEndTime) - DateTimeUtils.getMonth(cfStartTime) + 1))).divide(BigDecimal.valueOf(12L), 10, 4); BigDecimal standardLimitInteger = standardLimit.setScale(0, RoundingMode.FLOOR); BigDecimal standardLimitDecimal = standardLimit.subtract(standardLimitInteger); if (Double.doubleToLongBits((double)0.5F) == Double.doubleToLongBits(nursingAndParentalLeaveIntegerPrecision)) { if (StringUtils.equals("U", nursingAndParentalLeaveIntegerMethod)) { if (standardLimitDecimal.compareTo(BigDecimal.valueOf((double)0.5F)) > 0) { standardLimit = standardLimit.setScale(0, RoundingMode.CEILING); } else if (standardLimitDecimal.compareTo(BigDecimal.ZERO) > 0) { standardLimit = standardLimitInteger.add(BigDecimal.valueOf((double)0.5F)); } else { standardLimit = standardLimitInteger; } } else if (standardLimitDecimal.compareTo(BigDecimal.valueOf((double)0.5F)) > 0) { standardLimit = standardLimitInteger.add(BigDecimal.valueOf((double)0.5F)); } else { standardLimit = standardLimit.setScale(0, RoundingMode.FLOOR); } } else if (StringUtils.equals("U", nursingAndParentalLeaveIntegerMethod)) { standardLimit = standardLimit.setScale(0, RoundingMode.CEILING); } else { standardLimit = standardLimit.setScale(0, RoundingMode.FLOOR); } if (StringUtils.isNotEmpty(limitId)) { HolidayLimitInfo holidayLimitInfo = new HolidayLimitInfo(); holidayLimitInfo.setId(BOSUuid.read(limitId)); holidayLimitInfo.setStandardLimit(standardLimit); holidayLimitInfo.setRealLimit(standardLimit.add(iRowSet.getBigDecimal("FADDORSUBLIMIT"))); holidayLimitInfo.setRemainLimit(standardLimit.add(iRowSet.getBigDecimal("FADDORSUBLIMIT")).subtract(iRowSet.getBigDecimal("FUSEDLIMIT")).subtract(iRowSet.getBigDecimal("FreezeLimit"))); holidayLimitInfo.setCycleBeginDate(cfStartTime); holidayLimitInfo.setCycleEndDate(cfEndTime); holidayLimitInfo.setEffectDate(effectDate); holidayLimitInfo.setDelayDate(yearLastDay); if (leaveDate != null && leaveDate.getTime() == cfEndTime.getTime()) { holidayLimitInfo.setDelayDate(cfEndTime); } if (StringUtils.isNotEmpty(iRowSet.getString("FADMINORGUNITID"))) { AdminOrgUnitInfo adminOrgUnitInfo = new AdminOrgUnitInfo(); adminOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FADMINORGUNITID"))); holidayLimitInfo.setAdminOrgUnit(adminOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FHRORGUNITID"))) { HROrgUnitInfo hrOrgUnitInfo = new HROrgUnitInfo(); hrOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FHRORGUNITID"))); holidayLimitInfo.setHrOrgUnit(hrOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FPOSITIONID"))) { PositionInfo positionInfo = new PositionInfo(); positionInfo.setId(BOSUuid.read(iRowSet.getString("FPOSITIONID"))); holidayLimitInfo.setPosition(positionInfo); } holidayLimitInfo.setLastUpdateUser(ContextUtil.getCurrentUserInfo(ctx)); holidayLimitInfo.setLastUpdateTime(new Timestamp(nowDate.getTime())); updateList.add(holidayLimitInfo); } else { HolidayLimitInfo holidayLimitInfo = new HolidayLimitInfo(); holidayLimitInfo.setStandardLimit(standardLimit); holidayLimitInfo.setRealLimit(standardLimit); holidayLimitInfo.setRemainLimit(standardLimit); holidayLimitInfo.setFreezeLimit(BigDecimal.ZERO); holidayLimitInfo.setUsedLimit(BigDecimal.ZERO); holidayLimitInfo.setAddOrSubLimit(BigDecimal.ZERO); holidayLimitInfo.setPreOverdraftLimit(BigDecimal.ZERO); holidayLimitInfo.setCycleBeginDate(cfStartTime); holidayLimitInfo.setCycleEndDate(cfEndTime); holidayLimitInfo.setEffectDate(effectDate); holidayLimitInfo.setDelayDate(yearLastDay); if (leaveDate != null && leaveDate.getTime() == cfEndTime.getTime()) { holidayLimitInfo.setDelayDate(cfEndTime); } holidayLimitInfo.setYear(DateTimeUtils.getYear(yearFirstDay)); HolidayPolicyInfo holidayPolicyInfo = new HolidayPolicyInfo(); holidayPolicyInfo.setId(BOSUuid.read(holidayPolicyId)); holidayLimitInfo.setHolidayPolicy(holidayPolicyInfo); holidayLimitInfo.setHolidayUnit(HolidayTypeUnitEnum.day); holidayLimitInfo.setEnableCycle(true); PersonInfo personInfo = new PersonInfo(); personInfo.setId(BOSUuid.read(iRowSet.getString("FPERSONID"))); holidayLimitInfo.setProposer(personInfo); holidayLimitInfo.setStatus(HolidayLimitStatus.audited); if (StringUtils.isNotEmpty(iRowSet.getString("FADMINORGUNITID"))) { AdminOrgUnitInfo adminOrgUnitInfo = new AdminOrgUnitInfo(); adminOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FADMINORGUNITID"))); holidayLimitInfo.setAdminOrgUnit(adminOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FHRORGUNITID"))) { HROrgUnitInfo hrOrgUnitInfo = new HROrgUnitInfo(); hrOrgUnitInfo.setId(BOSUuid.read(iRowSet.getString("FHRORGUNITID"))); holidayLimitInfo.setHrOrgUnit(hrOrgUnitInfo); } if (StringUtils.isNotEmpty(iRowSet.getString("FPOSITIONID"))) { PositionInfo positionInfo = new PositionInfo(); positionInfo.setId(BOSUuid.read(iRowSet.getString("FPOSITIONID"))); holidayLimitInfo.setPosition(positionInfo); } CtrlUnitInfo ctrlUnitInfo = new CtrlUnitInfo(); ctrlUnitInfo.setId(BOSUuid.read("00000000-0000-0000-0000-000000000000CCE7AED4")); holidayLimitInfo.setCU(ctrlUnitInfo); holidayLimitInfo.setCycleDate(yearLastDay); holidayLimitInfo.setCreator(ContextUtil.getCurrentUserInfo(ctx)); holidayLimitInfo.setCreateTime(new Timestamp(nowDate.getTime())); holidayLimitInfo.setLastUpdateUser(ContextUtil.getCurrentUserInfo(ctx)); holidayLimitInfo.setLastUpdateTime(new Timestamp(nowDate.getTime())); addList.add(holidayLimitInfo); } } } } if (addList.size() > 0) { HolidayLimitFactory.getLocalInstance(ctx).addnewBatchData(addList); } if (updateList.size() > 0) { HolidayLimitFactory.getLocalInstance(ctx).updateBatchData(updateList); } } catch (SQLException | ParseException e) { throw new BOSException(e); } catch (EASBizException e) { throw new BOSException(e); } } private String createPersonFamilyTempTable(Context ctx, String cnLeave) throws BOSException { String dbType = ContextUtil.getDbType(ctx); String view_pf_PersonFamilyTempTable = ""; if (StringUtils.equalsIgnoreCase(nursingAndParentalLeaveUpdateSql, "Y")) { if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { String dropView = " IF EXISTS (SELECT * FROM sys.views WHERE name = 'view_pf_DateRanges_" + cnLeave + "') DROP VIEW view_pf_DateRanges_" + cnLeave; DbUtil.execute(ctx, dropView); dropView = " IF EXISTS (SELECT * FROM sys.views WHERE name = 'view_pf_CTE_" + cnLeave + "') DROP VIEW view_pf_CTE_" + cnLeave; DbUtil.execute(ctx, dropView); dropView = " IF EXISTS (SELECT * FROM sys.views WHERE name = 'view_pf_MergedRanges_" + cnLeave + "') DROP VIEW view_pf_MergedRanges_" + cnLeave; DbUtil.execute(ctx, dropView); dropView = " IF EXISTS (SELECT * FROM sys.views WHERE name = 'view_pf_GroupedRanges_" + cnLeave + "') DROP VIEW view_pf_GroupedRanges_" + cnLeave; DbUtil.execute(ctx, dropView); } String createView = " /*dialect*/create or replace view view_pf_DateRanges_" + cnLeave + " as ( select CFStartTime StartDate,CFEndTime EndDate,FPERSONID PersonID from t_hr_personfamily where CFCnleave = '" + cnLeave + "' ) "; if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { createView = " /*dialect*/create view view_pf_DateRanges_" + cnLeave + " as ( select CFStartTime StartDate,CFEndTime EndDate,FPERSONID PersonID from t_hr_personfamily where CFCnleave = '" + cnLeave + "' ) "; } DbUtil.execute(ctx, createView); createView = " /*dialect*/create or replace view view_pf_CTE_" + cnLeave + " AS ( SELECT PersonID,StartDate, EndDate,LAG(EndDate) OVER (PARTITION BY PersonID ORDER BY StartDate) AS prev_end_date FROM view_pf_DateRanges_" + cnLeave + ") "; if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { createView = " /*dialect*/create view view_pf_CTE_" + cnLeave + " AS ( SELECT PersonID,StartDate, EndDate,LAG(EndDate) OVER (PARTITION BY PersonID ORDER BY StartDate) AS prev_end_date FROM view_pf_DateRanges_" + cnLeave + ") "; } DbUtil.execute(ctx, createView); createView = " /*dialect*/create or replace view view_pf_MergedRanges_" + cnLeave + " AS ( SELECT PersonID,StartDate,EndDate,CASE WHEN StartDate <= prev_end_date + INTERVAL '1 day' THEN 0 ELSE 1 END AS is_overlap FROM view_pf_CTE_" + cnLeave + ") "; if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { createView = " /*dialect*/create view view_pf_MergedRanges_" + cnLeave + " AS ( SELECT PersonID,StartDate,EndDate,CASE WHEN StartDate <= DATEADD(day, 1, prev_end_date) THEN 0 ELSE 1 END AS is_overlap FROM view_pf_CTE_" + cnLeave + ") "; } DbUtil.execute(ctx, createView); createView = " /*dialect*/create or replace view view_pf_GroupedRanges_" + cnLeave + " AS ( SELECT PersonID,StartDate,EndDate,SUM(is_overlap) OVER (PARTITION BY PersonID ORDER BY StartDate) AS grp FROM view_pf_MergedRanges_" + cnLeave + ") "; if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { createView = " /*dialect*/create view view_pf_GroupedRanges_" + cnLeave + " AS ( SELECT PersonID,StartDate,EndDate,SUM(is_overlap) OVER (PARTITION BY PersonID ORDER BY StartDate) AS grp FROM view_pf_MergedRanges_" + cnLeave + ") "; } DbUtil.execute(ctx, createView); } view_pf_PersonFamilyTempTable = " (SELECT PersonID fpersonid,MIN(StartDate) AS CFStartTime,MAX(EndDate) AS CFEndTime FROM view_pf_GroupedRanges_" + cnLeave + " GROUP BY PersonID, grp ORDER BY PersonID, CFStartTime) "; if (StringUtils.equalsIgnoreCase("MS SQL Server", dbType)) { view_pf_PersonFamilyTempTable = " (SELECT top 100 percent PersonID fpersonid,MIN(StartDate) AS CFStartTime,MAX(EndDate) AS CFEndTime FROM view_pf_GroupedRanges_" + cnLeave + " GROUP BY PersonID, grp ORDER BY PersonID, CFStartTime) "; } return view_pf_PersonFamilyTempTable; } }