· 10 years ago · Sep 05, 2016, 08:38 AM
1package com.dirac.util;
2
3import java.math.BigDecimal;
4import java.sql.PreparedStatement;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.SQLIntegrityConstraintViolationException;
8import java.sql.Timestamp;
9import java.util.HashMap;
10import java.util.List;
11import java.util.Map;
12import java.util.Properties;
13
14import org.compiere.model.MColumn;
15import org.compiere.model.MSysConfig;
16import org.compiere.model.PO;
17import org.compiere.util.DB;
18import org.compiere.util.Env;
19import org.compiere.util.Trx;
20
21import com.dirac.bmodel.MHREmployee;
22import com.dirac.bmodel.MHREmployeeCalendar;
23import com.dirac.bmodel.MHROvertimeRule;
24import com.dirac.bmodel.MHRPunishment;
25import com.dirac.common.util.DSClientModel;
26import com.dirac.common.util.DateUtil;
27
28public class UpdateEmployeeCalendarUtil {
29 private Map<Integer, PO> punishmentRules;
30
31 private Map<Integer, Map<Integer, BigDecimal>> punishmentRepeats;
32
33 private Properties ctx;
34 private Timestamp DateFrom;
35 private Timestamp DateTo;
36 private int HR_Employee_ID;
37 private int HR_Department_ID;
38 private int HR_Job_ID;
39 private int HR_Work_Group_ID;
40 private int currentEmployeeID;
41 private int currentEmployeeLateDays;
42 private int currentEmployeeAbsenceDays;
43 private int currentEmployeeNoDepartureDays;
44 private boolean isRTypeEmployee;
45
46 private Trx mTrx;
47
48
49 public UpdateEmployeeCalendarUtil() {
50 punishmentRules = new HashMap<Integer, PO>();
51 punishmentRepeats = new HashMap<Integer, Map<Integer, BigDecimal>>();
52 currentEmployeeID = 0;
53 currentEmployeeLateDays = 0;
54 currentEmployeeAbsenceDays = 0;
55 currentEmployeeNoDepartureDays = 0;
56 isRTypeEmployee = false;
57 }
58
59 public UpdateEmployeeCalendarUtil(Properties ctx, Trx mTrx, Timestamp DateFrom, Timestamp DateTo, int HR_Employee_ID, int HR_Department_ID, int HR_Job_ID,int HR_Work_Group_ID) {
60 this();
61 this.ctx = ctx;
62 this.mTrx = mTrx;
63 this.DateFrom = DateFrom;
64 this.DateTo = DateTo;
65 this.HR_Employee_ID = HR_Employee_ID;
66 this.HR_Department_ID = HR_Department_ID;
67 this.HR_Job_ID = HR_Job_ID;
68 this.HR_Work_Group_ID = HR_Work_Group_ID;
69 }
70
71 public boolean update() {
72 // get all records from HREmployeeCalendarBase_V
73 String sql = getAllRecordsFromHREmpCalBaseVSqlString();
74 PreparedStatement ps = null;
75 ResultSet rs = null;
76 try {
77 ps = DB.prepareStatement(sql,null);
78 ps.setTimestamp(1, DateFrom);
79 ps.setTimestamp(2, DateTo);
80 if (HR_Employee_ID == 0) //hire / fire in period
81 {
82 ps.setInt(3, Env.getAD_Client_ID(ctx));
83 ps.setTimestamp(4, DateTo);
84 ps.setTimestamp(5, DateFrom);
85 }
86 rs = ps.executeQuery();
87 int i= 0;
88 while(rs.next()) {
89 int C_BPartner_ID = rs.getInt("C_BPartner_ID");
90 Timestamp workDate = rs.getTimestamp("workDate");
91 int HR_Work_Shift_ID = rs.getInt("HR_work_shift_ID");
92 int employeeCalendarID = getEmployeeCalendarID(C_BPartner_ID, workDate, HR_Work_Shift_ID);
93
94 MHREmployee emp = new MHREmployee(ctx, rs.getInt("HR_Employee_ID"), null);
95 //Hamed
96 int cid = MColumn.getColumn_ID(MHREmployee.Table_Name, "isOvertimeApplicable");
97 boolean isOvertimeApplicable = cid > 0 ? emp.isOvertimeApplicable() : true;
98 //
99 MHREmployeeCalendar employeeCalendar = new MHREmployeeCalendar(ctx, employeeCalendarID, mTrx.getTrxName());
100
101 setCopiedFields(employeeCalendar, rs);
102
103 Timestamp shiftFrom = rs.getTimestamp("shiftFromTime");
104 Timestamp shiftTo = rs.getTimestamp("shiftToTime");
105 Timestamp inTime = rs.getTimestamp("IncomingTime");
106 Timestamp outTime = rs.getTimestamp("OutgoingTime");
107
108 Timestamp Dep_permissionStartTime = rs.getTimestamp("Dep_startTime");
109 Timestamp Dep_permissionEndTime = rs.getTimestamp("Dep_endTime");
110
111 //if (shiftFrom != null) shiftFrom = DateUtil.getCombineTimeStamp(workDate, shiftFrom);
112 //if (shiftTo != null) shiftTo = DateUtil.getCombineTimeStamp(workDate, shiftTo);
113 //if (inTime != null) inTime = DateUtil.getCombineTimeStamp(workDate, inTime);
114 //if (outTime != null) outTime = DateUtil.getCombineTimeStamp(workDate, outTime);
115
116 employeeCalendar.setShiftFrom(shiftFrom);
117 employeeCalendar.setShiftTo(shiftTo);
118 employeeCalendar.setInTime(inTime);
119 employeeCalendar.setOutTime(outTime);
120
121 // moved before because they can be used in the if statement for R Type employee
122 boolean isOvertimeBeforeApplicable = rs.getString("isOvertimeBeforeApplicable").equalsIgnoreCase("Y");
123 Timestamp overtimeBeforeLimit = rs.getTimestamp("overtimeBeforeLimit");
124 Timestamp overtimeAfterStart = rs.getTimestamp("overtimeAfterStart");
125 Timestamp overtimeAfterMax = rs.getTimestamp("overtimeAfterMax");
126 Timestamp nightOTStart = rs.getTimestamp("nightOTStart");
127 Timestamp nightOTEnd = rs.getTimestamp("nightOTEnd");
128 String roundRule = rs.getString("roundRule");
129 BigDecimal roundBaseHours = rs.getBigDecimal("roundBaseHours");
130
131 //if (overtimeBeforeLimit != null) overtimeBeforeLimit = DateUtil.getCombineTimeStamp(workDate, overtimeBeforeLimit);
132 //if (overtimeAfterStart != null) overtimeAfterStart = DateUtil.getCombineTimeStamp(workDate, overtimeAfterStart);
133 //if (overtimeAfterMax != null) overtimeAfterMax = DateUtil.getCombineTimeStamp(workDate, overtimeAfterMax);
134 //if (nightOTStart != null) nightOTStart = DateUtil.getCombineTimeStamp(workDate, nightOTStart);
135 //if (nightOTEnd != null) nightOTEnd = DateUtil.getCombineTimeStamp(workDate, nightOTEnd);
136
137 if (inTime == null) { // either employee is absent or workday is official holiday or weekend or employee is on a leave
138 setDefaultsForOffDay(employeeCalendar); // will be overwritten if employee of type R, but I guess it's better than doing lots of if-checks
139
140 if (isCurrentEmployeeOfTypeR(employeeCalendar.getHR_Employee_ID())) {
141 setDefaulstForRTypeEmployee(employeeCalendar);
142
143 checkPermissionForRTypeEmployee(employeeCalendar);
144
145 if (isRTypeEmployeeAbsent(employeeCalendar)) {
146 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Absent);
147 calculateAbsentPunishDays(employeeCalendar);
148 } else if (employeeCalendar.getHR_LeaveType_ID() != 0) {
149 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Leave);
150 employeeCalendar.setInTime(null);
151 employeeCalendar.setInStatus(null);
152 employeeCalendar.setOutTime(null);
153 employeeCalendar.setOutStatus(null);
154 } else {
155 if (!employeeCalendar.getInStatus().equalsIgnoreCase(MHREmployeeCalendar.INSTATUS_Permission) && isRTypeEmployeeLate(employeeCalendar)) {
156 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
157 calculateLatePunishDays(employeeCalendar);
158 }
159 if(isRTypeEmployeeEarlyDeparture(employeeCalendar)){
160 calculateEarlyDepartureMinutes(employeeCalendar, shiftTo, employeeCalendar.getOutTime(), Dep_permissionStartTime, Dep_permissionEndTime);
161 calculateEarlyDeparturePunishDays(employeeCalendar);
162 }
163 if (isOvertimeApplicable && isRTypeEmployeeOT(employeeCalendar)) {
164 ;
165 } else if (employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_Weekend) || employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_Official) || employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_NotApplicable)) {
166 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_NotApplicable);
167 employeeCalendar.setInTime(null);
168 employeeCalendar.setInStatus(null);
169 employeeCalendar.setOutTime(null);
170 employeeCalendar.setOutStatus(null);
171 }
172 }
173 setActualHoursForRTypeEmployee(employeeCalendar);
174 } else {
175 if (employeeCalendar.getHR_LeaveType_ID() != 0) {
176 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Leave);
177 employeeCalendar.setAbsentPunishDays(Env.ZERO);
178 } else if (employeeCalendar.getDayType()!= null && employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_Work)) {
179 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Absent);
180 calculateAbsentPunishDays(employeeCalendar);
181 } else { // holiday or weekend
182 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_NotApplicable);
183 employeeCalendar.setAbsentPunishDays(Env.ZERO);
184 }
185 }
186
187 } else { // employee attended
188
189 if(isOvertimeApplicable)
190 calculateOvertime(employeeCalendar, shiftFrom, shiftTo, inTime, outTime, isOvertimeBeforeApplicable,
191 overtimeBeforeLimit, overtimeAfterStart, overtimeAfterMax, nightOTStart, nightOTEnd, roundRule, roundBaseHours);
192
193 Timestamp permissionStartTime = rs.getTimestamp("startTime");
194 Timestamp permissionEndTime = rs.getTimestamp("endTime");
195 Timestamp late = rs.getTimestamp("Late");
196 Timestamp lateLimit = rs.getTimestamp("LateLimit");
197
198// if (permissionStartTime != null) permissionStartTime = DateUtil.getCombineTimeStamp(workDate, permissionStartTime);
199// if (permissionEndTime != null) permissionEndTime = DateUtil.getCombineTimeStamp(workDate, permissionEndTime);
200// if (late != null) late = DateUtil.getCombineTimeStamp(workDate, late);
201// if (lateLimit != null) lateLimit = DateUtil.getCombineTimeStamp(workDate, lateLimit);
202
203 calculateLateTime(employeeCalendar, shiftFrom, inTime, late, lateLimit, permissionStartTime, permissionEndTime);
204 calculateEarlyDepartureMinutes(employeeCalendar, shiftTo, outTime, Dep_permissionStartTime, Dep_permissionEndTime);
205 calculateEarlyDeparturePunishDays(employeeCalendar);
206 //calculate any punishments
207 if (employeeCalendar.getInStatus().equalsIgnoreCase(MHREmployeeCalendar.INSTATUS_Late))
208 calculateLatePunishDays(employeeCalendar);
209 else {
210 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
211 employeeCalendar.setLateMinutes(Env.ZERO);
212 }
213 employeeCalendar.setAbsentPunishDays(Env.ZERO);; // no need to calculate absent punish days since inStatus != null means no absence
214 if (employeeCalendar.getOutTime() == null) {
215 employeeCalendar.setOutStatus(MHREmployeeCalendar.OUTSTATUS_NoLeave);
216 calculateNoDeparturePunishDays(employeeCalendar);
217 } else {
218 employeeCalendar.setNoDeparturePunishDays(Env.ZERO);
219 }
220 }
221 employeeCalendar.save(mTrx.getTrxName());
222 i++;
223 System.out.println("Update Calendar #" + i);
224 }
225 } catch (SQLIntegrityConstraintViolationException e) {
226 e.printStackTrace();
227 return false;
228 } catch (SQLException e) {
229 e.printStackTrace();
230 return false;
231 } catch (Exception e) {
232 e.printStackTrace();
233 return false;
234 } finally {
235 DB.close(rs, ps);
236 rs = null; ps = null;
237 }
238 return true;
239 }
240
241 // set overtime and punishments, except absence punishment, to zero in days when employees instatus is null
242 private void setDefaultsForOffDay(MHREmployeeCalendar employeeCalendar) {
243
244 employeeCalendar.setOTBD(Env.ZERO);
245 employeeCalendar.setOTBN(Env.ZERO);
246 employeeCalendar.setOTAD(Env.ZERO);
247 employeeCalendar.setOTAN(Env.ZERO);
248 employeeCalendar.setOTWD(Env.ZERO);
249 employeeCalendar.setOTWN(Env.ZERO);
250 employeeCalendar.setOTHD(Env.ZERO);
251 employeeCalendar.setOTHN(Env.ZERO);
252 employeeCalendar.setOTT(Env.ZERO);
253 employeeCalendar.setLateMinutes(Env.ZERO);
254 employeeCalendar.setActualHours(Env.ZERO);
255 employeeCalendar.setLatePunishDays(Env.ZERO);
256 employeeCalendar.setNoDeparturePunishDays(Env.ZERO);
257 employeeCalendar.setEarlyDeparDays(Env.ZERO);
258 employeeCalendar.setEarlyDeparMinutes(Env.ZERO);
259 }
260
261 // set the fields that are copied directly from the result set
262 private void setCopiedFields(MHREmployeeCalendar employeeCalendar,
263 ResultSet rs) throws SQLException {
264
265 employeeCalendar.setHR_Employee_ID(rs.getInt("HR_employee_ID"));
266 employeeCalendar.setC_BPartner_ID(rs.getInt("C_BPartner_ID"));
267 employeeCalendar.setWorkDate(rs.getTimestamp("workDate"));
268 employeeCalendar.setDayType(rs.getString("dayType"));
269 employeeCalendar.setHR_Work_Group_ID(rs.getInt("HR_work_group_ID"));
270 employeeCalendar.setHR_Work_Shift_ID(rs.getInt("HR_work_shift_ID"));
271 employeeCalendar.setHR_OvertimeRule_ID(rs.getInt("HR_overtimeRule_ID"));
272 employeeCalendar.setHR_Work_GroupShift_ID(rs.getInt("HR_work_groupShift_ID"));
273 employeeCalendar.setHR_EmployeeShift_ID(rs.getInt("HR_employeeShift_ID"));
274 employeeCalendar.setHR_LateRule_ID(rs.getInt("HR_lateRule_ID"));
275 employeeCalendar.setHR_AbsentRule_ID(rs.getInt("HR_absentRule_ID"));
276 employeeCalendar.setHR_EarlyDepartureRule_ID(rs.getInt("HR_EarlyDepartureRule_ID"));
277 employeeCalendar.setHR_NoDepartureRule_ID(rs.getInt("HR_noDepartureRule_ID"));
278 employeeCalendar.setInStatus(rs.getString("IncomingStatus"));
279 employeeCalendar.setOutStatus(rs.getString("OutgoingStatus"));
280 if (employeeCalendar.getDayType() == null || !employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_Work))
281 employeeCalendar.setPlannedHours(Env.ZERO);
282 else
283 employeeCalendar.setPlannedHours(rs.getBigDecimal("NoOfHours"));
284 employeeCalendar.setActualHours(rs.getBigDecimal("NumberOfHours"));
285 employeeCalendar.setPermissionHours(rs.getBigDecimal("HoursPermission"));
286 employeeCalendar.setHR_LeaveType_ID(rs.getInt("HR_leaveType_ID"));
287 }
288
289 private void calculateLatePunishDays(MHREmployeeCalendar employeeCalendar) {
290 int mLateRuleID = employeeCalendar.getHR_LateRule_ID();
291 if (employeeCalendar.getLateMinutes().compareTo(Env.ZERO) > 0) {
292 employeeCalendar.setLatePunishDays(getPunishmentDays(employeeCalendar, mLateRuleID, MHRPunishment.PUNISHTYPE_Late));
293 }
294 }
295
296 protected void calculateEarlyDeparturePunishDays(MHREmployeeCalendar employeeCalendar){
297 int earlyDepPunishRule = employeeCalendar.getHR_EarlyDepartureRule_ID();
298 if (employeeCalendar.getEarlyDeparMinutes().compareTo(Env.ZERO) > 0&&earlyDepPunishRule!=0) {
299 employeeCalendar.setLatePunishDays(getPunishmentDays(employeeCalendar, earlyDepPunishRule, MHRPunishment.PUNISHTYPE_EarlyDeparture));
300 }
301 }
302 private void calculateAbsentPunishDays(MHREmployeeCalendar employeeCalendar) {
303 int mAbsentRuleID = employeeCalendar.getHR_AbsentRule_ID();
304 if (employeeCalendar.getDayStatus().equalsIgnoreCase(MHREmployeeCalendar.DAYSTATUS_Absent)) {
305 employeeCalendar.setAbsentPunishDays(getPunishmentDays(employeeCalendar, mAbsentRuleID, MHRPunishment.PUNISHTYPE_Absent));
306 }
307 }
308
309 private void calculateNoDeparturePunishDays(MHREmployeeCalendar employeeCalendar) {
310 int mNoDepartureRuleID = employeeCalendar.getHR_NoDepartureRule_ID();
311 if (employeeCalendar.getDayStatus() != null && employeeCalendar.getDayStatus().equalsIgnoreCase(MHREmployeeCalendar.DAYSTATUS_Attendance)) {
312 employeeCalendar.setNoDeparturePunishDays(getPunishmentDays(employeeCalendar, mNoDepartureRuleID, MHRPunishment.PUNISHTYPE_NoDeparture));
313 }
314 }
315
316 private BigDecimal getPunishmentDays(MHREmployeeCalendar employeeCalendar,
317 int ruleID, String punishmentType) {
318
319 MHRPunishment mPunishment;
320 if (punishmentRules.containsKey(ruleID))
321 mPunishment = (MHRPunishment) punishmentRules.get(ruleID);
322 else {
323 mPunishment = new MHRPunishment(ctx, ruleID, mTrx.getTrxName());
324 punishmentRules.put(ruleID, mPunishment);
325 punishmentRepeats.put(ruleID, new HashMap<Integer, BigDecimal>());
326 }
327
328 int repeats = getRepeatsForEmployee(employeeCalendar, mPunishment, punishmentType);
329 BigDecimal punish;
330
331 Map<Integer, BigDecimal> map = punishmentRepeats.get(mPunishment.getHR_Punishment_ID());
332 if (map.containsKey(repeats) && !punishmentType.equalsIgnoreCase(MHRPunishment.PUNISHTYPE_Late)) {
333 punish = map.get(repeats);
334 } else {
335 StringBuilder sql = new StringBuilder();
336 sql.append("select punish\n");
337 sql.append(" from hr_punishmentrule\n");
338 sql.append(" where hr_punishment_id = ? and repeat = ?\n");
339 if (punishmentType.equalsIgnoreCase(MHRPunishment.PUNISHTYPE_Late)) {
340 sql.append(getFromToLatePunishmentLimit(employeeCalendar));
341 }
342 punish = DB.getSQLValueBD(mTrx.getTrxName(), sql.toString(), ruleID, repeats);
343 map.put(repeats, punish);
344 }
345 return punish == null ? getMaxRepeats(ruleID, repeats, punishmentType, employeeCalendar) : punish;
346 }
347
348 private BigDecimal getMaxRepeats(int punishmentID, int repeats, String punishmentType, MHREmployeeCalendar employeeCalendar) {
349 StringBuilder sql = new StringBuilder();
350 sql.append("select punish\n");
351 sql.append(" from hr_punishmentrule\n");
352 sql.append(" where hr_punishment_id = ? and repeat = (select max(repeat) from hr_punishmentrule where hr_punishment_id = ? and repeat <= ?)\n");
353 if (punishmentType.equalsIgnoreCase(MHRPunishment.PUNISHTYPE_Late)) {
354 sql.append(getFromToLatePunishmentLimit(employeeCalendar));
355 }
356 return DB.getSQLValueBD(mTrx.getTrxName(), sql.toString(), punishmentID, punishmentID, repeats);
357
358 }
359
360 private String getFromToLatePunishmentLimit(MHREmployeeCalendar employeeCalendar) {
361 StringBuilder sql = new StringBuilder();
362 sql.append(" AND to_date(substr('");
363 sql.append(employeeCalendar.getInTime());
364 sql.append("', 1, 10) || ' ' || to_char(fromTime, 'HH24:MI'), 'YYYY-MM-DD HH24:MI') <= to_date(substr('");
365 sql.append(employeeCalendar.getInTime());
366 sql.append("', 1, 19), 'YYYY-MM-DD HH24:MI:SS')");
367
368 sql.append(" AND to_date(substr('");
369 if (employeeCalendar.getOutTime() != null)
370 sql.append(employeeCalendar.getOutTime()); // get day of out time
371 else
372 sql.append(employeeCalendar.getShiftTo());
373 sql.append("', 1, 10) || ' ' || to_char(toTime, 'HH24:MI'), 'YYYY-MM-DD HH24:MI') >= to_date(substr('");
374 sql.append(employeeCalendar.getInTime());
375 sql.append("', 1, 19), 'YYYY-MM-DD HH24:MI:SS')");
376 return sql.toString();
377 }
378
379 private int getRepeatsForEmployee(MHREmployeeCalendar employeeCalendar, MHRPunishment mPunishment,
380 String punishmentType) {
381
382 String resetType = mPunishment.getResetType();
383 if (resetType == null)
384 resetType = "";
385 Timestamp t;
386 switch(resetType) {
387 case MHRPunishment.RESETTYPE_Monthly:
388 t = DateUtil.addMonths(employeeCalendar.getWorkDate(), -1);
389 break;
390 case MHRPunishment.RESETTYPE_QuarterYear:
391 t = DateUtil.addMonths(employeeCalendar.getWorkDate(), -3);
392 break;
393 case MHRPunishment.RESETTYPE_HalfYear:
394 t = DateUtil.addMonths(employeeCalendar.getWorkDate(), -6);
395 break;
396 case MHRPunishment.RESETTYPE_Yearly:
397 t = DateUtil.addMonths(employeeCalendar.getWorkDate(), -12);
398 break;
399 case MHRPunishment.RESETTYPE_WithOut:
400 default:
401 t = employeeCalendar.getWorkDate();
402 break;
403 }
404 StringBuilder sql;
405 int repeats = 0;
406 switch(punishmentType) {
407 case MHRPunishment.PUNISHTYPE_Late:
408 if (employeeCalendar.getHR_Employee_ID() == currentEmployeeID) {
409 repeats = ++currentEmployeeLateDays;
410 } else {
411 sql = new StringBuilder();
412 sql.append("select count(*)\n");
413 sql.append(" from hr_employeecalendar\n");
414 sql.append(" where trunc(workDate) > trunc(?) and HR_Employee_ID = ? and lateMinutes > 0\n");
415 repeats = DB.getSQLValue(mTrx.getTrxName(), sql.toString(), t, employeeCalendar.getHR_Employee_ID()) + 1;
416 resetPunishmentCounters();
417 currentEmployeeLateDays = repeats;
418 setCurrentEmployeeID(employeeCalendar.getHR_Employee_ID());
419 }
420 break;
421 case MHRPunishment.PUNISHTYPE_Absent:
422 if (employeeCalendar.getHR_Employee_ID() == currentEmployeeID) {
423 repeats = ++currentEmployeeAbsenceDays;
424 } else {
425 sql = new StringBuilder();
426 sql.append("select count(*)\n");
427 sql.append(" from hr_employeecalendar\n");
428 sql.append(" where trunc(workDate) > trunc(?) and HR_Employee_ID = ? and dayStatus = ?\n");
429 repeats = DB.getSQLValue(mTrx.getTrxName(), sql.toString(), t, employeeCalendar.getHR_Employee_ID(), "S") + 1;
430 resetPunishmentCounters();
431 currentEmployeeAbsenceDays = repeats;
432 setCurrentEmployeeID(employeeCalendar.getHR_Employee_ID());
433 }
434 break;
435 case MHRPunishment.PUNISHTYPE_NoDeparture:
436 if (employeeCalendar.getHR_Employee_ID() == currentEmployeeID) {
437 repeats = ++currentEmployeeNoDepartureDays;
438 } else {
439 sql = new StringBuilder();
440 sql.append("select count(*)\n");
441 sql.append(" from hr_employeecalendar\n");
442 sql.append(" where trunc(workDate) > trunc(?) and HR_Employee_ID = ? and outStatus = ?\n");
443 repeats = DB.getSQLValue(mTrx.getTrxName(), sql.toString(), t, employeeCalendar.getHR_Employee_ID(), null) + 1;
444 resetPunishmentCounters();
445 currentEmployeeNoDepartureDays = repeats;
446 setCurrentEmployeeID(employeeCalendar.getHR_Employee_ID());
447 }
448 break;
449 }
450 return repeats;
451 }
452
453 private void calculateLateTime(MHREmployeeCalendar employeeCalendar,
454 Timestamp shiftFrom, Timestamp inTime, Timestamp late, Timestamp lateLimit,
455 Timestamp permissionStartTime, Timestamp permissionEndTime) {
456
457 if (inTime == null) {
458 employeeCalendar.setLateMinutes(Env.ZERO);
459 // set day status to absent
460 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Absent);
461 return;
462 }
463 // if it's not a work day, no late
464 if (!employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_Work)) {
465 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
466 employeeCalendar.setLateMinutes(Env.ZERO);
467 return;
468 }
469 int lateMinutes = 0;
470 try {
471 lateMinutes = calculateMinutesDifference(inTime, shiftFrom).intValue();
472 if (lateMinutes <= 0) { // no late
473 lateMinutes = 0;
474 //set day status to attendance
475 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
476 } else {
477 int mLate = calculateMinutesDifference(late, shiftFrom).intValue();
478 int mLateLimit = calculateMinutesDifference(lateLimit, shiftFrom).intValue();
479 if (lateMinutes <= mLate) {
480 lateMinutes = 0;
481 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
482 }
483 else if (lateMinutes > mLateLimit) {
484 if (permissionStartTime != null && (calculateTimeDifference(permissionStartTime, shiftFrom).compareTo(Env.ZERO)) == 0) {
485 if ((calculateTimeDifference(inTime, permissionEndTime).compareTo(Env.ZERO)) <= 0) {
486 // set employee day status to attendance
487 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
488 } else {
489 // set employee status to absent
490 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Absent);
491 lateMinutes = 0;
492 }
493 } else {
494 // set employee status to absent
495 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Absent);
496 lateMinutes = 0;
497 }
498 } else { // employee came between late ignore and max late limit
499 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
500 }
501 }
502 } catch (Exception e) {
503 }
504 employeeCalendar.setLateMinutes(new BigDecimal(lateMinutes));
505 }
506
507 protected void calculateEarlyDepartureMinutes(MHREmployeeCalendar employeeCalendar, Timestamp shiftTo,
508 Timestamp outTime, Timestamp permissionStartTime, Timestamp permissionEndTime) {
509
510 if (outTime == null || !employeeCalendar.getDayType().equalsIgnoreCase(MHREmployeeCalendar.DAYTYPE_Work)) {
511 employeeCalendar.setEarlyDeparMinutes(Env.ZERO);
512 return;
513 }
514
515 BigDecimal earlyDepartureMinutes = Env.ZERO;
516 try {
517 earlyDepartureMinutes = calculateMinutesDifference(shiftTo, outTime);
518 if (earlyDepartureMinutes.compareTo(Env.ZERO) > 0) {
519 if (permissionStartTime != null && outTime.compareTo(permissionStartTime) >= 0
520 && outTime.compareTo(permissionEndTime) < 0) {
521 earlyDepartureMinutes = Env.ZERO;
522 }else {
523 employeeCalendar.setOutStatus(MHREmployeeCalendar.OUTSTATUS_EarlyLeave);
524 }
525 } else {
526 earlyDepartureMinutes = Env.ZERO;
527 }
528 } catch (Exception e) {
529 e.printStackTrace();
530 };
531 employeeCalendar.setEarlyDeparMinutes(earlyDepartureMinutes);
532
533 }
534
535 private String getAllRecordsFromHREmpCalBaseVSqlString() {
536
537 boolean onlyCO = MSysConfig.getBooleanValue("EMP_CAL_ONLY_CO_LEAVE_REQUEST", false);
538 String cond = onlyCO ? "AND lr.DOCSTATUS = 'CO'" : "";
539 //TODO Multiple Permission per day will destroy the query
540 StringBuilder sql = new StringBuilder();
541 sql.append("SELECT ebc.HR_employee_ID, ebc.C_BPartner_ID, ebc.workDate, ebc.dayType, ebc.HR_work_group_ID, ebc.HR_work_shift_ID, ebc.HR_overtimeRule_ID, ebc.HR_work_groupShift_ID,\n");
542 sql.append(" ebc.HR_employeeShift_ID, ebc.HR_lateRule_ID, ebc.HR_noDepartureRule_ID, ebc.HR_absentRule_ID,ebc.HR_EarlyDepartureRule_ID, ebc.shiftFromTime, ebc.shiftToTime, ebc.AttendMinLimit, ebc.AttendMaxLimit, ebc.Late, ebc.LateLimit,\n");
543 sql.append(" ebc.isOvertimeBeforeApplicable, ebc.overtimeBeforeLimit, ebc.overtimeAfterStart, ebc.overtimeAfterMax, ebc.roundRule, ebc.roundBaseHours, ebc.nightOTStart, ebc.nightOTEnd, \n");
544 sql.append(" ega.IncomingTime, ega.OutgoingTime, ega.IncomingStatus, ega.OutgoingStatus, ega.NumberOfHours, ebc.NoOfHours, p.HoursPermission, p.startTime, p.endTime, p2.startTime Dep_startTime, p2.endTime Dep_endTime, lr.HR_leaveType_ID\n");
545 sql.append(" FROM hr_employeebasecalendar_v ebc left join HR_EMP_GATE_ATTENDENCE ega\n");
546 sql.append(" on (ebc.c_bpartner_id = ega.c_bpartner_id and ebc.workdate = ega.workdate and ebc.hr_work_shift_id = ega.actual_work_shift_id)\n");
547/**/ sql.append(" left join HR_Permission p on (ebc.c_bpartner_id = p.c_bpartner_id and ebc.WorkDate = p.DatePermission and ebc.HR_Work_Shift_ID = p.HR_Work_Shift_ID and ebc.shiftFromTime = p.startTime and p.docStatus = 'CO')\n");
548/**/ sql.append(" left join HR_Permission p2 on (ebc.c_bpartner_id = p2.c_bpartner_id and ebc.WorkDate = p2.DatePermission and ebc.HR_Work_Shift_ID = p2.HR_Work_Shift_ID and ebc.shiftToTime = p2.endTime and p2.docStatus = 'CO')\n");
549 sql.append(" left join hr_leave_request lr on (ebc.c_bpartner_id = lr.c_bpartner_id and ebc.workDate >= lr.fromDate and ebc.workDate <= lr.toDate "+cond+")\n");
550 appendFormParameters(sql);
551 sql.append(" ORDER BY ebc.HR_Employee_id, ebc.workDate, ebc.shiftFromTime\n");
552
553 return sql.toString();
554 }
555
556 private void appendFormParameters(StringBuilder sql) {
557
558 sql.append(" WHERE ebc.workDate >= trunc(?) AND ebc.workDate <= trunc(?)\n");
559 sql.append("");
560 if (HR_Employee_ID != 0) {
561 sql.append(" AND ebc.HR_Employee_ID = ");
562 sql.append(HR_Employee_ID);
563 sql.append("\n");
564 } else
565 {
566 sql.append(" AND ebc.HR_Employee_ID IN (SELECT e.HR_Employee_ID FROM HR_Employee e WHERE e.IsActive='Y' AND AD_Client_ID =? ");
567 sql.append(" AND e.StartDate <=? AND ( RELIEVEEMPLOYEE='N' OR ( RELIEVEEMPLOYEE='Y' and RELIEVINGDATE >= ? ) ) ) ");
568 }
569 if (HR_Department_ID != 0) {
570 sql.append(" AND ebc.HR_Employee_ID IN (SELECT e.HR_Employee_ID FROM HR_Employee e WHERE e.HR_Department_ID = ");
571 sql.append(HR_Department_ID);
572 sql.append(")\n");
573 }
574 if (HR_Job_ID != 0) {
575 sql.append(" AND ebc.HR_Employee_ID IN (SELECT e.HR_Employee_ID FROM HR_Employee e WHERE e.HR_Job_ID = ");
576 sql.append(HR_Job_ID);
577 sql.append(")\n");
578 }
579
580 if(HR_Work_Group_ID !=0)
581 {
582 sql.append(" AND ebc.HR_Work_Group_ID = ");
583 sql.append(HR_Work_Group_ID);
584 sql.append("\n");
585 }
586
587
588 }
589
590 private void calculateOvertime(MHREmployeeCalendar employeeCalendar,
591 Timestamp shiftFrom, Timestamp shiftTo, Timestamp inTime,
592 Timestamp outTime, boolean isOvertimeBeforeApplicable,
593 Timestamp overtimeBeforeLimit, Timestamp overtimeAfterStart,
594 Timestamp overtimeAfterMax, Timestamp nightOTStart,
595 Timestamp nightOTEnd, String roundRule, BigDecimal roundBaseHours) {
596
597 boolean isNightShift = DateUtil.isNightShift(shiftFrom, shiftTo);
598
599 BigDecimal OTBD, OTBN, OTAD, OTAN, OTWD, OTWN, OTHD, OTHN, OTT;
600 OTBD = OTBN = OTAD = OTAN = OTWD = OTWN = OTHD = OTHN = OTT = Env.ZERO;
601
602 try {
603 switch(employeeCalendar.getDayType()) {
604 case MHREmployeeCalendar.DAYTYPE_NotApplicable:
605 break;
606 case MHREmployeeCalendar.DAYTYPE_Weekend:
607 OTWD = calculateOTWD(outTime, inTime, nightOTStart, nightOTEnd, isNightShift);
608 OTWN = calculateOTWN(outTime, inTime, nightOTStart, nightOTEnd, isNightShift);
609 OTT = OTWD.add(OTWN);
610 break;
611 case MHREmployeeCalendar.DAYTYPE_Official:
612 OTHD = calculateOTWD(outTime, inTime, nightOTStart, nightOTEnd, isNightShift);
613 OTHN = calculateOTWN(outTime, inTime, nightOTStart, nightOTEnd, isNightShift);
614 OTT = OTHD.add(OTHN);
615 break;
616 case MHREmployeeCalendar.DAYTYPE_Work:
617 default:
618 if (!isNightShift) {
619 OTBD = isOvertimeBeforeApplicable ? calculateOTBD(shiftFrom, nightOTEnd, inTime, isNightShift) : Env.ZERO;
620 OTBN = isOvertimeBeforeApplicable ? calculateOTBN(shiftFrom, nightOTEnd, inTime, isNightShift) :Env.ZERO;
621 OTAD = overtimeAfterStart.after(nightOTStart) ? Env.ZERO : calculateOTAD(nightOTStart, outTime, shiftTo, isNightShift);
622 OTAN = calculateOTAN(nightOTStart, outTime, shiftTo, isNightShift);
623 } else {
624 OTBD = isOvertimeBeforeApplicable ? calculateOTBD(shiftFrom, nightOTStart, inTime, isNightShift) : Env.ZERO;
625 OTBN = isOvertimeBeforeApplicable ? calculateOTBN(shiftFrom, nightOTStart, inTime, isNightShift) : Env.ZERO;
626 OTAD = calculateOTAD(nightOTEnd, outTime, shiftTo, isNightShift);
627 OTAN = overtimeAfterStart.after(nightOTEnd) ? Env.ZERO : calculateOTAN(nightOTEnd, outTime, shiftTo, isNightShift);
628 }
629
630 BigDecimal OTBeforeLim = calculateTimeDifference(shiftFrom, overtimeBeforeLimit);
631 BigDecimal OTAfterStartD, OTAfterStartN;
632 if (isNightShift) {
633 OTAfterStartD = calculateTimeDifference(overtimeAfterStart, nightOTEnd);
634 OTAfterStartN = calculateTimeDifference(overtimeAfterStart, shiftTo);
635 } else {
636 OTAfterStartD = calculateTimeDifference(overtimeAfterStart, shiftTo);
637 OTAfterStartN = calculateTimeDifference(overtimeAfterStart, nightOTStart);
638 }
639
640 BigDecimal OTAfterMax = calculateTimeDifference(overtimeAfterMax, shiftTo);
641 OTBD = roundLimitOTB(OTBD, OTBeforeLim, roundRule, roundBaseHours);
642 OTBN = roundLimitOTB(OTBN, OTBeforeLim, roundRule, roundBaseHours);
643 OTAD = roundLimitOTA(OTAD, OTAfterStartD, OTAfterMax, roundRule, roundBaseHours);
644 OTAN = roundLimitOTA(OTAN, OTAfterStartN, OTAfterMax, roundRule, roundBaseHours);
645
646 OTT = OTBD.add(OTBN).add(OTAD).add(OTAN);
647
648 break;
649 }
650 } catch (Exception e) {
651 }
652
653 employeeCalendar.setOTBD(OTBD);
654 employeeCalendar.setOTBN(OTBN);
655 employeeCalendar.setOTAD(OTAD);
656 employeeCalendar.setOTAN(OTAN);
657 employeeCalendar.setOTWD(OTWD);
658 employeeCalendar.setOTWN(OTWN);
659 employeeCalendar.setOTHD(OTHD);
660 employeeCalendar.setOTHN(OTHN);
661 employeeCalendar.setOTT(OTT);
662
663 }
664
665 // calculate time difference between to - from, in hours
666 private BigDecimal calculateTimeDifference(Timestamp to, Timestamp from) {
667 BigDecimal hours = (new BigDecimal(to.getTime() - from.getTime())).divide(DateUtil.ONE_HOUR_IN_MILLISECONDS, 2, BigDecimal.ROUND_HALF_UP);
668 return hours.compareTo(Env.ZERO) >= 0 ? hours : Env.ZERO;
669 }
670
671 // above method is not accurate for minutes
672 private BigDecimal calculateMinutesDifference(Timestamp to, Timestamp from) {
673 BigDecimal minutes = (new BigDecimal(to.getTime() - from.getTime())).divide(DateUtil.ONE_MINUTE_IN_MILLISECONDS, 2, BigDecimal.ROUND_HALF_UP);
674 return minutes.compareTo(Env.ZERO) >= 0 ? minutes : Env.ZERO;
675 }
676
677 private BigDecimal calculateOTBD(Timestamp shiftFrom, Timestamp overtimeNightMark, Timestamp inTime, boolean nightShift) {
678 try {
679 if (!nightShift) {
680 if (inTime.before(overtimeNightMark))
681 return calculateTimeDifference(shiftFrom, overtimeNightMark);
682 else
683 return calculateTimeDifference(shiftFrom, inTime);
684 } else {
685 if (inTime.before(overtimeNightMark))
686 return calculateTimeDifference(overtimeNightMark, inTime);
687 else
688 return Env.ZERO;
689 }
690 } catch (Exception e) {
691 return Env.ZERO;
692 }
693 }
694
695 private BigDecimal calculateOTBN(Timestamp shiftFrom, Timestamp overtimeNightMark, Timestamp inTime, boolean nightShift) {
696 try {
697 if (!nightShift) {
698 if (inTime.before(overtimeNightMark))
699 return calculateTimeDifference(overtimeNightMark, inTime);
700 else
701 return Env.ZERO;
702 } else {
703 if (inTime.before(overtimeNightMark))
704 return calculateTimeDifference(shiftFrom, overtimeNightMark);
705 else
706 return calculateTimeDifference(shiftFrom, inTime);
707 }
708 } catch (Exception e) {
709 return Env.ZERO;
710 }
711 }
712
713 private BigDecimal calculateOTAD(Timestamp overtimeNightMark,
714 Timestamp outTime, Timestamp shiftTo, boolean nightShift) {
715 try {
716 if (!nightShift) {
717 if (outTime.after(overtimeNightMark))
718 return calculateTimeDifference(overtimeNightMark, shiftTo);
719 else
720 return calculateTimeDifference(outTime, shiftTo);
721 } else {
722 if (outTime.after(overtimeNightMark))
723 return calculateTimeDifference(outTime, overtimeNightMark);
724 else
725 return Env.ZERO;
726 }
727 } catch (Exception e) {
728 return Env.ZERO;
729 }
730 }
731
732 private BigDecimal calculateOTAN(Timestamp overtimeNightMark, Timestamp outTime, Timestamp shiftTo, boolean nightShift) {
733 try {
734 if (!nightShift) {
735 if (outTime.after(overtimeNightMark))
736 return calculateTimeDifference(outTime, overtimeNightMark);
737 else
738 return Env.ZERO;
739 } else {
740 if (outTime.after(overtimeNightMark))
741 return calculateTimeDifference(overtimeNightMark, shiftTo);
742 else
743 return calculateTimeDifference(outTime, shiftTo);
744 }
745 } catch (Exception e) {
746 return Env.ZERO;
747 }
748 }
749
750 private BigDecimal calculateOTWD(Timestamp outTime, Timestamp inTime, Timestamp nightStart, Timestamp nightEnd, boolean isNightShift) {
751 try {
752 if (!isNightShift) { // shift doesn't span night
753 if (inTime.getTime() >= nightEnd.getTime() && outTime.getTime() <= nightStart.getTime())
754 return calculateTimeDifference(outTime, inTime);
755 else if (inTime.getTime() >= nightEnd.getTime() && outTime.getTime() >= nightStart.getTime())
756 return calculateTimeDifference(nightStart, inTime);
757 else if (inTime.getTime() <= nightEnd.getTime() && outTime.getTime() <= nightStart.getTime())
758 return calculateTimeDifference(outTime, nightEnd);
759 else
760 return calculateTimeDifference(nightStart, nightEnd);
761 } else {
762 if (inTime.getTime() >= nightStart.getTime() && outTime.getTime() <= nightEnd.getTime())
763 return Env.ZERO;
764 else if (inTime.getTime() <= nightStart.getTime() && outTime.getTime() <= nightEnd.getTime())
765 return calculateTimeDifference(nightStart, inTime);
766 else if (inTime.getTime() >= nightStart.getTime() && outTime.getTime() >= nightEnd.getTime())
767 return calculateTimeDifference(outTime, nightEnd);
768 else
769 return calculateTimeDifference(outTime, nightEnd).add(calculateTimeDifference(nightStart, inTime));
770 }
771 } catch (Exception e) {
772 return Env.ZERO;
773 }
774 }
775
776 private BigDecimal calculateOTWN(Timestamp outTime, Timestamp inTime, Timestamp nightStart, Timestamp nightEnd, boolean isNightShift) {
777 try {
778 if (!isNightShift) { // shift doesn't span night
779 if (inTime.getTime() >= nightEnd.getTime() && outTime.getTime() <= nightStart.getTime())
780 return Env.ZERO;
781 else if (inTime.getTime() >= nightEnd.getTime() && outTime.getTime() >= nightStart.getTime())
782 return calculateTimeDifference(outTime, nightStart);
783 else if (inTime.getTime() <= nightEnd.getTime() && outTime.getTime() <= nightStart.getTime())
784 return calculateTimeDifference(nightEnd, inTime);
785 else
786 return calculateTimeDifference(outTime, nightStart).add(calculateTimeDifference(nightEnd, inTime));
787 } else {
788 if (inTime.getTime() >= nightStart.getTime() && outTime.getTime() <= nightEnd.getTime())
789 return calculateTimeDifference(outTime, inTime);
790 else if (inTime.getTime() <= nightStart.getTime() && outTime.getTime() <= nightEnd.getTime())
791 return calculateTimeDifference(outTime, nightStart);
792 else if (inTime.getTime() >= nightStart.getTime() && outTime.getTime() >= nightEnd.getTime())
793 return calculateTimeDifference(nightEnd, inTime);
794 else
795 return calculateTimeDifference(nightEnd, nightStart);
796 }
797 } catch (Exception e) {
798 return Env.ZERO;
799 }
800 }
801
802 private BigDecimal roundLimitOTB(BigDecimal OTB, BigDecimal overtimeBeforeLimit,
803 String roundRule, BigDecimal roundBaseHours) {
804 if (OTB.compareTo(Env.ZERO) == 0) return Env.ZERO;
805 OTB = OTB.compareTo(overtimeBeforeLimit) <= 0 ? OTB : overtimeBeforeLimit;
806
807 return roundOT(OTB, roundRule, roundBaseHours);
808 }
809
810 private BigDecimal roundLimitOTA(BigDecimal OTA, BigDecimal OTAfterStart,
811 BigDecimal OTAfterMax, String roundRule, BigDecimal roundBaseHours) {
812 if (OTA.compareTo(Env.ZERO) == 0) return Env.ZERO;
813 if (OTA.compareTo(OTAfterStart) < 0) return Env.ZERO;
814 OTA = OTA.compareTo(OTAfterMax) <= 0 ? OTA.subtract(OTAfterStart) : OTAfterMax;
815
816 return roundOT(OTA, roundRule, roundBaseHours);
817 }
818
819 private BigDecimal roundOT(BigDecimal OT, String roundRule,
820 BigDecimal roundBaseHours) {
821 switch(roundRule) {
822 case MHROvertimeRule.ROUNDRULE_Round:
823 int segments = (OT.divide(roundBaseHours, 2, BigDecimal.ROUND_HALF_UP)).intValue();
824 return roundBaseHours.multiply(new BigDecimal(segments));
825 case MHROvertimeRule.ROUNDRULE_Floor:
826 return new BigDecimal(OT.intValue());
827 case MHROvertimeRule.ROUNDRULE_Ceil:
828 return new BigDecimal(OT.intValue() + 1);
829 }
830 return OT;
831 }
832
833 private void resetPunishmentCounters() {
834 currentEmployeeLateDays = 0;
835 currentEmployeeAbsenceDays = 0;
836 currentEmployeeNoDepartureDays = 0;
837 }
838
839 // return employeeCalendarID if record already exists, else 0 to create new record
840 private int getEmployeeCalendarID(int C_BPartner_ID, Timestamp workDate, int HR_Work_Shift_ID) {
841
842 String sql = "SELECT HR_EmployeeCalendar_ID" +
843 " FROM HR_EmployeeCalendar" +
844 " WHERE C_BPartner_ID = ? AND trunc(WorkDate) = trunc(?) AND HR_Work_Shift_ID = ? ";
845 int HR_EmployeeCalendar_ID = DB.getSQLValue(mTrx.getTrxName(), sql, C_BPartner_ID, workDate, HR_Work_Shift_ID);
846 return HR_EmployeeCalendar_ID > 0 ? HR_EmployeeCalendar_ID : 0;
847 }
848
849 // return true if and only if the current employee if of R type
850 private boolean isCurrentEmployeeOfTypeR(int HR_Employee_ID) {
851 if (currentEmployeeID == HR_Employee_ID)
852 return isRTypeEmployee;
853
854 setCurrentEmployeeID(HR_Employee_ID);
855 return isRTypeEmployee;
856 }
857
858 private void setCurrentEmployeeID(int HR_Employee_ID) {
859 currentEmployeeID = HR_Employee_ID;
860 String attendanceType = getAttendanceType(HR_Employee_ID);
861 isRTypeEmployee = attendanceType.equalsIgnoreCase(MHREmployee.ATTENDANCETYPE_Reverse);
862 }
863
864 private String getAttendanceType(int HR_Employee_ID) {
865 String sql = "Select AttendanceType" +
866 " From HR_Employee" +
867 " Where HR_Employee_ID = ?";
868 return DB.getSQLValueString(mTrx.getTrxName(), sql, HR_Employee_ID);
869 }
870
871 // set all attributes to default values. better do it and then modify it if needed than overchecking which was no set
872 private void setDefaulstForRTypeEmployee(MHREmployeeCalendar employeeCalendar) {
873 employeeCalendar.setDayStatus(MHREmployeeCalendar.DAYSTATUS_Attendance);
874 employeeCalendar.setInTime(employeeCalendar.getShiftFrom());
875 employeeCalendar.setInStatus(MHREmployeeCalendar.INSTATUS_Normal);
876 employeeCalendar.setOutTime(employeeCalendar.getShiftTo());
877 employeeCalendar.setOutStatus(MHREmployeeCalendar.OUTSTATUS_Normal);
878 employeeCalendar.setPermissionHours(Env.ZERO);
879 employeeCalendar.setLateMinutes(Env.ZERO);
880 employeeCalendar.setLatePunishDays(Env.ZERO);
881 employeeCalendar.setAbsentPunishDays(Env.ZERO);
882 employeeCalendar.setNoDeparturePunishDays(Env.ZERO);
883 employeeCalendar.setEarlyDeparDays(Env.ZERO);
884 employeeCalendar.setEarlyDeparMinutes(Env.ZERO);
885 }
886
887 // checks if an R-Type employee worked overtime and sets inTime, inStatus, outTime, outStatus accordingly
888 private boolean isRTypeEmployeeOT(MHREmployeeCalendar employeeCalendar) {
889 String sql = "Select overtimeBefore, overtimeAfter" +
890 " From HR_Overtime" +
891 " Where C_BPartner_ID = ? AND WorkDate = ? and HR_Work_Shift_ID = ?";
892
893 List<Object> list = DB.getSQLValueObjectsEx(mTrx.getTrxName(), sql, employeeCalendar.getC_BPartner_ID(), employeeCalendar.getWorkDate(), employeeCalendar.getHR_Work_Shift_ID());
894
895 if (list == null || list.size() == 0) { // no overtime
896 return false;
897 } else {
898 BigDecimal OTB = Env.ZERO;
899 BigDecimal OTA = Env.ZERO;
900 if (list.get(0) != null) {
901 OTB = (BigDecimal) list.get(0);
902 }
903 if (list.get(1) != null) {
904 OTA = (BigDecimal) list.get(1);
905 }
906
907 if (OTA.add(OTB).compareTo(Env.ZERO) == 0) { // no overtime
908 return false;
909 }
910
911 // if day is weekend inTime = shiftFrom, outTime = shiftFrom + additonalHours
912 // if normal day inTime = shiftFrom, outTime = shiftTo + additionalHours
913 // overtime Total = Overtime Beofre + overtime After
914
915 BigDecimal additionalHours = OTA.add(OTB);
916
917 employeeCalendar.setInTime(employeeCalendar.getShiftFrom());
918 if (employeeCalendar.getDayType().equals(MHREmployeeCalendar.DAYTYPE_Official) || employeeCalendar.getDayType().equals(MHREmployeeCalendar.DAYTYPE_Weekend)) {
919 employeeCalendar.setInStatus(MHREmployeeCalendar.INSTATUS_Overtime);
920 employeeCalendar.setOutStatus(MHREmployeeCalendar.OUTSTATUS_Overtime);
921 employeeCalendar.setOutTime(DateUtil.addMillis(employeeCalendar.getShiftFrom(), additionalHours.multiply(DateUtil.ONE_HOUR_IN_MILLISECONDS).longValue()));
922 } else {
923 employeeCalendar.setOutStatus(MHREmployeeCalendar.OUTSTATUS_Overtime);
924 employeeCalendar.setOutTime(DateUtil.addMillis(employeeCalendar.getShiftTo(), additionalHours.multiply(DateUtil.ONE_HOUR_IN_MILLISECONDS).longValue()));
925 }
926
927 employeeCalendar.setOTAD(additionalHours);
928 employeeCalendar.setOTT(additionalHours);
929
930 return true;
931 }
932 }
933
934 private boolean isRTypeEmployeeLate(MHREmployeeCalendar employeeCalendar) {
935 String sql = "Select IncomingTime, lateTime" +
936 " From HR_Late" +
937 " Where C_BPartner_ID = ? AND WorkDate = ? and HR_Work_Shift_ID = ?";
938
939 List<Object> list = DB.getSQLValueObjectsEx(mTrx.getTrxName(), sql, employeeCalendar.getC_BPartner_ID(), employeeCalendar.getWorkDate(), employeeCalendar.getHR_Work_Shift_ID());
940 if (list == null || list.size() == 0) {
941 employeeCalendar.setInStatus(MHREmployeeCalendar.INSTATUS_Normal);
942 employeeCalendar.setInTime(employeeCalendar.getShiftFrom());
943 employeeCalendar.setLateMinutes(Env.ZERO);
944 return false;
945 } else {
946 employeeCalendar.setInStatus(MHREmployeeCalendar.INSTATUS_Late);
947 employeeCalendar.setInTime(DateUtil.combineDateTime(employeeCalendar.getWorkDate(), (Timestamp) list.get(0)));
948 if (list.get(1) instanceof BigDecimal)
949 employeeCalendar.setLateMinutes((BigDecimal) list.get(1));
950 else if (list.get(1) instanceof Timestamp)
951 employeeCalendar.setLateMinutes(new BigDecimal(DateUtil.getTimeInMinutes((Timestamp) list.get(1))));
952 return true;
953 }
954
955 }
956
957 protected boolean isRTypeEmployeeEarlyDeparture(MHREmployeeCalendar employeeCalendar){
958 //TODO remove CA check , after finishing Deploy table @All DB
959 if(!DSClientModel.isCA())
960 return false;
961 String sql = "Select OUTGOINGTIME" +
962 " From HR_Early_Departure" +
963 " Where C_BPartner_ID = ? AND WorkDate = ? and HR_Work_Shift_ID = ?";
964
965 List<Object> list = DB.getSQLValueObjectsEx(mTrx.getTrxName(), sql, employeeCalendar.getC_BPartner_ID(), employeeCalendar.getWorkDate(), employeeCalendar.getHR_Work_Shift_ID());
966
967 if(list!=null&&list.size()>0){
968 employeeCalendar.setOutTime(DateUtil.combineDateTime(employeeCalendar.getWorkDate(), (Timestamp) list.get(0)));
969 return true;
970 }
971
972 return false;
973 }
974
975 private boolean isRTypeEmployeeAbsent(MHREmployeeCalendar employeeCalendar) {
976 String sql = "Select C_BPartner_ID" +
977 " From HR_Absent" +
978 " Where C_BPartner_ID = ? AND WorkDate = ? and HR_Work_Shift_ID = ?";
979 BigDecimal bpID = DB.getSQLValueBD(mTrx.getTrxName(), sql, employeeCalendar.getC_BPartner_ID(), employeeCalendar.getWorkDate(), employeeCalendar.getHR_Work_Shift_ID());
980 if (bpID != null) {
981 employeeCalendar.setInTime(null);
982 employeeCalendar.setInStatus(null);
983 employeeCalendar.setOutTime(null);
984 employeeCalendar.setOutStatus(null);
985 return true;
986 }
987 return false;
988 }
989
990 private void setActualHoursForRTypeEmployee(MHREmployeeCalendar employeeCalendar) {
991 if (employeeCalendar.getInTime() != null && employeeCalendar.getOutTime() != null)
992 employeeCalendar.setActualHours(DateUtil.getTimeDifferenceInHours(employeeCalendar.getOutTime(), employeeCalendar.getInTime()));
993
994 }
995
996 private void checkPermissionForRTypeEmployee(MHREmployeeCalendar employeeCalendar) {
997 String sql = "SELECT p.hoursPermission"
998 + " FROM HR_Permission p"
999 + " WHERE p.c_bpartner_id = ? and p.DatePermission = ? and p.HR_Work_Shift_ID = ? and p.startTime = ? and p.docStatus = 'CO'";
1000 double startDayPermHrs = DB.getSQLValue(mTrx.getTrxName(), sql, employeeCalendar.getC_BPartner_ID(), employeeCalendar.getWorkDate(), employeeCalendar.getHR_Work_Shift_ID(), employeeCalendar.getInTime());
1001 if (Double.compare(startDayPermHrs, 0.0) > 0) {
1002 employeeCalendar.setInStatus(MHREmployeeCalendar.INSTATUS_Permission);
1003 employeeCalendar.setInTime(DateUtil.addHours(employeeCalendar.getInTime(), startDayPermHrs));
1004 }
1005
1006 // outgoing permission, early leave
1007 sql = "SELECT hoursPermission"
1008 + " FROM HR_Permission p"
1009 + " WHERE p.c_bpartner_id = ? and p.DatePermission = ? and p.HR_Work_Shift_ID = ? and p.endTime = ? and p.docStatus = 'CO'";
1010 double endDayPermHrs = DB.getSQLValue(mTrx.getTrxName(), sql, employeeCalendar.getC_BPartner_ID(), employeeCalendar.getWorkDate(), employeeCalendar.getHR_Work_Shift_ID(), employeeCalendar.getOutTime());
1011 if (Double.compare(endDayPermHrs, 0.0) > 0) {
1012 employeeCalendar.setOutStatus(MHREmployeeCalendar.OUTSTATUS_Permission);
1013 employeeCalendar.setOutTime(DateUtil.addHours(employeeCalendar.getInTime(), -startDayPermHrs));
1014 }
1015 }
1016}