def get_data(filters, leave_types):
allocation_records_based_on_to_date = get_leave_allocation_records(
filters.to_date)
active_employees = frappe.get_all(
"Employee",
filters={
"status": "Active",
"company": filters.company
},
fields=["name", "employee_name", "department"])
data = []
for employee in active_employees:
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
# leaves taken
leaves_taken = get_approved_leaves_for_period(
employee.name, leave_type, filters.from_date, filters.to_date)
# closing balance
closing = get_leave_balance_on(
employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(
employee.name, frappe._dict()))
row += [leaves_taken, closing]
data.append(row)
return data
def get_record(self, filters):
allocation_records_based_on_to_date = get_leave_allocation_records(
filters.get('to_date'))
data, active_employees = [], frappe.get_all(
"Employee",
filters={"status": "Active"},
fields=["name", "employee_name", "department"])
for employee in active_employees:
row = [
employee.name, employee.employee_name, employee.department,
self.get_days_spent(employee.name)
]
# if leave type is selected in the filter
# leaves taken
leaves_taken = get_approved_leaves_for_period(
employee.name, filters.get('leave_type'),
filters.get('from_date'), filters.get('to_date'))
# closing balance
closing = get_leave_balance_on(
employee.name, filters.get('leave_type'),
filters.get('to_date'),
allocation_records_based_on_to_date.get(
employee.name, frappe._dict()))
row += [leaves_taken, closing]
data.append(row)
print(data)
def get_data(filters, leave_types):
user = frappe.session.user
allocation_records_based_on_to_date = get_leave_allocation_records(filters.to_date)
allocation_records_based_on_from_date = get_leave_allocation_records(filters.from_date)
active_employees = frappe.get_all("Employee",
filters = { "status": "Active", "company": filters.company},
fields = ["name", "employee_name", "department", "user_id"])
data = []
for employee in active_employees:
leave_approvers = [l.leave_approver for l in frappe.db.sql("""select leave_approver from `tabEmployee Leave Approver` where parent = %s""",
(employee.name),as_dict=True)]
if (len(leave_approvers) and user in leave_approvers) or (user in ["Administrator", employee.user_id]) or ("HR Manager" in frappe.get_roles(user)):
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
# leaves taken
leaves_taken = get_approved_leaves_for_period(employee.name, leave_type,
filters.from_date, filters.to_date)
# opening balance
opening = get_leave_balance_on(employee.name, leave_type, filters.from_date,
allocation_records_based_on_from_date.get(employee.name, frappe._dict()))
# closing balance
closing = get_leave_balance_on(employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(employee.name, frappe._dict()))
row += [opening, leaves_taken, closing]
data.append(row)
return data
def salaries(conditions, filters, leaves):
data = []
hours = {}
ss = frappe.db.sql(
"""select * from `tabEmployee` where docstatus <2 %s order by name """
% conditions,
filters,
as_dict=1)
for add in ss:
for leave_type in leaves:
# leaves taken
leaves_taken = get_approved_leaves_for_period(
add.name, leave_type.name, filters.from_date, filters.to_date)
allocation_records = get_leave_allocation_records(
filters.to_date, add.name).get(add.name, frappe._dict())
allocation = allocation_records.get(leave_type.name,
frappe._dict())
# closing balance
closing = flt(
allocation.total_leaves_allocated) - flt(leaves_taken)
if closing <= 0:
row = [
add.name, add.employee_name, add.department,
add.designation, leave_type.name
]
if allocation.total_leaves_allocated > 0:
data.append(row)
return data
def get_data(filters, leave_types):
user = frappe.session.user
allocation_records_based_on_to_date = get_leave_allocation_records(filters.to_date)
allocation_records_based_on_from_date = get_leave_allocation_records(filters.from_date)
active_employees = frappe.get_all("Employee",
filters = { "status": "Active", "company": filters.company},
fields = ["name", "employee_name", "department", "user_id"])
data = []
for employee in active_employees:
leave_approvers = get_approvers(employee.department)
if (len(leave_approvers) and user in leave_approvers) or (user in ["Administrator", employee.user_id]) or ("HR Manager" in frappe.get_roles(user)):
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
# leaves taken
leaves_taken = get_approved_leaves_for_period(employee.name, leave_type,
filters.from_date, filters.to_date)
# opening balance
opening = get_leave_balance_on(employee.name, leave_type, filters.from_date,
allocation_records_based_on_from_date.get(employee.name, frappe._dict()))
# closing balance
closing = get_leave_balance_on(employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(employee.name, frappe._dict()))
row += [opening, leaves_taken, closing]
data.append(row)
return data
def get_data(filters, leave_types):
allocation_records_based_on_to_date = get_leave_allocation_records(filters.to_date)
active_employees = frappe.get_all("Employee",
filters = { "status": "Active", "company": filters.company},
fields = ["name", "employee_name", "department"])
data = []
for employee in active_employees:
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
# leaves taken
leaves_taken = get_approved_leaves_for_period(employee.name, leave_type,
filters.from_date, filters.to_date)
# closing balance
closing = get_leave_balance_on(employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(employee.name, frappe._dict()))
row += [leaves_taken, closing]
data.append(row)
return data
def validate_against_leave_applications(self):
leaves_taken = get_approved_leaves_for_period(self.employee, self.leave_type,
self.from_date, self.to_date)
if flt(leaves_taken) > flt(self.total_leaves_allocated):
if frappe.db.get_value("Leave Type", self.leave_type, "allow_negative"):
frappe.msgprint(_("Note: Total allocated leaves {0} shouldn't be less than already approved leaves {1} for the period").format(self.total_leaves_allocated, leaves_taken))
else:
frappe.throw(_("Total allocated leaves {0} cannot be less than already approved leaves {1} for the period").format(self.total_leaves_allocated, leaves_taken), LessAllocationError)
def get_data(filters, leave_types):
dates = frappe.db.sql(
'''select
year_start_date, year_end_date
from
`tabFiscal Year`
where name = %s''', filters.fiscal_year)
start_date = dates[0][0]
end_date = dates[0][1]
allocation_records_based_on_to_date_prev_year = get_leave_allocation_records(
frappe.utils.add_days(start_date, -1))
allocation_records_based_on_to_date = get_leave_allocation_records(
end_date)
allocation_records_based_on_from_date = get_leave_allocation_records(
start_date)
active_employees = frappe.get_all(
"Employee",
filters={
"status": "Active",
"company": filters.company
},
fields=["name", "employee_name", "department", "user_id"])
data = []
for employee in active_employees:
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
# leaves taken
leaves_taken = get_approved_leaves_for_period(
employee.name, leave_type, start_date, end_date)
#prev_year_opening
prev_year_end_date = frappe.utils.add_days(start_date, -1)
prev_year_opening = get_leave_balance_on(
employee.name, leave_type, prev_year_end_date,
allocation_records_based_on_to_date_prev_year.get(
employee.name, frappe._dict()))
# opening balance
opening = get_total_allocated_leaves(employee.name, leave_type,
end_date)
added_leave_bal = 0
if (opening - prev_year_opening) > 0:
added_leave_bal = opening - prev_year_opening
# closing balance
closing = get_leave_balance_on(
employee.name, leave_type, end_date,
allocation_records_based_on_to_date.get(
employee.name, frappe._dict()))
row += [prev_year_opening, added_leave_bal, leaves_taken, closing]
data.append(row)
return data
def validate_against_leave_applications(self):
leaves_taken = get_approved_leaves_for_period(self.employee,
self.leave_type,
self.from_date,
self.to_date)
if flt(leaves_taken) > flt(self.total_leaves_allocated):
frappe.throw(
_("Total allocated leaves {0} cannot be less than already approved leaves {1} for the period"
).format(self.total_leaves_allocated, leaves_taken),
LessAllocationError)
def calculate_casual_leave(doc, method):
user = frappe.session.user
start_date = doc.start_date
end_date = doc.end_date
allocation_records_based_on_to_date = get_leave_allocation_records(
end_date)
employee_name = doc.employee_name
active_employees = frappe.get_all(
"Employee", fields=["name", "employee_name", "department", "user_id"])
a = 0.0
b = 0.0
t = 0.0
leaves_taken = 0.0
for employee in active_employees:
leave_approvers = [
l.leave_approver for l in frappe.db.sql(
"""select leave_approver from `tabEmployee Leave Approver` where parent = %s""",
(employee.name),
as_dict=True)
]
if (len(leave_approvers) and user in leave_approvers) or (user in [
"Administrator", employee.user_id
]) or ("HR Manager" in frappe.get_roles(user)):
# leaves taken
leaves_taken = get_approved_leaves_for_period(
employee.name, "Casual Leave", start_date, end_date)
a = a + leaves_taken
conditions = (" and employee_name='%s'" % employee_name)
allocation_records = frappe.db.sql("""
select total_leaves_allocated,from_date,to_date
from `tabLeave Allocation`
where leave_type="Casual Leave" and docstatus=1 {0}""".format(
conditions),
as_list=1)
salary_slip_list = frappe.db.sql("""
select start_date,end_date,pending_leaves,casual_leaves,sick_leaves
from `tabSalary Slip`
where docstatus=1 {0}""".format(conditions),
as_list=1)
if len(salary_slip_list) != 0:
length = len(salary_slip_list)
t = frappe.utils.data.flt(salary_slip_list[length - 1][3], precision=1)
doc.casual_leaves = t - a
if len(salary_slip_list) == 0:
if allocation_records[0][
1] <= start_date and start_date <= allocation_records[0][2]:
doc.casual_leaves = allocation_records[0][0] - a
def get_data(filters, leave_types):
allocation_records_based_on_to_date = get_leave_allocation_records(
filters.to_date)
active_employees = frappe.get_all(
"Employee",
filters={
"status": "Active",
"company": filters.company
},
fields=["name", "employee_name", "department"],
order_by="employee_name asc")
data = []
for employee in active_employees:
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
matched_leave_type = leave_type.lower().replace(' ', '_')
if filters.get(matched_leave_type):
# leaves taken
leaves_taken = get_approved_leaves_for_period(
employee.name, leave_type, filters.from_date,
filters.to_date)
# closing balance
closing = get_leave_balance_on(
employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(
employee.name, frappe._dict()))
# annual leave allocated
allocation_records = allocation_records_based_on_to_date.get(
employee.name, frappe._dict())
allocation = allocation_records.get(leave_type, frappe._dict())
total_leaves_allocated = flt(allocation.total_leaves_allocated)
new_leaves_allocated = flt(allocation.new_leaves_allocated)
row += [
new_leaves_allocated, total_leaves_allocated, leaves_taken,
closing
]
data.append(row)
return data
def validate_against_leave_applications(self):
leaves_taken = get_approved_leaves_for_period(self.employee,
self.leave_type,
self.from_date,
self.to_date)
if flt(leaves_taken) > flt(self.total_leaves_allocated):
if frappe.db.get_value("Leave Type", self.leave_type,
"allow_negative"):
frappe.msgprint(
_("Note: Total allocated leaves {0} shouldn't be less than already approved leaves {1} for the period"
).format(self.total_leaves_allocated, leaves_taken))
else:
frappe.throw(
_("Total allocated leaves {0} cannot be less than already approved leaves {1} for the period"
).format(self.total_leaves_allocated, leaves_taken),
LessAllocationError)
def get_leave_balance_on(employee,
leave_type,
date,
allocation_records=None,
consider_all_leaves_in_the_allocation_period=False):
if allocation_records == None:
allocation_records = get_leave_allocation_records(date, employee).get(
employee, frappe._dict())
allocation = allocation_records.get(leave_type, frappe._dict())
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)
return flt(allocation.total_leaves_allocated) - flt(leaves_taken)
def get_carry_forwarded_leaves(employee, leave_type, date, carry_forward=None):
carry_forwarded_leaves = 0
if carry_forward:
validate_carry_forward(leave_type)
previous_allocation = frappe.db.sql("""
select name, from_date, to_date, total_leaves_allocated
from `tabLeave Allocation`
where employee=%s and leave_type=%s and docstatus=1 and to_date < %s
order by to_date desc limit 1
""", (employee, leave_type, date), as_dict=1)
if previous_allocation:
leaves_taken = get_approved_leaves_for_period(employee, leave_type,
previous_allocation[0].from_date, previous_allocation[0].to_date)
carry_forwarded_leaves = flt(previous_allocation[0].total_leaves_allocated) - flt(leaves_taken)
return carry_forwarded_leaves
def get_carry_forwarded_leaves(employee, leave_type, date, carry_forward=None):
carry_forwarded_leaves = 0
if carry_forward:
validate_carry_forward(leave_type)
previous_allocation = frappe.db.sql("""
select name, from_date, to_date, total_leaves_allocated
from `tabLeave Allocation`
where employee=%s and leave_type=%s and docstatus=1 and to_date < %s
order by to_date desc limit 1
""", (employee, leave_type, date),
as_dict=1)
if previous_allocation:
leaves_taken = get_approved_leaves_for_period(
employee, leave_type, previous_allocation[0].from_date,
previous_allocation[0].to_date)
carry_forwarded_leaves = flt(
previous_allocation[0].total_leaves_allocated) - flt(
leaves_taken)
return carry_forwarded_leaves
def get_leaves_taken(self):
offer_date = frappe.db.get_value('Employee Employment Detail',
self.employee,
'scheduled_confirmation_date')
leaves_taken = get_approved_leaves_for_period(self.employee,
_('Annual Leave'),
offer_date,
self.relieving_date)
leave_balance, leaves_hours = get_leave_balance_on(
self.employee,
_('Annual Leave'),
self.relieving_date,
consider_all_leaves_in_the_allocation_period=True)
#leave_allocations = get_leave_allocation_records(self.relieving_date, self.employee).get(self.employee, frappe._dict())
#from_date = leave_allocations.get(_('Annual Leave'), frappe._dict()).from_date
#leave_allocation_records = frappe.db.sql("""select employee, leave_type, total_leaves_allocated, from_date, to_date,carry_forward
#from `tabLeave Allocation` where employee=%s and leave_type = %s and docstatus=1 order by from_date desc""", (self.employee, _('Annual Leave')), as_dict=1)
#for d in leave_allocation_records:
#leaves_allocated += d.total_leaves_allocated
# leave_balance ,leaves_hours = get_leave_balance_on(self.employee, _('Annual Leave'), d.from_date ,consider_all_leaves_in_the_allocation_period=True)
# leave_balance += leave_balance
return leave_balance
def get_data(filters, leave_types):
allocation_records_based_on_to_date = get_leave_allocation_records(filters.to_date)
active_employees = frappe.get_all("Employee",
filters = { "status": "Active", "company": filters.company},
fields = ["name", "employee_name", "department"],
order_by = "employee_name asc")
data = []
for employee in active_employees:
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
matched_leave_type = leave_type.lower().replace(' ','_')
if filters.get(matched_leave_type):
# leaves taken
leaves_taken = get_approved_leaves_for_period(employee.name, leave_type,
filters.from_date, filters.to_date)
# closing balance
closing = get_leave_balance_on(employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(employee.name, frappe._dict()))
# annual leave allocated
allocation_records = allocation_records_based_on_to_date.get(employee.name, frappe._dict())
allocation = allocation_records.get(leave_type, frappe._dict())
total_leaves_allocated = flt(allocation.total_leaves_allocated)
new_leaves_allocated = flt(allocation.new_leaves_allocated)
row += [new_leaves_allocated, total_leaves_allocated, leaves_taken, closing]
data.append(row)
return data
def validate_against_leave_applications(self):
leaves_taken = get_approved_leaves_for_period(self.employee, self.leave_type,
self.from_date, self.to_date)
if flt(leaves_taken) > flt(self.total_leaves_allocated):
frappe.throw(_("Total allocated leaves {0} cannot be less than already approved leaves {1} for the period").format(self.total_leaves_allocated, leaves_taken), LessAllocationError)
def get_data(filters, leave_types):
user = frappe.session.user
allocation_records_based_on_to_date = get_leave_allocation_records(
filters.to_date)
allocation_records_based_on_from_date = get_leave_allocation_records(
filters.from_date)
hr_manager = frappe.get_doc("User", frappe.session.user).get(
"roles", {"role": "HR Manager"})
if hr_manager:
emp_filters = {
"status": "Active",
"company": filters.company,
"department": filters.department
}
else:
emp_filters = {
"status": "Active",
"company": filters.company,
"department": filters.department,
"user_id": frappe.session.user
}
active_employees = frappe.get_all(
"Employee",
filters=emp_filters,
fields=["name", "employee_name", "department", "user_id"],
order_by="name desc")
data = []
for employee in active_employees:
leave_approvers = [
l.leave_approver for l in frappe.db.sql(
"""select leave_approver from `tabEmployee Leave Approver` where parent = %s""",
(employee.name),
as_dict=True)
]
if (len(leave_approvers) and user in leave_approvers) or (user in [
"Administrator", employee.user_id
]) or ("HR Manager" in frappe.get_roles(user)):
row = [employee.name, employee.employee_name, employee.department]
for leave_type in leave_types:
# leaves taken
leaves_taken = get_approved_leaves_for_period(
employee.name, leave_type, filters.from_date,
filters.to_date)
# opening balance
opening = get_leave_balance_on(
employee.name, leave_type, filters.from_date,
allocation_records_based_on_from_date.get(
employee.name, frappe._dict()))
# closing balance
closing = get_leave_balance_on(
employee.name, leave_type, filters.to_date,
allocation_records_based_on_to_date.get(
employee.name, frappe._dict()))
row += [opening, leaves_taken, closing]
data.append(row)
return data
def get_carry_forwarded_leaves(employee,
leave_type,
date,
carry_forward=None,
till_now=False):
carry_forwarded_leaves = 0
if carry_forward:
validate_carry_forward(leave_type)
previous_allocation = frappe.db.sql("""
select name, from_date, to_date, total_leaves_allocated
from `tabLeave Allocation`
where employee=%s and leave_type=%s and docstatus=1 and to_date < %s
order by to_date desc limit 1
""", (employee, leave_type, date),
as_dict=1)
if previous_allocation:
leaves_taken = get_approved_leaves_for_period(
employee, leave_type, previous_allocation[0].from_date,
previous_allocation[0].to_date)
carry_forwarded_leaves = flt(
previous_allocation[0].total_leaves_allocated) - flt(
leaves_taken)
if till_now:
days_in_current_allocated = 0.0
current_allocation = frappe.db.sql("""
select from_date, to_date, total_leaves_allocated, new_leaves_allocated ,(select sum(encashable_days) from `tabLeave Encashment` where employee= %s and encashment_date BETWEEN L.from_date and L.to_date) last_encashed
from `tabLeave Allocation` L
where employee= %s
and leave_type= %s
and %s BETWEEN from_date and to_date
and docstatus=1
""", (employee, employee, 'Casual Leave', date),
as_dict=1)
# frappe.msgprint(str(current_allocation))
if current_allocation:
current_allocation = current_allocation[0]
leave_period = get_leave_period(
current_allocation["from_date"], current_allocation["to_date"],
get_default_company(frappe.session.user))
if leave_period:
leave_period = leave_period[0]
total_lwp_days = get_leaves_without_pay(
leave_period["from_date"], leave_period["to_date"],
employee)
else:
frappe.throw("Please set Leave Period")
days_diff = date_diff(date, current_allocation["from_date"]) + 1
month_diff = \
get_dates_diff(add_days(current_allocation["from_date"], (total_lwp_days if total_lwp_days else 0.0)),
date)["months"]
Calculation_option = 360 #frappe.db.get_single_value("HR Settings", "day_calculation")
old_leaves_allocated = flt(
current_allocation["total_leaves_allocated"]) - flt(
current_allocation["new_leaves_allocated"])
balance_encashment_options = frappe.db.get_single_value(
"HR Settings", "balance_encashment_options")
if Calculation_option in ("360", "365"):
days_diff -= (total_lwp_days if total_lwp_days else 0.0)
days_in_current_allocated = round_to_nearest_half(
(flt(current_allocation["new_leaves_allocated"]) /
flt(Calculation_option)) * flt(days_diff))
else:
days_in_current_allocated = round_to_nearest_half(
(flt(current_allocation["new_leaves_allocated"]) / 12) *
(month_diff if month_diff > 0 else 0))
if balance_encashment_options == "Forwarded Balance From last Year":
carry_forwarded_leaves = old_leaves_allocated
days_in_current_allocated = 0
# elif balance_encashment_options=="Balance Till Submit Day":
elif balance_encashment_options == "Annual Balance":
days_in_current_allocated = days_in_current_allocated + old_leaves_allocated
carry_forwarded_leaves += ((days_in_current_allocated)
if days_in_current_allocated > 0 else 0)
if current_allocation[
"last_encashed"] != None and carry_forwarded_leaves > flt(
current_allocation["last_encashed"]):
carry_forwarded_leaves = carry_forwarded_leaves - flt(
current_allocation["last_encashed"])
# frappe.msgprint(str(carry_forwarded_leaves))
return carry_forwarded_leaves
def calculate_gratuity(self, salaryperday, joining_date, relieving_date):
disable_rounded_total = cint(frappe.db.get_value("Global Defaults", None, "disable_rounded_total"))
# Relieving date is the last day of work
payment_days = date_diff(relieving_date, joining_date) + 1
payment_years = flt(payment_days) / 365
payment_years = rounded(payment_years, self.precision("net_pay"))
leavedaysdue = 0
if payment_years >= 1:
leavedaysdue = flt(payment_days) / 365 * 30
leavedaysdue = ceil(leavedaysdue)
from erpnext.hr.doctype.leave_application.leave_application import get_approved_leaves_for_period
leave_type = "Vacation Leave"
leavedaystaken = get_approved_leaves_for_period(self.employee, leave_type, joining_date, relieving_date)
leave_type = "Encash Leave"
leavedaystaken += flt(get_approved_leaves_for_period(self.employee, leave_type, joining_date, relieving_date))
leave_type = "Leave Without Pay"
leavedaystaken += flt(get_approved_leaves_for_period(self.employee, leave_type, joining_date, relieving_date))
leavesbalance = leavedaysdue - leavedaystaken
leaveadvance = 0
if leavesbalance < 0:
payment_days += leavesbalance
else:
leaveadvance = flt(leavesbalance) * flt(salaryperday)
leaveadvance = rounded(leaveadvance, self.precision("net_pay"))
if payment_years < 1:
gratuity_pay = 0
elif payment_years <= 5:
gratuity_pay = flt(payment_years) * 21 * flt(salaryperday)
else:
gratuity_pay = (5 * 21 * flt(salaryperday)) + (payment_years - 5) * (30 * flt(salaryperday))
self.leave_encashment_amount = leaveadvance
gratuity_pay = rounded(gratuity_pay, self.precision("net_pay"))
joiningtext = (
"Joining Date: "
+ str(joining_date)
+ " - Relieving Date: "
+ str(relieving_date)
+ " - Total Working Days: "
+ str(payment_days)
)
workingdaystext = (
"Total Leave Due: "
+ str(leavedaysdue)
+ " - Total Leave Taken: "
+ str(leavedaystaken)
+ " - Leave Balance: "
+ str(leavesbalance)
)
networkingdaytext = "Net Working Days: " + str(payment_days) + " - Net Working Years: " + str(payment_years)
gratuitytext = "Less than 1 year, no leave or gratuity<br>Between 1 and 5 years: No. of years worked * Basic Salary per day * 21<br>More than 5 years: (5 * Basic Salary per day * 21) + (No. of years worked - 5) * (Basic Salary per day * 30)"
self.gratuity_calculation = (
joiningtext + "<br>" + workingdaystext + "<br>" + networkingdaytext + "<br>" + gratuitytext + "<br>"
)
return gratuity_pay
