def get_holidays(employee, from_date, to_date):
'''get holidays between two dates for the given employee'''
holiday_list = get_holiday_list_for_employee(employee)
holidays = frappe.db.sql("""select count(distinct holiday_date) from `tabHoliday` h1, `tabHoliday List` h2
where h1.parent = h2.name and h1.holiday_date between %s and %s
and h2.name = %s""", (from_date, to_date, holiday_list))[0][0]
return holidays
def get_count_holidays_of_employee(self, employee):
holiday_list = get_holiday_list_for_employee(employee)
holidays = 0
if holiday_list:
days = frappe.db.sql("""select count(*) from tabHoliday where
parent=%s and holiday_date between %s and %s""", (holiday_list,
self.start_date, self.end_date))
if days and days[0][0]:
holidays = days[0][0]
return holidays
def get_holidays_for_employee(self, start_date, end_date):
holiday_list = get_holiday_list_for_employee(self.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": start_date, "end_date": end_date},
)
holidays = [cstr(i) for i in holidays]
return holidays
def add_holidays(events, start, end, employee, company):
applicable_holiday_list = get_holiday_list_for_employee(employee, company)
if not applicable_holiday_list:
return
for holiday in frappe.db.sql("""select name, holiday_date, description
from `tabHoliday` where parent=%s and holiday_date between %s and %s""",
(applicable_holiday_list, start, end), as_dict=True):
events.append({
"doctype": "Holiday",
"from_date": holiday.holiday_date,
"to_date": holiday.holiday_date,
"title": _("Holiday") + ": " + cstr(holiday.description),
"name": holiday.name
})
def add_holidays(events, start, end, employee, company):
applicable_holiday_list = get_holiday_list_for_employee(employee, company)
if not applicable_holiday_list:
return
for holiday in frappe.db.sql("""select name, holiday_date, description
from `tabHoliday` where parent=%s and holiday_date between %s and %s""",
(applicable_holiday_list, start, end),
as_dict=True):
events.append({
"doctype": "Holiday",
"from_date": holiday.holiday_date,
"to_date": holiday.holiday_date,
"title": _("Holiday") + ": " + cstr(holiday.description),
"name": holiday.name
})
def get_holidays_for_employee(employee, start_date, end_date):
holiday_list = get_holiday_list_for_employee(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": start_date,
"end_date": end_date
})
holidays = [cstr(i) for i in holidays]
return holidays
def mark_absent_for_dates_with_no_attendance(self, employee):
"""Marks Absents for the given employee on working days in this shift which have no attendance marked.
The Absent is marked starting from 'process_attendance_after' or employee creation date.
"""
date_of_joining, relieving_date, employee_creation = frappe.db.get_value(
"Employee", employee, ["date_of_joining", "relieving_date", "creation"]
)
if not date_of_joining:
date_of_joining = employee_creation.date()
start_date = max(getdate(self.process_attendance_after), date_of_joining)
actual_shift_datetime = get_actual_start_end_datetime_of_shift(
employee, get_datetime(self.last_sync_of_checkin), True
)
last_shift_time = (
actual_shift_datetime[0]
if actual_shift_datetime[0]
else get_datetime(self.last_sync_of_checkin)
)
prev_shift = get_employee_shift(
employee, last_shift_time.date() - timedelta(days=1), True, "reverse"
)
if prev_shift:
end_date = (
min(prev_shift.start_datetime.date(), relieving_date)
if relieving_date
else prev_shift.start_datetime.date()
)
else:
return
holiday_list_name = self.holiday_list
if not holiday_list_name:
holiday_list_name = get_holiday_list_for_employee(employee, False)
dates = get_filtered_date_list(employee, start_date, end_date, holiday_list=holiday_list_name)
for date in dates:
shift_details = get_employee_shift(employee, date, True)
if shift_details and shift_details.shift_type.name == self.name:
attendance = mark_attendance(employee, date, "Absent", self.name)
if attendance:
frappe.get_doc(
{
"doctype": "Comment",
"comment_type": "Comment",
"reference_doctype": "Attendance",
"reference_name": attendance,
"content": frappe._("Employee was marked Absent due to missing Employee Checkins."),
}
).insert(ignore_permissions=True)
def validate_employee_advance(employee, date):
policies = frappe.get_doc("Advance Salary Policies",
"Advance Salary Policies")
employee_type = frappe.db.get_value("Employee", employee,
"employment_type")
if employee_type == "Probation":
frappe.msgprint(
_("Advance Salary Request Apply After Probation Period"))
return False
start_date = frappe.db.get_value("Employee", employee,
"final_confirmation_date")
if getdate(start_date) > getdate(date):
frappe.msgprint(
_("Advance Salary Request Apply After Probation Period"))
return False
if get_salary_advance_total(employee, date, policies) == True:
frappe.msgprint(_("Already Applied Employee Advance In Last 6 Months"))
return False
sal_st = get_sal_structure(employee)
salary_slip = make_salary_slip_custom(sal_st,
employee=employee,
start_date=get_first_day(date),
end_date=add_days(
get_first_day(date), 19),
ignore_permissions=True)
if salary_slip.gross_pay > flt(
policies.salary_should_be_below_or_equal_to):
frappe.msgprint(
_("Gross Pay Must Be Less Than or Equal {0}").format(
policies.salary_should_be_below_or_equal_to))
return False
holiday_list = get_holiday_list_for_employee(employee, False)
attendance = get_filtered_date_list(employee, get_first_day(date),
add_days(get_first_day(date), 19),
holiday_list)
working_days = date_diff(get_last_day(date), get_first_day(date)) + 1
gross_pay_day = salary_slip.gross_pay / working_days
final_working_days = 20 - ((flt(salary_slip.total_working_days) - len(
get_holidays_for_employee(employee, get_first_day(date),
add_days(get_first_day(date), 19)))) -
flt(attendance))
gross_pay_eligible_for_advance = (flt(gross_pay_day) *
flt(final_working_days))
loan = get_loan_amount(employee, get_first_day(date), get_last_day(date))
return gross_pay_eligible_for_advance, loan
def validate_schedule_date_for_holiday_list(self, schedule_date, sales_person):
validated = False
employee = frappe.db.get_value("Sales Person", sales_person, "employee")
holiday_list = get_holiday_list_for_employee(employee)
holidays = frappe.db.sql_list('''select holiday_date from `tabHoliday` where parent=%s''', holiday_list)
if not validated and holidays:
# max iterations = len(holidays)
for i in xrange(len(holidays)):
if schedule_date in holidays:
schedule_date = add_days(schedule_date, -1)
else:
validated = True
break
return schedule_date
def create_leave_ledger_entry(la, submit=True):
if la.status != 'Approved' and submit:
return
expiry_date = get_allocation_expiry(la.employee, la.leave_type, la.to_date,
la.from_date)
lwp = frappe.db.get_value("Leave Type", la.leave_type, "is_lwp")
if expiry_date:
create_ledger_entry_for_intermediate_allocation_expiry(
expiry_date, submit, lwp)
else:
args = dict(leaves=la.total_leave_days * -1,
from_date=la.from_date,
to_date=la.to_date,
is_lwp=lwp,
holiday_list=get_holiday_list_for_employee(la.employee))
base_leave_ledger(la, args, submit)
def get_holidays(doc, start_date, end_date,emp):
if emp.relieving_date is None:
relieving_date = datetime.date(2099, 12, 31)
else:
relieving_date = emp.relieving_date
if emp.date_of_joining > start_date:
start_date = emp.date_of_joining
if relieving_date < end_date:
end_date = relieving_date
holiday_list = get_holiday_list_for_employee(doc.employee)
holidays = frappe.db.sql("""SELECT count(name) FROM `tabHoliday` WHERE parent = '%s' AND
holiday_date >= '%s' AND holiday_date <= '%s'""" %(holiday_list, \
start_date, end_date), as_list=1)
holidays = flt(holidays[0][0]) #no of holidays in a month from the holiday list
return holidays
def get_holidays(doc, start_date, end_date,emp):
if emp.relieving_date is None:
relieving_date = datetime.date(2099, 12, 31)
else:
relieving_date = emp.relieving_date
if emp.date_of_joining > start_date:
start_date = emp.date_of_joining
if relieving_date < end_date:
end_date = relieving_date
holiday_list = get_holiday_list_for_employee(doc.employee)
holidays = frappe.db.sql("""SELECT count(name) FROM `tabHoliday` WHERE parent = '%s' AND
holiday_date >= '%s' AND holiday_date <= '%s'""" %(holiday_list, \
start_date, end_date), as_list=1)
holidays = flt(holidays[0][0]) #no of holidays in a month from the holiday list
return holidays
def create_ledger_entry_for_intermediate_allocation_expiry(
self, expiry_date, submit, lwp):
''' splits leave application into two ledger entries to consider expiry of allocation '''
args = dict(
from_date=self.from_date,
to_date=expiry_date,
leaves=(date_diff(expiry_date, self.from_date) + 1) * -1,
is_lwp=lwp,
holiday_list=get_holiday_list_for_employee(self.employee),
)
create_leave_ledger_entry(self, args, submit)
if getdate(expiry_date) != getdate(self.to_date):
start_date = add_days(expiry_date, 1)
args.update(
dict(from_date=start_date,
to_date=self.to_date,
leaves=date_diff(self.to_date, expiry_date) * -1))
create_leave_ledger_entry(self, args, submit)
def get_data(args):
dates = get_dates(args)
employees = get_active_employees()
holidays = get_holidays_for_employees(
[employee.name for employee in employees], args["from_date"],
args["to_date"])
existing_attendance_records = get_existing_attendance_records(args)
data = []
for date in dates:
for employee in employees:
if getdate(date) < getdate(employee.date_of_joining):
continue
if employee.relieving_date:
if getdate(date) > getdate(employee.relieving_date):
continue
existing_attendance = {}
if existing_attendance_records \
and tuple([getdate(date), employee.name]) in existing_attendance_records \
and getdate(employee.date_of_joining) <= getdate(date) \
and getdate(employee.relieving_date) >= getdate(date):
existing_attendance = existing_attendance_records[tuple(
[getdate(date), employee.name])]
employee_holiday_list = get_holiday_list_for_employee(
employee.name)
row = [
existing_attendance and existing_attendance.name or "",
employee.name,
employee.employee_name,
date,
existing_attendance and existing_attendance.status or "",
existing_attendance and existing_attendance.leave_type or "",
employee.company,
existing_attendance and existing_attendance.naming_series
or get_naming_series(),
]
if date in holidays[employee_holiday_list]:
row[4] = "Holiday"
data.append(row)
return data
def mark_absent_for_dates_with_no_attendance(self, employee):
"""Marks Absents for the given employee on working days in this shift which have no attendance marked.
The Absent is marked starting from 'process_attendance_after' or employee creation date.
"""
start_date, end_date = self.get_start_and_end_dates(employee)
# no shift assignment found, no need to process absent attendance records
if start_date is None:
return
holiday_list_name = self.holiday_list
if not holiday_list_name:
holiday_list_name = get_holiday_list_for_employee(employee, False)
start_time = get_time(self.start_time)
for date in daterange(getdate(start_date), getdate(end_date)):
if is_holiday(holiday_list_name, date):
# skip marking absent on a holiday
continue
timestamp = datetime.combine(date, start_time)
shift_details = get_employee_shift(employee, timestamp, True)
if shift_details and shift_details.shift_type.name == self.name:
attendance = mark_attendance(employee, date, "Absent",
self.name)
if attendance:
frappe.get_doc({
"doctype":
"Comment",
"comment_type":
"Comment",
"reference_doctype":
"Attendance",
"reference_name":
attendance,
"content":
frappe.
_("Employee was marked Absent due to missing Employee Checkins."
),
}).insert(ignore_permissions=True)
def process_sandwich_leave_daily(self):
holiday_date = []
last_date = add_days(self.attendance_date, -1)
on_leave = leave_check = False
while leave_check == False:
if check_leave(self, last_date):
last_date = add_days(last_date, -1)
on_leave = True
else:
leave_check = True
holiday = get_holiday_list_for_employee(self.employee)
frappe.errprint(holiday)
if on_leave == True:
while check_holiday(last_date, holiday):
frappe.errprint('holiday')
holiday_date.append(last_date)
last_date = add_days(last_date, -1)
if len(holiday_date) >= 1:
if check_leave(self, last_date):
for hd in holiday_date:
create_leave(self.employee, hd, 0)
def get_holidays_for_employee(self, start_date, end_date, get_type=None):
holiday_list = get_holiday_list_for_employee(self.employee)
count, d_count = frappe.db.sql('''select count(*),count(distinct holiday_date) from `tabHoliday`
where holiday_date >= %(start_date)s
and holiday_date <= %(end_date)s''', {
"start_date": start_date,
"end_date": end_date
})[0]
if count == d_count:
if count == 0 and get_type == "working":
frappe.msgprint(_("No Holiday lists have been found for in this period. The default Holiday list is {0} as specified in the Company.").format(holiday_list))
holidays = frappe.db.sql_list('''select holiday_date from `tabHoliday`
where holiday_date >= %(start_date)s
and holiday_date <= %(end_date)s''', {
"start_date": start_date,
"end_date": end_date
})
else:
if get_type == "working":
frappe.msgprint(_("Multiple Holiday lists have been found for the same period. The default Holiday list {0} has been selected.").format(holiday_list))
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": start_date,
"end_date": end_date
})
#if len(holidays) == 0 and get_type == "working":
# frappe.msgprint(_("There is no Holiday Date in this payslip period."))
holidays = [cstr(i) for i in holidays]
return holidays
def mark_absent_for_dates_with_no_attendance(self, employee):
"""Marks Absents for the given employee on working days in this shift which have no attendance marked.
The Absent is marked starting from 'process_attendance_after' or employee creation date.
"""
date_of_joining, relieving_date, employee_creation = frappe.db.get_value("Employee", employee, ["date_of_joining", "relieving_date", "creation"])
if not date_of_joining:
date_of_joining = employee_creation.date()
start_date = max(getdate(self.process_attendance_after), date_of_joining)
actual_shift_datetime = get_actual_start_end_datetime_of_shift(employee, get_datetime(self.last_sync_of_checkin), True)
last_shift_time = actual_shift_datetime[0] if actual_shift_datetime[0] else get_datetime(self.last_sync_of_checkin)
prev_shift = get_employee_shift(employee, last_shift_time.date()-timedelta(days=1), True, 'reverse')
if prev_shift:
end_date = min(prev_shift.start_datetime.date(), relieving_date) if relieving_date else prev_shift.start_datetime.date()
else:
return
holiday_list_name = self.holiday_list
if not holiday_list_name:
holiday_list_name = get_holiday_list_for_employee(employee, False)
dates = get_filtered_date_list(employee, start_date, end_date, holiday_list=holiday_list_name)
for date in dates:
shift_details = get_employee_shift(employee, date, True)
if shift_details and shift_details.shift_type.name == self.name:
mark_absent(employee, date, self.name)
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_employee_shift(employee,
for_date=None,
consider_default_shift=False,
next_shift_direction=None):
"""Returns a Shift Type for the given employee on the given date. (excluding the holidays)
:param employee: Employee for which shift is required.
:param for_date: Date on which shift are required
:param consider_default_shift: If set to true, default shift is taken when no shift assignment is found.
:param next_shift_direction: One of: None, 'forward', 'reverse'. Direction to look for next shift if shift not found on given date.
"""
if for_date is None:
for_date = nowdate()
default_shift = frappe.db.get_value("Employee", employee, "default_shift")
shift_type_name = None
shift_assignment_details = frappe.db.get_value(
"Shift Assignment",
{
"employee": employee,
"start_date": ("<=", for_date),
"docstatus": "1",
"status": "Active"
},
["shift_type", "end_date"],
)
if shift_assignment_details:
shift_type_name = shift_assignment_details[0]
# if end_date present means that shift is over after end_date else it is a ongoing shift.
if shift_assignment_details[
1] and for_date >= shift_assignment_details[1]:
shift_type_name = None
if not shift_type_name and consider_default_shift:
shift_type_name = default_shift
if shift_type_name:
holiday_list_name = frappe.db.get_value("Shift Type", shift_type_name,
"holiday_list")
if not holiday_list_name:
holiday_list_name = get_holiday_list_for_employee(employee, False)
if holiday_list_name and is_holiday(holiday_list_name, for_date):
shift_type_name = None
if not shift_type_name and next_shift_direction:
MAX_DAYS = 366
if consider_default_shift and default_shift:
direction = -1 if next_shift_direction == "reverse" else +1
for i in range(MAX_DAYS):
date = for_date + timedelta(days=direction * (i + 1))
shift_details = get_employee_shift(employee, date,
consider_default_shift,
None)
if shift_details:
shift_type_name = shift_details.shift_type.name
for_date = date
break
else:
direction = "<" if next_shift_direction == "reverse" else ">"
sort_order = "desc" if next_shift_direction == "reverse" else "asc"
dates = frappe.db.get_all(
"Shift Assignment",
["start_date", "end_date"],
{
"employee": employee,
"start_date": (direction, for_date),
"docstatus": "1",
"status": "Active",
},
as_list=True,
limit=MAX_DAYS,
order_by="start_date " + sort_order,
)
if dates:
for date in dates:
if date[1] and date[1] < for_date:
continue
shift_details = get_employee_shift(employee, date[0],
consider_default_shift,
None)
if shift_details:
shift_type_name = shift_details.shift_type.name
for_date = date[0]
break
return get_shift_details(shift_type_name, for_date)
def check_attendance(ddate=nowdate(), hour=20, nowF=False):
#print str(ddate)
#ddate = '2019-01-09'
#send_aler_email("Absent Script Running")
#frappe.msgprint(str(nowF)+str(now_datetime()))
#frappe.msgprint ("dd "+str(ddate))
employees = frappe.get_all("Employee",
fields=[
"name", "work_shift", "employee_name",
"scheduled_confirmation_date",
"date_of_joining"
],
filters={'status': 'Active'})
today = calendar.day_name[getdate(ddate).weekday()]
#today= day #getdate(nowdate()) #'2018-08-13'
cur_time = get_time(now_datetime())
#print cur_time.hour
for emp in employees:
holiday_list = get_holiday_list_for_employee(emp)
if holiday_list:
holiday_filter = [["parent", "=", holiday_list],
["holiday_date", "<=", ddate]]
holidays = frappe.get_all("Holiday",
fields=["holiday_date"],
filters=holiday_filter)
for holiday in holidays:
if str(ddate) == str(holiday):
print "False"
return False
attendance = frappe.db.get_value(
"Attendance", {
"employee": emp.name,
"attendance_date": getdate(ddate),
"docstatus": ("!=", 2)
}, "name")
departure = frappe.db.get_value(
"Departure", {
"employee": emp.name,
"departure_date": getdate(ddate),
"docstatus": ("!=", 2)
}, "name")
#emp_work_shift = frappe.db.get_value("Employee Employment Detail", emp.name, "work_shift")
#end_time = frappe.db.get_value("Work Shift Details", {"parent":emp_work_shift,"day":today}, "end_work")
#print cur_time.hour
if emp.date_of_joining:
date_of_joining = emp.date_of_joining
if emp.scheduled_confirmation_date:
date_of_joining = emp.scheduled_confirmation_date
if nowF:
condition = (not attendance and not departure) or (
not attendance or not departure) or (not attendance
and departure)
else:
condition = (
(not attendance and not departure) or
(not attendance or not departure) or
(not attendance and departure)) and cur_time.hour >= hour
if frappe.db.get_value("Employee", {
'status': 'Active',
'employee_number': emp.name
}, "name") and condition and getdate(ddate) >= date_of_joining:
if not attendance and departure:
dep = frappe.get_doc('Departure', departure)
dep.docstatus = 2
dep.flags.ignore_validate = True
dep.flags.ignore_validate_update_after_submit = True
dep.save(ignore_permissions=True)
atten = frappe.new_doc('Attendance')
atten.update({
'name': emp.employee_name,
'employee_name': emp.employee_name,
'employee': emp.name,
'status': 'Absent',
"docstatus": 1,
"attendance_date": getdate(ddate),
"attendance_time": '00:00:00'
})
atten.flags.ignore_validate = True
atten.save(ignore_permissions=True)
dept = frappe.new_doc('Departure')
dept.update({
'name': emp.employee_name,
'employee_name': emp.employee_name,
'employee': emp.name,
'status': 'Absent',
"docstatus": 1,
"departure_time": '00:00:00',
"departure_date": getdate(ddate)
})
dept.flags.ignore_validate = True
dept.insert(ignore_permissions=True)
print 'added |' + str(emp.name)
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 get_employee_shift(employee,
for_date=nowdate(),
consider_default_shift=False,
next_shift_direction=None):
"""Returns a Shift Type for the given employee on the given date. (excluding the holidays)
:param employee: Employee for which shift is required.
:param for_date: Date on which shift are required
:param consider_default_shift: If set to true, default shift is taken when no shift assignment is found.
:param next_shift_direction: One of: None, 'forward', 'reverse'. Direction to look for next shift if shift not found on given date.
"""
default_shift = frappe.db.get_value('Employee', employee, 'default_shift')
shift_type_name = frappe.db.get_value('Shift Assignment', {
'employee': employee,
'date': for_date,
'docstatus': '1'
}, 'shift_type')
if not shift_type_name and consider_default_shift:
shift_type_name = default_shift
if shift_type_name:
holiday_list_name = frappe.db.get_value('Shift Type', shift_type_name,
'holiday_list')
if not holiday_list_name:
holiday_list_name = get_holiday_list_for_employee(employee, False)
if holiday_list_name and is_holiday(holiday_list_name, for_date):
shift_type_name = None
if not shift_type_name and next_shift_direction:
MAX_DAYS = 366
if consider_default_shift and default_shift:
direction = -1 if next_shift_direction == 'reverse' else +1
for i in range(MAX_DAYS):
date = for_date + timedelta(days=direction * (i + 1))
shift_details = get_employee_shift(employee, date,
consider_default_shift,
None)
if shift_details:
shift_type_name = shift_details.shift_type.name
for_date = date
break
else:
direction = '<' if next_shift_direction == 'reverse' else '>'
sort_order = 'desc' if next_shift_direction == 'reverse' else 'asc'
dates = frappe.db.get_all('Shift Assignment',
'date', {
'employee': employee,
'date': (direction, for_date),
'docstatus': '1'
},
as_list=True,
limit=MAX_DAYS,
order_by="date " + sort_order)
for date in dates:
shift_details = get_employee_shift(employee, date[0],
consider_default_shift,
None)
if shift_details:
shift_type_name = shift_details.shift_type.name
for_date = date[0]
break
return get_shift_details(shift_type_name, for_date)
def process_attendance(date=None):
try:
if date == None:
date = add_days(today(), -1)
shift_data = frappe.get_all("Shift Type",
filters={"shift_type": "Day Shift"},
fields=["name", "start_time", "end_time"])
employee_list_logs = []
for shift in shift_data:
"""
Process Attendance for each Shift
"""
# Get Attendance Log for current shift and for yesterday date or date pass from run_attendance_manual
attendance_log = frappe.db.sql(
"""select * from `tabAttendance Log` where shift=%s and Date(attendance_time)=%s order by employee,attendance_time""",
(shift.name, date),
as_dict=1)
frappe.errprint(attendance_log)
for key, group in itertools.groupby(
attendance_log,
key=lambda x: (x['employee'], x['shift_start_time'])):
frappe.errprint(key)
# frappe.errprint(list(group))
logs = list(group)
if logs:
#logs has combinations of in out logs for perticular shift
try:
in_time, out_time, total_hours, early_exit, late_entry, miss_punch = get_attendance_details(
shift, logs)
holiday_list = get_holiday_list_for_employee(
logs[0].employee)
if check_holiday(getdate(logs[0].attendance_time),
holiday_list):
print("Holiday attendance start")
create_holiday_attendance(
logs[0].employee,
getdate(logs[0].attendance_time), in_time,
out_time, total_hours)
print("Holiday Attendance Done")
else:
print("Creating Attendance Start")
create_attendance(logs[0].employee,
getdate(logs[0].attendance_time),
in_time, out_time, total_hours,
early_exit, late_entry,
miss_punch, shift)
print("Attendance Creation Done")
print(
"Attendance creaetion or create holiday attendance done"
)
employee_list_logs.append(logs[0].employee)
except Exception as e:
print('exception')
frappe.log_error(frappe.get_traceback())
# create_lwp_for_missing_employee(employee_list_logs,date)
except Exception as e:
frappe.log_error(frappe.get_traceback())
def create_separate_ledger_entries(self, alloc_on_from_date, alloc_on_to_date, submit, lwp):
"""Creates separate ledger entries for application period falling into separate allocations"""
# for creating separate ledger entries existing allocation periods should be consecutive
if (
submit
and alloc_on_from_date
and alloc_on_to_date
and add_days(alloc_on_from_date.to_date, 1) != alloc_on_to_date.from_date
):
frappe.throw(
_(
"Leave Application period cannot be across two non-consecutive leave allocations {0} and {1}."
).format(
get_link_to_form("Leave Allocation", alloc_on_from_date.name),
get_link_to_form("Leave Allocation", alloc_on_to_date),
)
)
raise_exception = False if frappe.flags.in_patch else True
if alloc_on_from_date:
first_alloc_end = alloc_on_from_date.to_date
second_alloc_start = add_days(alloc_on_from_date.to_date, 1)
else:
first_alloc_end = add_days(alloc_on_to_date.from_date, -1)
second_alloc_start = alloc_on_to_date.from_date
leaves_in_first_alloc = get_number_of_leave_days(
self.employee,
self.leave_type,
self.from_date,
first_alloc_end,
self.half_day,
self.half_day_date,
)
leaves_in_second_alloc = get_number_of_leave_days(
self.employee,
self.leave_type,
second_alloc_start,
self.to_date,
self.half_day,
self.half_day_date,
)
args = dict(
is_lwp=lwp,
holiday_list=get_holiday_list_for_employee(self.employee, raise_exception=raise_exception)
or "",
)
if leaves_in_first_alloc:
args.update(
dict(from_date=self.from_date, to_date=first_alloc_end, leaves=leaves_in_first_alloc * -1)
)
create_leave_ledger_entry(self, args, submit)
if leaves_in_second_alloc:
args.update(
dict(from_date=second_alloc_start, to_date=self.to_date, leaves=leaves_in_second_alloc * -1)
)
create_leave_ledger_entry(self, args, submit)
