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