def absent_days(employee, month, fiscal_year):
m = get_month_details(fiscal_year, month)
absent = frappe.db.sql("""
select att_date from `tabAttendance` where employee = %s and status = "Absent" and att_date between %s and %s
""", (employee, m.month_start_date, m.month_end_date),
as_list=1)
return len(absent)
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 get_leave_details(self, joining_date=None, relieving_date=None, lwp=None):
if not self.fiscal_year:
self.fiscal_year = frappe.db.get_default("fiscal_year")
if not self.month:
self.month = "%02d" % getdate(nowdate()).month
if not joining_date:
joining_date, relieving_date = frappe.db.get_value("Employee", self.employee,
["date_of_joining", "relieving_date"])
m = get_month_details(self.fiscal_year, self.month)
holidays = self.get_holidays_for_employee(m['month_start_date'], m['month_end_date'])
working_days = m["month_days"]
if not cint(frappe.db.get_value("HR Settings", None, "include_holidays_in_total_working_days")):
working_days -= len(holidays)
if working_days < 0:
frappe.throw(_("There are more holidays than working days this month."))
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.total_days_in_month = working_days
self.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m, joining_date, relieving_date)) - flt(lwp)
self.payment_days = payment_days > 0 and payment_days or 0
def test_payroll_frequency(self):
fiscal_year = get_fiscal_year(nowdate(), company="_Test Company")[0]
month = "%02d" % getdate(nowdate()).month
m = get_month_details(fiscal_year, month)
for payroll_frequncy in [
"Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily"
]:
self.make_employee(payroll_frequncy + "*****@*****.**")
ss = frappe.get_doc(
"Salary Slip",
self.make_employee_salary_slip(
payroll_frequncy + "*****@*****.**",
payroll_frequncy))
if payroll_frequncy == "Monthly":
self.assertEqual(ss.end_date, m['month_end_date'])
elif payroll_frequncy == "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_frequncy == "Fortnightly":
self.assertEqual(ss.end_date, getdate(add_days(nowdate(), 13)))
elif payroll_frequncy == "Weekly":
self.assertEqual(ss.end_date, getdate(add_days(nowdate(), 6)))
elif payroll_frequncy == "Daily":
self.assertEqual(ss.end_date, getdate(nowdate()))
def get_leave_details(self, joining_date=None, relieving_date=None, lwp=None):
if not self.fiscal_year:
self.fiscal_year = frappe.db.get_default("fiscal_year")
if not self.month:
self.month = "%02d" % getdate(nowdate()).month
if not joining_date:
joining_date, relieving_date = frappe.db.get_value("Employee", self.employee,
["date_of_joining", "relieving_date"])
m = get_month_details(self.fiscal_year, self.month)
holidays = self.get_holidays_for_employee(m['month_start_date'], m['month_end_date'])
working_days = m["month_days"]
if not cint(frappe.db.get_value("HR Settings", None, "include_holidays_in_total_working_days")):
working_days -= len(holidays)
if working_days < 0:
frappe.throw(_("There are more holidays than working days this month."))
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.total_days_in_month = working_days
self.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m, joining_date, relieving_date)) - flt(lwp)
self.payment_days = payment_days > 0 and payment_days or 0
def check_sal_struct(self):
m = get_month_details(self.fiscal_year, self.month)
struct = frappe.db.sql("""select name from `tabSalary Structure`
where employee=%s and is_active = 'Yes'
and from_date <= %s and (to_date is null or to_date >= %s)""",
(self.employee, m.month_start_date, m.month_end_date))
if not struct:
msgprint(_("No active Salary Structure found for employee {0} and the month")
.format(self.employee))
self.employee = None
return struct and struct[0][0] or ''
def check_sal_struct(self, joining_date, relieving_date):
m = get_month_details(self.fiscal_year, self.month)
struct = frappe.db.sql("""select name from `tabSalary Structure`
where employee=%s and is_active = 'Yes'
and (from_date <= %s or from_date <= %s)
and (to_date is null or to_date >= %s or to_date >= %s)""",
(self.employee, m.month_start_date, joining_date, m.month_end_date, relieving_date))
if not struct:
msgprint(_("No active Salary Structure found for employee {0} and the month")
.format(self.employee))
self.employee = None
return struct and struct[0][0] or ''
def get_attendance_details(self):
if self.employee:
self.overtime_hours_weekdays = 0
self.overtime_hours_fridays = 0
self.overtime_hours_holidays = 0
self.absent_days = 0
self.unverified_days
conditions = "employee = %(employee)s and month(att_date) = %(month)s and year(att_date) = %(fiscal_year)s"
vars = frappe.db.sql(
"""select overtime,overtime_fridays,overtime_holidays, att_date, status from tabAttendance where %s order by att_date"""
% conditions,
{"employee": self.employee, "month": self.month, "fiscal_year": self.fiscal_year},
as_dict=True,
)
total_absent = 0
total_days = 0
if vars:
for d in vars:
total_days += 1
if self.enable_attendance:
self.overtime_hours_weekdays += flt(d.overtime)
self.overtime_hours_fridays += flt(d.overtime_fridays)
self.overtime_hours_holidays += flt(d.overtime_holidays)
if d.status == "Absent":
total_absent = total_absent + 1
m = get_month_details(self.fiscal_year, self.month)
holidays = self.get_holidays_for_employee(m["month_start_date"], m["month_end_date"])
working_days = m["month_days"]
self.absent_days = flt(total_absent)
self.unverified_days = flt(working_days) - flt(total_days)
self.overtime_hours_weekdays = flt(self.overtime_hours_weekdays) / flt(
frappe.db.get_single_value("Regulations", "overtime_weekdays_rate")
)
self.overtime_hours_fridays = flt(self.overtime_hours_fridays) / flt(
frappe.db.get_single_value("Regulations", "overtime_fridays_rate")
)
self.overtime_hours_holidays = flt(self.overtime_hours_holidays) / flt(
frappe.db.get_single_value("Regulations", "overtime_holidays_rate")
)
else:
msgprint(_("No active Attendance Sheet found for employee {0} and the month").format(self.employee))
return vars or ""
def execute():
ss_columns = frappe.db.get_table_columns("Salary Slip")
if "fiscal_year" not in ss_columns or "month" not in ss_columns:
return
salary_slips = frappe.db.sql("""select fiscal_year, month, name from `tabSalary Slip`
where (month is not null and month != '')
and (fiscal_year is not null and fiscal_year != '') and
(start_date is null or start_date = '') and
(end_date is null or end_date = '') and docstatus != 2""", as_dict=1)
for salary_slip in salary_slips:
get_start_end_date = get_month_details(salary_slip.fiscal_year, 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 get_leave_details(self, lwp=None):
if not self.fiscal_year:
self.fiscal_year = frappe.db.get_default("fiscal_year")
if not self.month:
self.month = "%02d" % getdate(nowdate()).month
m = get_month_details(self.fiscal_year, self.month)
holidays = self.get_holidays_for_employee(m)
if not cint(frappe.db.get_value("HR Settings", "HR Settings", "include_holidays_in_total_working_days")):
m["month_days"] -= len(holidays)
if m["month_days"] < 0:
frappe.throw(_("There are more holidays than working days this month."))
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.total_days_in_month = m["month_days"]
self.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m)) - flt(lwp)
self.payment_days = payment_days > 0 and payment_days or 0
def test_payroll_frequency(self):
fiscal_year = get_fiscal_year(nowdate(), company="_Test Company")[0]
month = "%02d" % getdate(nowdate()).month
m = get_month_details(fiscal_year, month)
for payroll_frequncy in ["Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily"]:
self.make_employee(payroll_frequncy + "*****@*****.**")
ss = frappe.get_doc("Salary Slip",
self.make_employee_salary_slip(payroll_frequncy + "*****@*****.**", payroll_frequncy))
if payroll_frequncy == "Monthly":
self.assertEqual(ss.end_date, m['month_end_date'])
elif payroll_frequncy == "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_frequncy == "Fortnightly":
self.assertEqual(ss.end_date, getdate(add_days(nowdate(),13)))
elif payroll_frequncy == "Weekly":
self.assertEqual(ss.end_date, getdate(add_days(nowdate(),6)))
elif payroll_frequncy == "Daily":
self.assertEqual(ss.end_date, getdate(nowdate()))
def execute():
ss_columns = frappe.db.get_table_columns("Salary Slip")
if "fiscal_year" not in ss_columns or "month" not in ss_columns:
return
salary_slips = frappe.db.sql(
"""select fiscal_year, month, name from `tabSalary Slip`
where (month is not null and month != '')
and (fiscal_year is not null and fiscal_year != '') and
(start_date is null or start_date = '') and
(end_date is null or end_date = '') and docstatus != 2""",
as_dict=1,
)
for salary_slip in salary_slips:
get_start_end_date = get_month_details(salary_slip.fiscal_year, 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 get_expense_claim(doc, method):
m = get_month_details(doc.fiscal_year, doc.month)
#Get total Expense Claims Due for an Employee
query = """SELECT ec.name, ec.employee, ec.total_sanctioned_amount, ec.total_amount_reimbursed
FROM `tabExpense Claim` ec
WHERE ec.docstatus = 1 AND ec.approval_status = 'Approved' AND
ec.total_amount_reimbursed < ec.total_sanctioned_amount AND
ec.posting_date <= '%s' AND ec.employee = '%s'""" % (m.month_end_date,
doc.employee)
ec_list = frappe.db.sql(query, as_list=1)
for i in ec_list:
existing_ec = []
for e in doc.earnings:
existing_ec.append(e.expense_claim)
if i[0] not in existing_ec:
#Add earning claim for each EC separately:
doc.append("earnings", {
"idx": len(doc.earnings)+1, "depends_on_lwp": 0, "default_amount": (i[2]-i[3]), \
"expense_claim": i[0], "salary_component": "Expense Claim", "amount": (i[2]- i[3])
})
def get_leave_details(self, lwp=None):
if not self.fiscal_year:
self.fiscal_year = frappe.db.get_default("fiscal_year")
if not self.month:
self.month = "%02d" % getdate(nowdate()).month
m = get_month_details(self.fiscal_year, self.month)
holidays = self.get_holidays_for_employee(m)
if not cint(
frappe.db.get_value("HR Settings", "HR Settings",
"include_holidays_in_total_working_days")):
m["month_days"] -= len(holidays)
if m["month_days"] < 0:
frappe.throw(
_("There are more holidays than working days this month."))
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.total_days_in_month = m['month_days']
self.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m)) - flt(lwp)
self.payment_days = payment_days > 0 and payment_days or 0
def validate(doc, method):
# get_edc(doc, method)
gross_pay = 0
net_pay = 0
tot_ded = 0
tot_cont = 0
#dias_pagamento = 0
if type(doc.start_date) == unicode:
mes_startdate = datetime.datetime.strptime(doc.start_date, '%Y-%m-%d')
else:
mes_startdate = doc.start_date
#Salva Payment Days e recalcula o IRT, INSS
print "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
print doc.name, " + ", doc.employee, " + ", doc.start_date
print 'Dias de pagamento e Working Days'
print doc.payment_days, " + ", doc.total_working_days
print doc.company.encode('utf-8')
# if not doc.salary_slip_based_on_timesheet:
#Falta Injustificada
j = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Absent' and tipo_de_faltas = 'Falta Injustificada' and month(attendance_date) = %s and processar_mes_seguinte = 0 and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
#Falta Justificada c/salario
ja = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Absent' and tipo_de_faltas = 'Falta Justificada C/Salario' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
j1 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'On leave' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
j2 = frappe.db.sql(
""" SELECT sum(numero_de_horas) as horas from `tabAttendance` where employee = %s and status = 'Present' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
#Half day Injustificada
j3 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Half Day' and tipo_de_faltas = 'Falta Injustificada' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and processar_mes_seguinte = 0 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
#Still need to COUNT for Present during Holiday
fiscal_year = get_fiscal_year(doc.start_date, company=doc.company)[0]
month = "%02d" % getdate(doc.start_date).month
m = get_month_details(fiscal_year, month)
#m = get_month_details(mes_startdate.year, mes_startdate.month)
msd = m.month_start_date
med = m.month_end_date
print 'Mes start ', msd
print 'Mes END ', med
#Gets Faltas do previous month
print month
print int(month) - 1
prev_m = get_month_details(fiscal_year, int(month) - 1)
prev_msd = prev_m.month_start_date
prev_med = prev_m.month_end_date
print prev_msd
print prev_med
print mes_startdate.month
j4 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and (status = 'Half Day' or status = 'Absent') and tipo_de_faltas = 'Falta Injustificada' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and processar_mes_seguinte = 1 and company = %s """,
(doc.employee, int(month) - 1, mes_startdate.year, doc.company),
as_dict=True)
print 'Faltas no mes anterior ', j4
holiday_list = get_holiday_list_for_employee(doc.employee)
holidays = frappe.db.sql_list(
'''select holiday_date from `tabHoliday`
where
parent=%(holiday_list)s
and holiday_date >= %(start_date)s
and holiday_date <= %(end_date)s''', {
"holiday_list": holiday_list,
"start_date": msd,
"end_date": med
})
holidays = [cstr(i) for i in holidays]
trabalhouferiado = 0
for h in holidays:
print h
hh = frappe.db.sql(
""" select attendance_date,status,company,employee from `tabAttendance` where docstatus = 1 and status = 'Present' and attendance_date = %s and employee = %s and company = %s """,
(h, doc.employee, doc.company),
as_dict=True)
print hh
if hh:
print "TRABALHOU !!!!"
trabalhouferiado += 1
doc.total_working_days = doc.total_working_days + trabalhouferiado
doc.numero_de_faltas = flt(j[0]['count(status)']) + (
flt(j3[0]['count(status)']) / 2) + flt(j4[0]['count(status)'])
doc.payment_days = (
doc.payment_days + trabalhouferiado
) - j[0]['count(status)'] - j1[0]['count(status)'] - j4[0]['count(status)']
diaspagamento = doc.payment_days
totaldiastrabalho = doc.total_working_days
horasextra = j2[0]['horas']
print 'ATTENDANCE ABSENT e ON LEAVE'
print 'ATTENDANCE ABSENT e ON LEAVE'
print 'ATTENDANCE ABSENT e ON LEAVE'
print j[0]['count(status)'], j3[0]['count(status)'], j1[0]['count(status)']
print j[0]['count(status)'], j3[0]['count(status)'], j1[0]['count(status)']
print(flt(j[0]['count(status)']) + flt(j3[0]['count(status)']))
print 'Horas Extra ', j2[0]['horas']
print 'diastrab+feriado ', totaldiastrabalho + trabalhouferiado
# print doc.name , " + ", doc.employee
# print doc.payment_days - j[0]['count(status)']
# for desconto in frappe.db.sql(""" SELECT * from `tabSalary Detail` where parent = %s """,doc.name, as_dict=True):
# dd = frappe.get_doc("Salary Detail",desconto.name)
# print "valor ", dd.amount
# print "default ", dd.default_amount
# dd.amount = desconto.default_amount
# dd.save()
# if not (len(doc.get("earnings")) or len(doc.get("deductions"))):
# get details from salary structure
# print "BUSCA SALARY STRUCTURE"
# doc.get_emp_and_leave_details()
# else:
# print "BUSCA SALARY STRUCTURE com LEAVE"
# doc.get_leave_details(lwp = doc.leave_without_pay)
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "VALOR POR EXTENSO"
company_currency = erpnext.get_company_currency(doc.company)
print company_currency
if (company_currency == 'KZ'):
doc.total_in_words = num2words(doc.rounded_total,
lang='pt_BR').title() + ' Kwanzas.'
else:
doc.total_in_words = money_in_words(doc.rounded_total,
company_currency)
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "VALIDAR SUBSIDIO DE FERIAS"
valida_sub_ferias(doc, diaspagamento, totaldiastrabalho)
return
m = get_month_details(mes_startdate.year, mes_startdate.month)
msd = m.month_start_date
med = m.month_end_date
emp = frappe.get_doc("Employee", doc.employee)
tdim, twd = get_total_days(doc, method, emp, msd, med, m)
get_loan_deduction(doc, method, msd, med)
get_expense_claim(doc, method)
holidays = get_holidays(doc, method, msd, med, emp)
lwp, plw = get_leaves(doc, method, msd, med, emp)
doc.leave_without_pay = lwp
doc.posting_date = m.month_end_date
wd = twd - holidays #total working days
doc.total_days_in_month = tdim
att = frappe.db.sql("""SELECT sum(overtime), count(name) FROM `tabAttendance`
WHERE employee = '%s' AND attendance_date >= '%s' AND attendance_date <= '%s'
AND status = 'Present' AND docstatus=1""" \
%(doc.employee, msd, med),as_list=1)
half_day = frappe.db.sql("""SELECT count(name) FROM `tabAttendance`
WHERE employee = '%s' AND attendance_date >= '%s' AND attendance_date <= '%s'
AND status = 'Half Day' AND docstatus=1""" \
%(doc.employee, msd, med),as_list=1)
t_hd = flt(half_day[0][0])
t_ot = flt(att[0][0])
doc.total_overtime = t_ot
tpres = flt(att[0][1])
ual = twd - tpres - lwp - holidays - plw - (t_hd / 2)
if ual < 0:
frappe.throw(("Unauthorized Leave cannot be Negative for Employee {0}").\
format(doc.employee_name))
paydays = tpres + (t_hd / 2) + plw + math.ceil(
(tpres + (t_hd / 2)) / wd * holidays)
pd_ded = flt(doc.payment_days_for_deductions)
doc.payment_days = paydays
# if doc.change_deductions == 0:
# doc.payment_days_for_deductions = doc.payment_days
# doc.unauthorized_leaves = ual
ot_ded = round(8 * ual, 1)
if ot_ded > t_ot:
ot_ded = (int(t_ot / 8)) * 8
doc.overtime_deducted = ot_ded
d_ual = int(ot_ded / 8)
#Calculate Earnings
chk_ot = 0 #Check if there is an Overtime Rate
for d in doc.earnings:
if d.salary_component == "Overtime Rate":
chk_ot = 1
for d in doc.earnings:
earn = frappe.get_doc("Salary Component", d.salary_component)
if earn.depends_on_lwp == 1:
d.depends_on_lwp = 1
else:
d.depends_on_lwp = 0
if earn.based_on_earning:
for d2 in doc.earnings:
#Calculate Overtime Value
if earn.earning == d2.salary_component:
d.default_amount = flt(d2.amount) * t_ot
d.amount = flt(d2.amount) * (t_ot - ot_ded)
else:
if d.depends_on_lwp == 1 and earn.books == 0:
if chk_ot == 1:
d.amount = round(
flt(d.default_amount) * (paydays + d_ual) / tdim, 0)
else:
d.amount = round(
flt(d.default_amount) * (paydays) / tdim, 0)
elif d.depends_on_lwp == 1 and earn.books == 1:
d.amount = round(
flt(d.default_amount) *
flt(doc.payment_days_for_deductions) / tdim, 0)
else:
d.amount = d.default_amount
if earn.only_for_deductions <> 1:
gross_pay += flt(d.amount)
if gross_pay < 0:
doc.arrear_amount = -1 * gross_pay
gross_pay += flt(doc.arrear_amount) + flt(doc.leave_encashment_amount)
#Calculate Deductions
for d in doc.deductions:
#Check if deduction is in any earning's formula
chk = 0
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for form in earn.deduction_contribution_formula:
if d.salary_component == form.salary_component:
chk = 1
d.amount = 0
if chk == 1:
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for form in earn.deduction_contribution_formula:
if d.salary_component == form.salary_component:
d.default_amount = flt(e.default_amount) * flt(
form.percentage) / 100
d.amount += flt(e.amount) * flt(form.percentage) / 100
d.amount = round(d.amount, 0)
d.default_amount = round(d.default_amount, 0)
elif d.salary_component <> 'Loan Deduction':
str = frappe.get_doc("Salary Structure", doc.salary_structure)
for x in str.deductions:
if x.salary_component == d.salary_component:
d.default_amount = x.amount
d.amount = d.default_amount
tot_ded += d.amount
#Calculate Contributions
for c in doc.contributions:
#Check if contribution is in any earning's formula
chk = 0
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for form in earn.deduction_contribution_formula:
if c.salary_component == form.salary_component:
chk = 1
if chk == 1:
c.amount = round((flt(c.default_amount) *
flt(doc.payment_days_for_deductions) / tdim), 0)
tot_cont += c.amount
doc.gross_pay = gross_pay
doc.total_deduction = tot_ded
doc.net_pay = doc.gross_pay - doc.total_deduction
doc.rounded_total = myround(doc.net_pay, 10)
company_currency = erpnext.get_company_currency(doc.company)
doc.total_in_words = money_in_words(doc.rounded_total, company_currency)
doc.total_ctc = doc.gross_pay + tot_cont
def set_month_dates(self): if self.month and not self.salary_slip_based_on_timesheet: m = get_month_details(self.fiscal_year, self.month) self.start_date = m['month_start_date'] self.end_date = m['month_end_date']
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"])
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
prev_ot_hrs = prev_total_hrs - 8 * working_days
#if prev_ot_hrs < 0:
# prev_ot_hrs = 0
#prev_ot_hrs -= prev_lieu_hrs
row = frappe._dict({
"month": "START OF YEAR",
"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 end_date > current_date:
end_date = current_date
if end_date > start_date:
holidays = get_holidays_for_employee(employee, 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
ot_hrs = total_hrs - 8 * working_days
#if ot_hrs < 0:
# ot_hrs = 0
#ot_hrs -= lieu_hrs
ytd_ot += ot_hrs
row = frappe._dict({
"month": month,
"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",
"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,
"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
def validate(doc, method):
# get_edc(doc, method)
gross_pay = 0
net_pay = 0
tot_ded = 0
tot_cont = 0
#dias_pagamento = 0
if type(doc.start_date) == unicode:
mes_startdate = datetime.datetime.strptime(doc.start_date, '%Y-%m-%d')
else:
mes_startdate = doc.start_date
#Salva Payment Days e recalcula o IRT, INSS
print "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
print doc.name, " + ", doc.employee, " + ", doc.start_date
print 'Dias de pagamento e Working Days'
print doc.payment_days, " + ", doc.total_working_days
print doc.company.encode('utf-8')
# if not doc.salary_slip_based_on_timesheet:
#Falta Injustificada
j = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Absent' and tipo_de_faltas = 'Falta Injustificada' and month(attendance_date) = %s and processar_mes_seguinte = 0 and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
#Falta Justificada c/salario
ja = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Absent' and tipo_de_faltas = 'Falta Justificada C/Salario' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
j1 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'On leave' and leave_type != 'Subsidio de Ferias' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
j2 = frappe.db.sql(
""" SELECT sum(numero_de_horas) as horas from `tabAttendance` where employee = %s and status = 'Present' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
#Half day Injustificada
j3 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Half Day' and tipo_de_faltas = 'Falta Injustificada' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and processar_mes_seguinte = 0 and company = %s """,
(doc.employee, mes_startdate.month, mes_startdate.year, doc.company),
as_dict=True)
#Still need to COUNT for Present during Holiday
fiscal_year = get_fiscal_year(doc.start_date, company=doc.company)[0]
month = "%02d" % getdate(doc.start_date).month
m = get_month_details(fiscal_year, month)
#m = get_month_details(mes_startdate.year, mes_startdate.month)
msd = m.month_start_date
med = m.month_end_date
print 'Mes start ', msd
print 'Mes END ', med
#Gets Faltas do previous month
print month
print int(month) - 1
prev_m = get_month_details(fiscal_year, int(month) - 1)
prev_msd = prev_m.month_start_date
prev_med = prev_m.month_end_date
print prev_msd
print prev_med
print mes_startdate.month
j4 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and (status = 'Half Day' or status = 'Absent') and tipo_de_faltas = 'Falta Injustificada' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 and processar_mes_seguinte = 1 and company = %s """,
(doc.employee, int(month) - 1, mes_startdate.year, doc.company),
as_dict=True)
print 'Faltas no mes anterior ', j4
holiday_list = get_holiday_list_for_employee(doc.employee)
holidays = frappe.db.sql_list(
'''select holiday_date from `tabHoliday`
where
parent=%(holiday_list)s
and holiday_date >= %(start_date)s
and holiday_date <= %(end_date)s''', {
"holiday_list": holiday_list,
"start_date": msd,
"end_date": med
})
holidays = [cstr(i) for i in holidays]
trabalhouferiado = 0
for h in holidays:
print h
hh = frappe.db.sql(
""" select attendance_date,status,company,employee from `tabAttendance` where docstatus = 1 and status = 'Present' and attendance_date = %s and employee = %s and company = %s """,
(h, doc.employee, doc.company),
as_dict=True)
print hh
if hh:
print "TRABALHOU !!!!"
trabalhouferiado += 1
doc.total_working_days = doc.total_working_days + trabalhouferiado
doc.numero_de_faltas = flt(j[0]['count(status)']) + (
flt(j3[0]['count(status)']) / 2) + flt(j4[0]['count(status)'])
doc.payment_days = (
doc.payment_days + trabalhouferiado
) - j[0]['count(status)'] - j1[0]['count(status)'] - j4[0]['count(status)']
diaspagamento = doc.payment_days
totaldiastrabalho = doc.total_working_days
horasextra = j2[0]['horas']
print 'ATTENDANCE ABSENT e ON LEAVE'
print 'ATTENDANCE ABSENT e ON LEAVE'
print 'ATTENDANCE ABSENT e ON LEAVE'
print j[0]['count(status)'], j3[0]['count(status)'], j1[0]['count(status)']
print j[0]['count(status)'], j3[0]['count(status)'], j1[0]['count(status)']
print(flt(j[0]['count(status)']) + flt(j3[0]['count(status)']))
print 'Horas Extra ', j2[0]['horas']
print 'diastrab+feriado ', totaldiastrabalho + trabalhouferiado
# print doc.name , " + ", doc.employee
# print doc.payment_days - j[0]['count(status)']
# for desconto in frappe.db.sql(""" SELECT * from `tabSalary Detail` where parent = %s """,doc.name, as_dict=True):
# dd = frappe.get_doc("Salary Detail",desconto.name)
# print "valor ", dd.amount
# print "default ", dd.default_amount
# dd.amount = desconto.default_amount
# dd.save()
# if not (len(doc.get("earnings")) or len(doc.get("deductions"))):
# get details from salary structure
# print "BUSCA SALARY STRUCTURE"
# doc.get_emp_and_leave_details()
# else:
# print "BUSCA SALARY STRUCTURE com LEAVE"
# doc.get_leave_details(lwp = doc.leave_without_pay)
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "VALOR POR EXTENSO"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "VALIDAR SUBSIDIO DE FERIAS"
valida_sub_ferias(doc, diaspagamento, totaldiastrabalho)
company_currency = erpnext.get_company_currency(doc.company)
print company_currency
if (company_currency == 'KZ'):
doc.total_in_words = num2words(doc.rounded_total,
lang='pt_BR').title() + ' Kwanzas.'
else:
doc.total_in_words = money_in_words(doc.rounded_total,
company_currency)
return
def get_edc(doc, method):
#Earning Table should be replaced if there is any change in the Earning Composition
#Change can be of 3 types in the earning table
#1. If a user removes a type of earning
#2. If a user adds a type of earning
#3. If a user deletes and adds a type of another earning
#Function to get the Earnings, Deductions and Contributions (E,D,C)
m = get_month_details(doc.fiscal_year, doc.month)
emp = frappe.get_doc("Employee", doc.employee)
joining_date = emp.date_of_joining
if emp.relieving_date:
relieving_date = emp.relieving_date
else:
relieving_date = '2099-12-31'
struct = frappe.db.sql(
"""SELECT name FROM `tabSalary Structure Employee` WHERE employee = %s AND
is_active = 'Yes' AND (from_date <= %s OR from_date <= %s) AND
(to_date IS NULL OR to_date >= %s OR to_date >= %s)""",
(doc.employee, m.month_start_date, joining_date, m.month_end_date,
relieving_date))
if struct:
sstr = frappe.get_doc("Salary Structure", struct[0][0])
print struct[0][0]
else:
frappe.throw("No active Salary Structure for this period")
contri_amount = 0
existing_ded = []
dict = {}
for d in doc.deductions:
dict['salary_component'] = d.salary_component
dict['idx'] = d.idx
dict['default_amount'] = d.default_amount
existing_ded.append(dict.copy())
doc.contributions = []
doc.earnings = []
earn = 0
#Update Earning Table if the Earning table is empty
if doc.earnings:
pass
else:
earn = 1
chk = 0
for e in sstr.earnings:
for ern in doc.earnings:
if e.salary_component == ern.salary_component and e.idx == ern.idx:
chk = 1
if chk == 0:
doc.earnings = []
get_from_str(doc, method)
if earn == 1:
doc.earnings = []
for e in sstr.earnings:
doc.append(
"earnings", {
"salary_component": e.salary_component,
"default_amount": e.amount,
"amount": e.amount,
"idx": e.idx
})
ded = 0
if doc.deductions:
pass
else:
ded = 1
for d in doc.deductions:
found = 0
for dss in sstr.deductions:
if d.salary_component == dss.salary_component and d.idx == dss.idx and found == 0:
found = 1
if found == 0 and ded == 0:
if d.salary_component <> "Loan Deduction":
ded = 1
if ded == 1:
doc.deductions = []
for d in sstr.deductions:
doc.append(
"deductions", {
"salary_component": d.salary_component,
"default_amount": d.amount,
"amount": d.amount,
"d.idx": d.idx
})
for c in sstr.contributions:
contri = frappe.get_doc("Salary Component", c.salary_component)
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for cont in earn.deduction_contribution_formula:
if c.salary_component == cont.salary_component:
contri_amount += round(cont.percentage * e.amount / 100, 0)
doc.append(
"contributions", {
"salary_component": c.salary_component,
"default_amount": c.amount,
"amount": contri_amount
})
def absent_days(employee, month, fiscal_year):
m = get_month_details(fiscal_year, month)
absent = frappe.db.sql("""
select att_date from `tabAttendance` where employee = %s and status = "Absent" and att_date between %s and %s
""", (employee, m.month_start_date, m.month_end_date), as_list=1)
return len(absent)
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"])
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
prev_ot_hrs = prev_total_hrs - 8 * working_days
#if prev_ot_hrs < 0:
# prev_ot_hrs = 0
#prev_ot_hrs -= prev_lieu_hrs
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 end_date > current_date:
end_date = current_date
if end_date > start_date:
holidays = get_holidays_for_employee(employee, 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
ot_hrs = total_hrs - 8 * working_days
#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
def validate(doc, method):
# get_edc(doc, method)
gross_pay = 0
net_pay = 0
tot_ded = 0
tot_cont = 0
#dias_pagamento = 0
if type(doc.start_date) == unicode:
mes_startdate = datetime.datetime.strptime(doc.start_date, '%Y-%m-%d')
else:
mes_startdate = doc.start_date
#Salva Payment Days e recalcula o IRT, INSS
print "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"
print doc.name, " + ", doc.employee, " + ", doc.start_date
# if not doc.salary_slip_based_on_timesheet:
j = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'Absent' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 """,
(doc.employee, mes_startdate.month, mes_startdate.year),
as_dict=True)
j1 = frappe.db.sql(
""" SELECT count(status) from `tabAttendance` where employee = %s and status = 'On leave' and month(attendance_date) = %s and year(attendance_date) = %s and docstatus=1 """,
(doc.employee, mes_startdate.month, mes_startdate.year),
as_dict=True)
doc.numero_de_faltas = j[0]['count(status)']
doc.payment_days = doc.payment_days - j[0]['count(status)'] - j1[0][
'count(status)']
diaspagamento = doc.payment_days
totaldiastrabalho = doc.total_working_days
print j[0]['count(status)'], j1[0]['count(status)']
# print doc.name , " + ", doc.employee
# print doc.payment_days - j[0]['count(status)']
# for desconto in frappe.db.sql(""" SELECT * from `tabSalary Detail` where parent = %s """,doc.name, as_dict=True):
# dd = frappe.get_doc("Salary Detail",desconto.name)
# print "valor ", dd.amount
# print "default ", dd.default_amount
# dd.amount = desconto.default_amount
# dd.save()
# if not (len(doc.get("earnings")) or len(doc.get("deductions"))):
# get details from salary structure
# print "BUSCA SALARY STRUCTURE"
# doc.get_emp_and_leave_details()
# else:
# print "BUSCA SALARY STRUCTURE com LEAVE"
# doc.get_leave_details(lwp = doc.leave_without_pay)
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "VALOR POR EXTENSO"
company_currency = erpnext.get_company_currency(doc.company)
print company_currency
if (company_currency == 'KZ'):
doc.total_in_words = num2words(doc.rounded_total,
lang='pt_BR').title() + ' Kwanzas.'
else:
doc.total_in_words = money_in_words(doc.rounded_total,
company_currency)
print "MEU TESTE"
print "MEU TESTE"
print "MEU TESTE"
print "VALIDAR SUBSIDIO DE FERIAS"
valida_sub_ferias(doc, diaspagamento, totaldiastrabalho)
return
m = get_month_details(mes_startdate.year, mes_startdate.month)
msd = m.month_start_date
med = m.month_end_date
emp = frappe.get_doc("Employee", doc.employee)
tdim, twd = get_total_days(doc, method, emp, msd, med, m)
get_loan_deduction(doc, method, msd, med)
get_expense_claim(doc, method)
holidays = get_holidays(doc, method, msd, med, emp)
lwp, plw = get_leaves(doc, method, msd, med, emp)
doc.leave_without_pay = lwp
doc.posting_date = m.month_end_date
wd = twd - holidays #total working days
doc.total_days_in_month = tdim
att = frappe.db.sql("""SELECT sum(overtime), count(name) FROM `tabAttendance`
WHERE employee = '%s' AND attendance_date >= '%s' AND attendance_date <= '%s'
AND status = 'Present' AND docstatus=1""" \
%(doc.employee, msd, med),as_list=1)
half_day = frappe.db.sql("""SELECT count(name) FROM `tabAttendance`
WHERE employee = '%s' AND attendance_date >= '%s' AND attendance_date <= '%s'
AND status = 'Half Day' AND docstatus=1""" \
%(doc.employee, msd, med),as_list=1)
t_hd = flt(half_day[0][0])
t_ot = flt(att[0][0])
doc.total_overtime = t_ot
tpres = flt(att[0][1])
ual = twd - tpres - lwp - holidays - plw - (t_hd / 2)
if ual < 0:
frappe.throw(("Unauthorized Leave cannot be Negative for Employee {0}").\
format(doc.employee_name))
paydays = tpres + (t_hd / 2) + plw + math.ceil(
(tpres + (t_hd / 2)) / wd * holidays)
pd_ded = flt(doc.payment_days_for_deductions)
doc.payment_days = paydays
# if doc.change_deductions == 0:
# doc.payment_days_for_deductions = doc.payment_days
# doc.unauthorized_leaves = ual
ot_ded = round(8 * ual, 1)
if ot_ded > t_ot:
ot_ded = (int(t_ot / 8)) * 8
doc.overtime_deducted = ot_ded
d_ual = int(ot_ded / 8)
#Calculate Earnings
chk_ot = 0 #Check if there is an Overtime Rate
for d in doc.earnings:
if d.salary_component == "Overtime Rate":
chk_ot = 1
for d in doc.earnings:
earn = frappe.get_doc("Salary Component", d.salary_component)
if earn.depends_on_lwp == 1:
d.depends_on_lwp = 1
else:
d.depends_on_lwp = 0
if earn.based_on_earning:
for d2 in doc.earnings:
#Calculate Overtime Value
if earn.earning == d2.salary_component:
d.default_amount = flt(d2.amount) * t_ot
d.amount = flt(d2.amount) * (t_ot - ot_ded)
else:
if d.depends_on_lwp == 1 and earn.books == 0:
if chk_ot == 1:
d.amount = round(
flt(d.default_amount) * (paydays + d_ual) / tdim, 0)
else:
d.amount = round(
flt(d.default_amount) * (paydays) / tdim, 0)
elif d.depends_on_lwp == 1 and earn.books == 1:
d.amount = round(
flt(d.default_amount) *
flt(doc.payment_days_for_deductions) / tdim, 0)
else:
d.amount = d.default_amount
if earn.only_for_deductions <> 1:
gross_pay += flt(d.amount)
if gross_pay < 0:
doc.arrear_amount = -1 * gross_pay
gross_pay += flt(doc.arrear_amount) + flt(doc.leave_encashment_amount)
#Calculate Deductions
for d in doc.deductions:
#Check if deduction is in any earning's formula
chk = 0
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for form in earn.deduction_contribution_formula:
if d.salary_component == form.salary_component:
chk = 1
d.amount = 0
if chk == 1:
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for form in earn.deduction_contribution_formula:
if d.salary_component == form.salary_component:
d.default_amount = flt(e.default_amount) * flt(
form.percentage) / 100
d.amount += flt(e.amount) * flt(form.percentage) / 100
d.amount = round(d.amount, 0)
d.default_amount = round(d.default_amount, 0)
elif d.salary_component <> 'Loan Deduction':
str = frappe.get_doc("Salary Structure", doc.salary_structure)
for x in str.deductions:
if x.salary_component == d.salary_component:
d.default_amount = x.amount
d.amount = d.default_amount
tot_ded += d.amount
#Calculate Contributions
for c in doc.contributions:
#Check if contribution is in any earning's formula
chk = 0
for e in doc.earnings:
earn = frappe.get_doc("Salary Component", e.salary_component)
for form in earn.deduction_contribution_formula:
if c.salary_component == form.salary_component:
chk = 1
if chk == 1:
c.amount = round((flt(c.default_amount) *
flt(doc.payment_days_for_deductions) / tdim), 0)
tot_cont += c.amount
doc.gross_pay = gross_pay
doc.total_deduction = tot_ded
doc.net_pay = doc.gross_pay - doc.total_deduction
doc.rounded_total = myround(doc.net_pay, 10)
company_currency = erpnext.get_company_currency(doc.company)
doc.total_in_words = money_in_words(doc.rounded_total, company_currency)
doc.total_ctc = doc.gross_pay + tot_cont
def set_month_dates(self):
if self.month and not self.salary_slip_based_on_timesheet:
m = get_month_details(self.fiscal_year, self.month)
self.start_date = m['month_start_date']
self.end_date = m['month_end_date']
def calculate_leaveadvance(self, salaryperday, joining_date, relieving_date):
disable_rounded_total = cint(frappe.db.get_value("Global Defaults", None, "disable_rounded_total"))
if relieving_date:
return
else:
m = get_month_details(self.fiscal_year, self.month)
dt = add_days(cstr(m["month_start_date"]), m["month_days"] + 2)
leave = frappe.db.sql(
"""
select from_date,to_date,leave_type
from `tabLeave Application` t1
where (t1.leave_type = 'Vacation Leave' OR t1.leave_type = 'Encash Leave')
and t1.docstatus < 2
and t1.status = 'Approved'
and t1.employee = %s
and t1.from_date <= %s
ORDER BY to_date DESC LIMIT 2""",
(self.employee, dt),
as_dict=True,
)
frappe.errprint(leave)
if leave:
if leave[0].leave_type == "Vacation Leave":
relieving_date = leave[0].from_date
# Relieving date should be decremented since leave applications include the first day
relieving_date = relieving_date - timedelta(days=1)
if relieving_date < m["month_start_date"]:
self.encash_leave = 0
frappe.msgprint(
_(
"No leave applications found for this period. Please approve a leave application for this employee"
)
)
return
if date_diff(relieving_date, joining_date) < 365:
frappe.msgprint(_("This employee has worked at the company for less than a year."))
if len(leave) > 1:
joining_date = leave[1].to_date
# Joining date should be incremented since leave applications include the last day
joining_date = joining_date + timedelta(days=1)
frappe.msgprint(
_("Calculating Leave From Date {0} To Date {1}.").format(joining_date, relieving_date)
)
else:
frappe.msgprint(
_("No previous application found for this employee. Using company joining date.")
)
elif leave[0].leave_type == "Encash Leave":
relieving_date = leave[0].to_date
joining_date = leave[0].from_date
frappe.msgprint(_("Special Case: Leave Encashment application dated {0}.").format(relieving_date))
else:
self.encash_leave = 0
frappe.msgprint(
_(
"No VACATION/ENCASH leave applications found for this period. Change LEAVE WITHOUT PAY Applications to VACATION/ENCASH"
)
)
return
else:
self.encash_leave = 0
frappe.msgprint(
_(
"No leave applications found for this period. Please approve a valid leave application for this employee"
)
)
return
payment_days = date_diff(relieving_date, joining_date) + 1
leavedaysdue = flt(payment_days) / 365 * 30
leavedaysdue = ceil(leavedaysdue)
if leavedaysdue < 30 and leavedaysdue + 2 >= 30:
leavedaysdue = 30
leaveadvance = flt(leavedaysdue) * flt(salaryperday)
leaveadvance = rounded(leaveadvance, self.precision("net_pay"))
joiningtext = (
"From Date: "
+ str(joining_date)
+ " - To Date: "
+ str(relieving_date)
+ " - Total Working Days: "
+ str(payment_days)
)
workingdaystext = "Leave Days Due (Rounded): " + str(leavedaysdue)
leavetext = "30 Days Leave Accumulated Every Year"
self.leave_calculation = joiningtext + " - " + workingdaystext + "<br>" + leavetext + "<br>"
return leaveadvance
