def execute(filters=None):
data, columns, row = [], [], []
if not filters: filters = {}
conditions, filters = get_conditions(filters)
columns = get_columns()
leave_data = get_data(conditions, filters)
for d in sorted(leave_data):
disc_leavs_indays = 0.0
emp_shif_hrs = get_emp_work_shift(d.name, d.day)
if (d.disc_leavs != "NULL"
or d.disc_leavs is not None) and emp_shif_hrs:
disc_leavs_indays = d.disc_leavs / emp_shif_hrs
row = [
d.name, d.employee_name, d.attendance_date, disc_leavs_indays,
d.disc_leavs
]
data.append(row)
return columns, data
def update_leave_balance(self):
if not frappe.db.get_value("Leave Type",_('Annual Leave'), "name") :
frappe.throw(_("{0} not found, you should add it to complete the process").format(_('Annual Leave')) )
if self.discount_salary_from_leave():
#from_date = add_days(cstr(getdate(today())), 1)
from erpnext.hr import get_emp_work_shift
day = calendar.day_name[getdate(self.start_date).weekday()];
shift_diff = get_emp_work_shift(self.employee,day)
#emp_wshift = frappe.db.get_value("Employee Employment Detail", self.employee, "work_shift")
#employee_start_time = frappe.db.get_value("Work Shift Details", {"parent":emp_wshift,"day":day}, "start_work")
#employee_end_time = frappe.db.get_value("Work Shift Details", {"parent":emp_wshift,"day":day}, "end_work")
#shift_diff = time_diff_in_hours(employee_end_time, employee_start_time)
day_count = flt(self.hrs_disc) / flt(shift_diff)
to_date = add_days(cstr(getdate(self.start_date)), day_count)
#to_date= datetime.datetime.strptime(to_date,'%Y-%m-%d')
doc = frappe.db.get_value('Leave Application',{'leave_type':_('Annual Leave'),'employee':self.employee,'status':'Approved','from_date':(">=",self.start_date ),'to_date':("<=",to_date ),'description': _('Auto Entry: Discount form Leaves')})
if not doc and self.hrs_disc >= shift_diff:
leave = frappe.new_doc('Leave Application')
leave.employee= self.employee
leave.employee_name= self.employee_name
leave.leave_type= _('Annual Leave')
leave.from_date = self.start_date
leave.to_date = to_date
leave.total_leave_days = get_number_of_leave_days(self.employee, _('Annual Leave'),self.start_date,to_date)
leave.status = 'Approved'
leave.hours = self.hrs_disc
leave.balance_hrs = self.hrs_disc
leave.leave_balance = get_leave_balance_on(self.employee,_('Annual Leave'), self.start_date,
consider_all_leaves_in_the_allocation_period=True)
leave.description = _('Auto Entry: Discount form Leaves')
leave.docstatus= 1
leave.discount_salary_from_leaves= 1
leave.flags.ignore_validate = True
leave.insert(ignore_permissions=True)
leave.submit()
def get_leave_balance_on(employee, leave_type, date, allocation_records=None,
consider_all_leaves_in_the_allocation_period=False,docname=None,company=None):
if allocation_records == None:
allocation_records = get_leave_allocation_records(date, employee).get(employee, frappe._dict())
com = frappe.defaults.get_user_default("Company")
remain,leave_hrs_balance = 0, 0.0
discout_from_leaves = frappe.db.get_value("Employee", employee, "discount_salary_from_leaves")
date2 = datetime.datetime.strptime(str(date), "%Y-%m-%d")
date_of_joining = frappe.db.get_value("Employee", employee, "date_of_joining")
if date2:
try:
pre_year_date = date2.replace(year=date2.year-1)
except ValueError as e:
pre_year_date = date2.replace(day=date2.day-1)
pre_year_date = pre_year_date.replace(year=date2.year-1)
frappe.msgprint(str(pre_year_date))
#if(date_diff(pre_year_date,date_of_joining) < 0 and date_diff(date_of_joining,date2)<0 ):
# pre_year_date =date_of_joining
allocation_records_last_year = get_leave_allocation_records(pre_year_date, employee).get(employee, frappe._dict())
allocation_prev = allocation_records_last_year.get(leave_type, frappe._dict())
leaves_taken_prev = get_approved_leaves_for_period(employee, leave_type, allocation_prev.from_date, allocation_prev.to_date)
remain = flt(allocation_prev.total_leaves_allocated) - flt(leaves_taken_prev)
if ( flt(allocation_prev.total_leaves_allocated) - flt(leaves_taken_prev) > 0.5*(flt(allocation_prev.total_leaves_allocated))):
remain = 0.5*(flt(allocation_prev.total_leaves_allocated))
else:
remain = flt(allocation_prev.total_leaves_allocated) - flt(leaves_taken_prev)
allocation = allocation_records.get(leave_type, frappe._dict())
from erpnext.hr.doctype.salary_slip.salary_slip import get_working_total_hours
from erpnext.hr import get_emp_work_shift
total_hrs , disc_hours ,total_real_hrs, compensatory_total_hours, permission_total_hours = get_working_total_hours(employee,allocation.from_date, datetime.datetime.strptime(str(date), "%Y-%m-%d").date())
if consider_all_leaves_in_the_allocation_period:
date = allocation.to_date
leaves_taken = get_approved_leaves_for_period(employee, leave_type, allocation.from_date, date)
shift_hrs = get_emp_work_shift(employee,calendar.day_name[getdate(allocation.from_date).weekday()] )
leave_days_balance = (flt(allocation.total_leaves_allocated) - flt(leaves_taken))+remain
if leave_days_balance > 60:
leave_days_balance=60
leaves_dayss=leave_days_balance
leaves_hourss=0
annual_ex_total_hours= 0.0
sick_ex_total_hours= 0.0
disc_hours =disc_hours - permission_total_hours
perm = frappe.db.sql("select employee,docstatus ,permission_date ,ifnull(diff_exit,0) as diff from `tabExit permission` where type='Return' and permission_type='Exit with return' and employee = %(employee)s and date(permission_date) BETWEEN %(start_date)s AND DATE_ADD(%(end_date)s,INTERVAL 1 day) and docstatus = 1 and exit_type = 'Special' ", {'employee': employee, 'start_date': allocation.from_date, 'end_date': date}, as_dict=1)
if perm:
for p in perm:
exit_hrs = frappe.db.sql("select format(((TIME_TO_SEC('%s'))/3600),0)" %p.diff)[0][0]
annual_ex_total_hours += float(exit_hrs)
sickk = frappe.db.sql("select employee,docstatus ,permission_date ,ifnull(diff_exit,0) as diff from `tabExit permission` where type='Return' and permission_type='Exit with return' and employee = %(employee)s and date(permission_date) BETWEEN %(start_date)s AND DATE_ADD(%(end_date)s,INTERVAL 1 day) and docstatus = 1 and exit_type = 'Sick' ", {'employee': employee, 'start_date':allocation.from_date, 'end_date': date}, as_dict=1)
if sickk:
for p in sickk:
exit_hrs = frappe.db.sql("select format(((TIME_TO_SEC('%s'))/3600),0)" %p.diff)[0][0]
sick_ex_total_hours += float(exit_hrs)
if leave_type == _('Annual Leave'):
disc_hours =disc_hours + annual_ex_total_hours
if leave_type == _('Sick Leave'):
sick_ex_total_hours = sick_ex_total_hours
if shift_hrs:
leave_hrs_balance= (leave_days_balance * shift_hrs)
if discout_from_leaves and discout_from_leaves ==1:
if leave_type == _('Annual Leave'):
leave_hrs_balance = (leave_days_balance * shift_hrs) - disc_hours
if leave_type == _('Sick Leave'):
leave_hrs_balance = (leave_days_balance * shift_hrs) - sick_ex_total_hours
leaves_dayss = int(leave_hrs_balance/shift_hrs)
leaves_hourss = leave_hrs_balance % shift_hrs
return leaves_dayss ,flt(leaves_hourss,2)
def get_data(conditions, filters):
data = []
hours = {}
condition = ""
from_date = filters.get('from_date')
to_date = filters.get('to_date')
if filters.get("supervisor"):
condition += " and supervisor = %(supervisor)s"
if filters.get("department"):
condition += " and department = %(department)s"
if filters.get("employee"): condition += " and name = %(employee)s"
employees = frappe.db.sql(
"""select name as employee ,employee_name ,supervisor,department, status from `tabEmployee`
where docstatus <2 and status = "Active" %s order by employee """ %
(condition),
filters,
as_list=1)
for emp_data in employees:
emp_details = get_employee_details(conditions, filters, emp_data[0])
total = 0.0
total_late = 0.0
total_early = 0.0
early_hrs_count = 0
total_abs = 0
onleavs = 0
exit_permision = 0
disc = 0
total_over = 0
total_ex = 0
dont_write_report = 0
write_late = 0
total_att = 0
over_count = 0
late_hrs_count = 0
abs_hrs = 0.0
total_disc = 0.0
#leaves_taken = get_leaves_for_period(emp_data[0], leave_type,from_date, to_date) * -1
#total_over, over_count = get_overtime_hrs(emp_data[0],from_date,to_date)
#exit_permision= get_permissions(emp_data[0],from_date,to_date )
for att in emp_details:
comp_over, over = 0.0, 0.0
if att.total_hours:
total_att = att.total_hours
else:
total_att = 0.0
#if att.total_work_hrs > att.total_hours :
#diff = (float(att.total_work_hrs) - float(att.total_hours))
if att.early_departure:
total_early += att.early_departure
early_hrs_count += 1
if att.late_hrs:
total_late += att.late_hrs
late_hrs_count += 1
if (att.status == "Absent"):
total_abs += 1
emp_wsh = get_emp_work_shift(emp_data[0], att.day)
if emp_wsh: abs_hrs += (total_abs * emp_wsh)
if (att.status == "On Leave"):
onleavs += 1
#if att.total_work_hrs and att.total_hours :
# if att.total_work_hrs > att.total_hours :
# disc += (float(att.total_work_hrs) - float(att.total_hours))
if att.ext_diff > 0:
total_ex += 1
discount_permissions_from_attendance_hours = frappe.db.get_value(
"HR Settings", None,
"discount_permissions_from_attendance_hours")
if att.ext_diff and discount_permissions_from_attendance_hours:
total_att -= att.ext_diff
exit_permision += att.ext_diff
if att.compensatory > 0 or att.overtime_hours > 0 or att.holiday_overtime_hours > 0:
if att.compensatory and att.compensatory != 0.0:
if total_att > att.total_work_hrs:
total_att = total_att - (total_att -
att.total_work_hrs)
comp_over = float(att.compensatory)
total_att += comp_over
if att.overtime_hours:
ovr_rate = frappe.db.get_value("HR Settings", None,
"overtime_hour_price")
over = att.overtime_hours * float(ovr_rate)
total_over += over
if att.holiday_overtime_hours:
overtime_hour_price_in_holidays = frappe.db.get_value(
"HR Settings", None, "overtime_hour_price_in_holidays")
over = att.holiday_overtime_hours * float(
overtime_hour_price_in_holidays)
total_over += over
over_count += 1
if total_att > att.total_work_hrs and not comp_over:
total_att = total_att - (total_att - att.total_work_hrs)
if total_att <= 0:
total_att = 0.0
total += total_att
if emp_data[1]:
data.append([
emp_data[1], emp_data[2], emp_data[3],
round(total, 2), late_hrs_count,
round(total_late, 2), early_hrs_count, total_early, over_count,
round(total_over, 2),
max(0, round(total_disc, 2)), total_ex,
round(exit_permision, 2), onleavs, total_abs
])
return data