def test_payroll_frequency(self): fiscal_year = get_fiscal_year(nowdate(), company=erpnext.get_default_company())[0] month = "%02d" % getdate(nowdate()).month m = get_month_details(fiscal_year, month) for payroll_frequency in [ "Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily" ]: make_employee(payroll_frequency + "*****@*****.**") ss = make_employee_salary_slip( payroll_frequency + "*****@*****.**", payroll_frequency) if payroll_frequency == "Monthly": self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Bimonthly": if getdate(ss.start_date).day <= 15: self.assertEqual(ss.end_date, m['month_mid_end_date']) else: self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Fortnightly": self.assertEqual(ss.end_date, add_days(nowdate(), 13)) elif payroll_frequency == "Weekly": self.assertEqual(ss.end_date, add_days(nowdate(), 6)) elif payroll_frequency == "Daily": self.assertEqual(ss.end_date, nowdate())
def execute(): ''' Update the following fields in Salary Slip: 1. Status field, if docstatus = 2, Cancelled etc, Paid if its linked to SSP 2. Frequency = Monthly 3. Start Date and End Date as per FY and Month 4. Posting Date as end of the month ''' salary_slips = frappe.db.sql("""SELECT ss.name FROM `tabSalary Slip` ss""", as_list=1) for ss in salary_slips: ss_doc = frappe.get_doc("Salary Slip", ss[0]) if ss_doc.docstatus == 2: if ss_doc.status != "Cancelled": frappe.db.set_value("Salary Slip", ss[0], "status", "Cancelled") print("Status of " + ss[0] + " set to Cancelled") elif ss_doc.docstatus == 0: if ss_doc.status != "Draft": frappe.db.set_value("Salary Slip", ss[0], "status", "Draft") print("Status of " + ss[0] + " set to Draft") if ss_doc.payroll_frequency != "Monthly": frappe.db.set_value("Salary Slip", ss[0], "payroll_frequency", "Monthly") print("Frequency of " + ss[0] + " set to Monthly") if ss_doc.fiscal_year is not None: m = get_month_details(ss_doc.fiscal_year, ss_doc.month) start_date = m['month_start_date'] end_date = m['month_end_date'] frappe.db.set_value("Salary Slip", ss[0], "start_date", start_date) frappe.db.set_value("Salary Slip", ss[0], "end_date", end_date) frappe.db.set_value("Salary Slip", ss[0], "posting_date", end_date) print("Start, End and Posting Date for " + ss[0] + " set.")
def execute(): ''' Update the following fields in Salary Slip: 1. Status field, if docstatus = 2, Cancelled etc, Paid if its linked to SSP 2. Frequency = Monthly 3. Start Date and End Date as per FY and Month 4. Posting Date as end of the month ''' salary_slips = frappe.db.sql("""SELECT ss.name FROM `tabSalary Slip` ss""", as_list=1) for ss in salary_slips: ss_doc = frappe.get_doc("Salary Slip", ss[0]) if ss_doc.docstatus == 2: if ss_doc.status != "Cancelled": frappe.db.set_value("Salary Slip", ss[0], "status", "Cancelled") print ("Status of " + ss[0] + " set to Cancelled") elif ss_doc.docstatus == 0: if ss_doc.status != "Draft": frappe.db.set_value("Salary Slip", ss[0], "status", "Draft") print ("Status of " + ss[0] + " set to Draft") if ss_doc.payroll_frequency != "Monthly": frappe.db.set_value("Salary Slip", ss[0], "payroll_frequency", "Monthly") print ("Frequency of " + ss[0] + " set to Monthly") if ss_doc.fiscal_year is not None: m = get_month_details(ss_doc.fiscal_year, ss_doc.month) start_date = m['month_start_date'] end_date = m['month_end_date'] frappe.db.set_value("Salary Slip", ss[0], "start_date", start_date) frappe.db.set_value("Salary Slip", ss[0], "end_date", end_date) frappe.db.set_value("Salary Slip", ss[0], "posting_date", end_date) print ("Start, End and Posting Date for " + ss[0] + " set.")
def execute(): frappe.reload_doctype('Salary Slip') if not frappe.db.has_column('Salary Slip', 'fiscal_year'): return salary_slips = frappe.db.sql("""select month, name, fiscal_year from `tabSalary Slip` where (month is not null and month != '') and start_date is null and end_date is null and docstatus != 2""", as_dict=True) for salary_slip in salary_slips: if not cint(salary_slip.month): continue get_start_end_date = get_month_details(salary_slip.fiscal_year, cint(salary_slip.month)) start_date = get_start_end_date['month_start_date'] end_date = get_start_end_date['month_end_date'] frappe.db.sql("""update `tabSalary Slip` set start_date = %s, end_date = %s where name = %s""", (start_date, end_date, salary_slip.name))
def execute(): frappe.reload_doctype('Salary Slip') if not frappe.db.has_column('Salary Slip', 'fiscal_year'): return salary_slips = frappe.db.sql("""select month, name, fiscal_year from `tabSalary Slip` where (month is not null and month != '') and (start_date is null or start_date = '') and (end_date is null or end_date = '') and docstatus != 2""", as_dict=True) for salary_slip in salary_slips: if not cint(salary_slip.month): continue get_start_end_date = get_month_details(salary_slip.fiscal_year, cint(salary_slip.month)) start_date = get_start_end_date['month_start_date'] end_date = get_start_end_date['month_end_date'] frappe.db.sql("""update `tabSalary Slip` set start_date = %s, end_date = %s where name = %s""", (start_date, end_date, salary_slip.name))
def test_payroll_frequency(self): fiscal_year = get_fiscal_year(nowdate(), company=erpnext.get_default_company())[0] month = "%02d" % getdate(nowdate()).month m = get_month_details(fiscal_year, month) for payroll_frequency in ["Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily"]: make_employee(payroll_frequency + "*****@*****.**") ss = make_employee_salary_slip(payroll_frequency + "*****@*****.**", payroll_frequency) if payroll_frequency == "Monthly": self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Bimonthly": if getdate(ss.start_date).day <= 15: self.assertEqual(ss.end_date, m['month_mid_end_date']) else: self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Fortnightly": self.assertEqual(ss.end_date, add_days(nowdate(),13)) elif payroll_frequency == "Weekly": self.assertEqual(ss.end_date, add_days(nowdate(),6)) elif payroll_frequency == "Daily": self.assertEqual(ss.end_date, nowdate())
def get_conditions(filters): conditions = "" if not (filters.get("month") and filters.get("fiscal_year")): msgprint(_("Please select month and year"), raise_exception=1) filters["month"] = [ "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" ].index(filters.month) + 1 if not filters.get("fiscal_year"): msgprint(_("Please select valid year"), raise_exception=1) if frappe.db.get_value("Fiscal Year", filters.fiscal_year, "year_start_date"): year_start_date, year_end_date = frappe.db.get_value( "Fiscal Year", filters.fiscal_year, ["year_start_date", "year_end_date"]) else: msgprint(_("Please select a valid year"), raise_exception=1) if filters.month >= year_start_date.strftime("%m"): year = year_start_date.strftime("%Y") else: year = year_end_date.strftime("%Y") from erpnext.hr.doctype.payroll_entry.payroll_entry import get_month_details month_details = get_month_details(year, filters.month) filters["from_date"] = month_details.month_start_date filters["to_date"] = month_details.month_end_date if filters.get("from_date"): conditions += " and start_date >= %(from_date)s" if filters.get("to_date"): conditions += " and end_date <= %(to_date)s" if filters.get("employee"): conditions += " and employee = %(employee)s" elif filters.get("company"): conditions += " and company = %(company)s" return conditions, filters, year
def month_days(employee=None, cur_date=None): #employee= "EMP/0001" if not cur_date: cur_date = nowdate() mydate = datetime.datetime.strptime(str(cur_date), "%Y-%m-%d") month = mydate.month year = mydate.year total_days_in_month = cint(monthrange(cint(year), cint(month))[1]) fiscal_year = get_fiscal_year(nowdate(), company=erpnext.get_default_company())[0] #month = "%02d" % getdate(nowdate()).month m = get_month_details(fiscal_year, month) start_date = m['month_start_date'] end_date = m['month_end_date'] if employee: number_of_days = flt(total_days_in_month) - flt( get_holidays(employee, start_date, end_date)) else: number_of_days = 26 #get working_hours_day total_time, count, hr_per_day = 0, 0, 0.0 wshift = frappe.db.get_value("Employee", employee, "work_shift") total_time = frappe.db.sql( "select ifnull(sum(round(TIMESTAMPDIFF(MINUTE,start_work,end_work)/60,2)),0) as total_hours, count(start_work) from `tabWork Shift Details` where parent=%s", wshift) if total_time and total_time[0][1] != 0: hr_per_day = float(total_time[0][0]) / float(total_time[0][1]) #wsh_time = frappe.get_all("Work Shift Details", fields=["start_work","end_work"],filters={'parent':wshift}) #for t in wsh_time: # count =count+1 # time_dif = frappe.db.sql("select TIMESTAMPDIFF(MINUTE,%s,%s)/60" ,(t.start_work,t.end_work) ) # total_time = total_time + round( float(time_dif[0][0] or 0.0),2) return total_days_in_month, number_of_days, hr_per_day
def execute(filters=None): if not filters: filters = {} data = [] columns = [] conditions, filters, year = get_conditions(filters) company = filters.company if not company: return columns, data salary_slips = get_salary_slips(conditions, filters) if not salary_slips: return columns, data columns, earning_types, ded_types = get_columns(filters, salary_slips) from erpnext.hr.doctype.payroll_entry.payroll_entry import get_month_details month_details = get_month_details(year, filters.month) no_id = filters.no_id no_leave = filters.no_leave ss_earning_map = get_ss_earning_map(salary_slips) ss_ded_map = get_ss_ded_map(salary_slips) total_salary = 0 total_count = 0 for count, ss in enumerate(salary_slips, 1): row = [] if no_leave and (ss.leave_calculation or ss.gratuity_calculation): continue vars = frappe.db.sql( """select mol_id, payroll_agent_id , payroll_agent_code from `tabEmployee` where employee = %(employee)s LIMIT 1""", {"employee": ss.employee}, as_dict=1) if filters.get("company") == "Science Lab Inc": if vars: payroll_agent_id = vars[0].payroll_agent_id if no_id and not payroll_agent_id: continue if str(payroll_agent_id).isdigit(): payroll_agent_id = str(payroll_agent_id).zfill(16) total_count += 1 row += ["EDR"] row += [payroll_agent_id] row += [ss.employee_name] row += [count] row += [month_details.month_start_date] row += [month_details.month_end_date] row += [ss.leave_without_pay] basic_pay = 0 variable_pay = 0 for e in earning_types: if "salary" in e.lower(): basic_pay += flt(ss_earning_map.get(ss.name, {}).get(e)) elif "benefit" in e.lower(): basic_pay += flt(ss_earning_map.get(ss.name, {}).get(e)) import math basic_pay = math.ceil(basic_pay) variable_pay = flt(ss.rounded_total) - flt(basic_pay) if variable_pay < 0: basic_pay = basic_pay + variable_pay variable_pay = 0 row += [basic_pay, variable_pay] row += [basic_pay + variable_pay] total_salary = total_salary + basic_pay + variable_pay data.append(row) else: if vars: payroll_agent_id = vars[0].payroll_agent_id if no_id and not payroll_agent_id: continue row += ["EDR"] row += [str(vars[0].mol_id).zfill(14)] if str(payroll_agent_id).isdigit(): payroll_agent_id = str(payroll_agent_id).zfill(23) row += [vars[0].payroll_agent_code, payroll_agent_id] row += [month_details.month_start_date] row += [month_details.month_end_date] row += [month_details.month_days] basic_pay = 0 variable_pay = 0 for e in earning_types: if "salary" in e.lower(): basic_pay += flt(ss_earning_map.get(ss.name, {}).get(e)) elif "benefit" in e.lower(): pass import math basic_pay = math.ceil(basic_pay) variable_pay = flt(ss.rounded_total) - flt(basic_pay) if variable_pay < 0: basic_pay = basic_pay + variable_pay variable_pay = 0 variable_pay = 0 total_salary = total_salary + basic_pay + variable_pay row += [basic_pay, variable_pay] row += [ss.leave_without_pay] data.append(row) row = [] row += ["SCR"] company_details = [] if company: company_details = frappe.db.sql( """select establishment_id,default_payroll_agent from `tabCompany Licenses` where company = %(company)s LIMIT 1""", {"company": company}, as_dict=1) establishment_id = "" default_payroll_agent = "" if company_details: establishment_id = company_details[0].establishment_id default_payroll_agent = company_details[0].default_payroll_agent if filters.get("company") == "Science Lab Inc": row += [default_payroll_agent] row += ["360"] import time creation_date = time.strftime("%Y-%m-%d") creation_time = time.strftime("%H%M") row += [str(filters.month).zfill(2) + str(year)] row += [""] row += [""] row += [""] row += [total_count] row += [""] row += [total_salary] row += ["*****@*****.**"] data.append(row) row = [] creation_date = time.strftime("%Y%m%d") creation_time = "{:<06}".format(creation_time) row = [ default_payroll_agent + "PR" + creation_date + creation_time + ".SIF" ] data.append(row) else: row += [str(establishment_id).zfill(13)] row += [default_payroll_agent] import time creation_date = time.strftime("%Y-%m-%d") creation_time = time.strftime("%H%M") row += [creation_date] row += [creation_time] row += [str(filters.month).zfill(2) + str(year)] row += [len(salary_slips)] row += [total_salary] row += ["AED"] row += [company] row += ["*****@*****.**"] data.append(row) row = [] creation_date = time.strftime("%y%m%d") creation_time = "{:<06}".format(creation_time) row = [establishment_id + creation_date + creation_time + ".SIF"] data.append(row) return columns, data
def get_hours(employee, fiscal_year): out = [] month_list = [ "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" ] # set up data for last column ytd_total = 0 ytd_vacation = 0 ytd_stat = 0 ytd_lieu = 0 ytd_sick = 0 ytd_ot = 0 # Get the hours from all previous years prev_total_hrs = 0 prev_vacation_hrs = 0 prev_sick_hrs = 0 prev_stat_hrs = 0 prev_lieu_hrs = 0 prev_ot_hrs = 0 m = get_month_details(fiscal_year, 1) start_date = m['month_start_date'] current_date = frappe.utils.getdate(frappe.utils.nowdate()) for timesheet_hrs in frappe.db.sql( """select detail.hours as hours, detail.activity_type as type from `tabTimesheet Detail` as detail, `tabTimesheet` as sheet where detail.from_time < %(start_time)s and detail.parent = sheet.name and sheet.docstatus = 1 and sheet.employee = %(employee)s""", { "employee": employee, "start_time": start_date }, as_dict=1): prev_total_hrs += timesheet_hrs.hours if "Vacation" in timesheet_hrs.type: prev_vacation_hrs += timesheet_hrs.hours if "Stat Holiday" in timesheet_hrs.type: prev_stat_hrs += timesheet_hrs.hours if "Sick" in timesheet_hrs.type: prev_sick_hrs += timesheet_hrs.hours if "Lieu" in timesheet_hrs.type: prev_lieu_hrs += timesheet_hrs.hours prev_total_hrs -= timesheet_hrs.hours #Get the starting value working days joining_date = frappe.db.get_value("Employee", employee, ["date_of_joining"]) working_days = 0 if not joining_date is None: holidays = get_holidays_for_employee(employee, joining_date, start_date) if start_date >= joining_date: working_days = date_diff(start_date, joining_date) - len(holidays) else: working_days = 0 else: working_days prev_ot_hrs = max(0, prev_total_hrs - 8 * working_days) row = frappe._dict({ "month": "START OF YEAR", "working_days": 0, "total_hours": prev_total_hrs, "vacation_hours": prev_vacation_hrs, "sick_hours": prev_sick_hrs, "statutory_hours": prev_stat_hrs, "lieu_hours": prev_lieu_hrs, "overtime_hours": prev_ot_hrs }) out.append(row) # Do each month for month in month_list: total_hrs = 0 vacation_hrs = 0 sick_hrs = 0 stat_hrs = 0 lieu_hrs = 0 ot_hrs = 0 index = month_list.index(month) m = get_month_details(fiscal_year, index + 1) start_date = m['month_start_date'] end_date = m['month_end_date'] for timesheet_hrs in frappe.db.sql( """SELECT detail.hours as hours, detail.activity_type as type FROM `tabTimesheet Detail` as detail, `tabTimesheet` as sheet WHERE cast(detail.from_time as date) BETWEEN %(start_time)s AND %(end_time)s AND detail.parent = sheet.name AND sheet.docstatus = 1 AND sheet.employee = %(employee)s""", { "employee": employee, "start_time": start_date, "end_time": end_date }, as_dict=1): total_hrs += timesheet_hrs.hours ytd_total += timesheet_hrs.hours if "Vacation" in timesheet_hrs.type: vacation_hrs += timesheet_hrs.hours ytd_vacation += timesheet_hrs.hours if "Stat Holiday" in timesheet_hrs.type: stat_hrs += timesheet_hrs.hours ytd_stat += timesheet_hrs.hours if "Sick" in timesheet_hrs.type: sick_hrs += timesheet_hrs.hours ytd_sick += timesheet_hrs.hours if "Lieu" in timesheet_hrs.type: lieu_hrs += timesheet_hrs.hours ytd_lieu += timesheet_hrs.hours total_hrs -= timesheet_hrs.hours ytd_total -= timesheet_hrs.hours #Get the hours in each month you're supposed to work if joining_date is None: working_days = 0 else: if end_date > current_date: end_date = current_date if end_date > start_date: month_start_date = start_date if start_date >= joining_date else joining_date holidays = get_holidays_for_employee(employee, month_start_date, end_date) if start_date >= joining_date: working_days = date_diff(end_date, start_date) + 1 - len(holidays) elif end_date >= joining_date: working_days = date_diff(end_date, joining_date) + 1 - len(holidays) else: working_days = 0 else: working_days = 0 if working_days > 0: ot_hrs = max(0, total_hrs - 8 * working_days) else: ot_hrs = 0 #if ot_hrs < 0: # ot_hrs = 0 #ot_hrs -= lieu_hrs ytd_ot += ot_hrs row = frappe._dict({ "month": month, "working_days": working_days, "total_hours": total_hrs, "vacation_hours": vacation_hrs, "sick_hours": sick_hrs, "statutory_hours": stat_hrs, "lieu_hours": lieu_hrs, "overtime_hours": ot_hrs }) out.append(row) row = frappe._dict({ "month": "YEAR TO DATE", "working_days": 0, "total_hours": ytd_total, "vacation_hours": ytd_vacation, "sick_hours": ytd_sick, "statutory_hours": ytd_stat, "lieu_hours": ytd_lieu, "overtime_hours": ytd_ot }) out.append(row) row = frappe._dict({ "month": "GRAND TOTAL", "total_hours": ytd_total + prev_total_hrs, "working_days": 0, "vacation_hours": ytd_vacation + prev_vacation_hrs, "sick_hours": ytd_sick + prev_sick_hrs, "statutory_hours": ytd_stat + prev_stat_hrs, "lieu_hours": ytd_lieu + prev_lieu_hrs, "overtime_hours": ytd_ot + prev_ot_hrs }) out.append(row) return out