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
