def get_expected_time_for(parameter, service_level, start_date_time):
current_date_time = start_date_time
expected_time = current_date_time
start_time = None
end_time = None
if parameter == "response":
allotted_seconds = service_level.get("response_time")
elif parameter == "resolution":
allotted_seconds = service_level.get("resolution_time")
else:
frappe.throw(_("{0} parameter is invalid").format(parameter))
expected_time_is_set = 0
support_days = {}
for service in service_level.get("support_and_resolution"):
support_days[service.workday] = frappe._dict({
"start_time":
service.start_time,
"end_time":
service.end_time,
})
holidays = get_holidays(service_level.get("holiday_list"))
weekdays = get_weekdays()
while not expected_time_is_set:
current_weekday = weekdays[current_date_time.weekday()]
if not is_holiday(current_date_time,
holidays) and current_weekday in support_days:
start_time = current_date_time - datetime(current_date_time.year, current_date_time.month, current_date_time.day) \
if getdate(current_date_time) == getdate(start_date_time) and get_time_in_timedelta(current_date_time.time()) > support_days[current_weekday].start_time \
else support_days[current_weekday].start_time
end_time = support_days[current_weekday].end_time
time_left_today = time_diff_in_seconds(end_time, start_time)
# no time left for support today
if time_left_today <= 0: pass
elif allotted_seconds:
if time_left_today >= allotted_seconds:
expected_time = datetime.combine(
getdate(current_date_time), get_time(start_time))
expected_time = add_to_date(expected_time,
seconds=allotted_seconds)
expected_time_is_set = 1
else:
allotted_seconds = allotted_seconds - time_left_today
if not expected_time_is_set:
current_date_time = add_to_date(current_date_time, days=1)
if end_time and allotted_seconds >= 86400:
current_date_time = datetime.combine(getdate(current_date_time),
get_time(end_time))
else:
current_date_time = expected_time
return current_date_time
def test_skip_auto_attendance_for_duplicate_record(self):
# Skip auto attendance in case of duplicate attendance record
from erpnext.hr.doctype.attendance.attendance import mark_attendance
from erpnext.hr.doctype.employee_checkin.test_employee_checkin import make_checkin
employee = make_employee("*****@*****.**", company="_Test Company")
shift_type = setup_shift_type()
date = getdate()
# mark attendance
mark_attendance(employee, date, "Present")
make_shift_assignment(shift_type.name, employee, date)
timestamp = datetime.combine(date, get_time("08:00:00"))
log_in = make_checkin(employee, timestamp)
self.assertEqual(log_in.shift, shift_type.name)
timestamp = datetime.combine(date, get_time("12:00:00"))
log_out = make_checkin(employee, timestamp)
self.assertEqual(log_out.shift, shift_type.name)
# auto attendance should skip marking
shift_type.process_auto_attendance()
log_in.reload()
log_out.reload()
self.assertEqual(log_in.skip_auto_attendance, 1)
self.assertEqual(log_out.skip_auto_attendance, 1)
def test_working_hours_threshold_for_absent_and_half_day_2(self):
# considers absent over half day
from erpnext.hr.doctype.employee_checkin.test_employee_checkin import make_checkin
employee = make_employee("*****@*****.**", company="_Test Company")
shift_type = setup_shift_type(
shift_type="Half Day + Absent Test",
working_hours_threshold_for_half_day=1,
working_hours_threshold_for_absent=2,
)
date = getdate()
make_shift_assignment(shift_type.name, employee, date)
timestamp = datetime.combine(date, get_time("08:00:00"))
log_in = make_checkin(employee, timestamp)
self.assertEqual(log_in.shift, shift_type.name)
timestamp = datetime.combine(date, get_time("09:30:00"))
log_out = make_checkin(employee, timestamp)
self.assertEqual(log_out.shift, shift_type.name)
shift_type.process_auto_attendance()
attendance = frappe.db.get_value("Attendance", {"shift": shift_type.name}, "status")
self.assertEqual(attendance, "Absent")
def add_data(w, args,doctype=None):
dates = get_dates(args)
employees = get_active_employees()
if doctype=='Attendance':
existing_attendance_records = get_existing_attendance_records(args)
if doctype=='Departure':
existing_attendance_records = get_existing_depature_records(args)
for date in dates:
for employee in employees:
existing_attendance = {}
if existing_attendance_records \
and tuple([date, employee.name]) in existing_attendance_records:
existing_attendance = existing_attendance_records[tuple([date, employee.name])]
if doctype=='Attendance':
row = [
existing_attendance and existing_attendance.name or "",
employee.name, employee.employee_name, date,
existing_attendance and existing_attendance.attendance_time or get_time(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(doctype),
]
if doctype=='Departure':
row = [
existing_attendance and existing_attendance.name or "",
employee.name, employee.employee_name, date,
existing_attendance and existing_attendance.departure_time or get_time(date),
existing_attendance and existing_attendance.status or "",
"", employee.company,
existing_attendance and existing_attendance.naming_series or get_naming_series(doctype),
]
w.writerow(row)
return w
def test_entry_and_exit_grace(self):
from erpnext.hr.doctype.employee_checkin.test_employee_checkin import make_checkin
employee = make_employee("*****@*****.**", company="_Test Company")
# doesn't mark late entry until 60 mins after shift start i.e. till 9
# doesn't mark late entry until 60 mins before shift end i.e. 11
shift_type = setup_shift_type(
enable_entry_grace_period=1,
enable_exit_grace_period=1,
late_entry_grace_period=60,
early_exit_grace_period=60,
)
date = getdate()
make_shift_assignment(shift_type.name, employee, date)
timestamp = datetime.combine(date, get_time("09:30:00"))
log_in = make_checkin(employee, timestamp)
self.assertEqual(log_in.shift, shift_type.name)
timestamp = datetime.combine(date, get_time("10:30:00"))
log_out = make_checkin(employee, timestamp)
self.assertEqual(log_out.shift, shift_type.name)
shift_type.process_auto_attendance()
attendance = frappe.db.get_value(
"Attendance",
{"shift": shift_type.name},
["status", "name", "late_entry", "early_exit"],
as_dict=True,
)
self.assertEqual(attendance.status, "Present")
self.assertEqual(attendance.late_entry, 1)
self.assertEqual(attendance.early_exit, 1)
def test_fetch_shift(self):
employee = make_employee("*****@*****.**",
company="_Test Company")
# shift setup for 8-12
shift_type = setup_shift_type()
date = getdate()
make_shift_assignment(shift_type.name, employee, date)
# within shift time
timestamp = datetime.combine(date, get_time("08:45:00"))
log = make_checkin(employee, timestamp)
self.assertEqual(log.shift, shift_type.name)
# "begin checkin before shift time" = 60 mins, so should work for 7:00:00
timestamp = datetime.combine(date, get_time("07:00:00"))
log = make_checkin(employee, timestamp)
self.assertEqual(log.shift, shift_type.name)
# "allow checkout after shift end time" = 60 mins, so should work for 13:00:00
timestamp = datetime.combine(date, get_time("13:00:00"))
log = make_checkin(employee, timestamp)
self.assertEqual(log.shift, shift_type.name)
# should not fetch this shift beyond allowed time
timestamp = datetime.combine(date, get_time("13:01:00"))
log = make_checkin(employee, timestamp)
self.assertIsNone(log.shift)
def create_calendar_events(self):
if self.school_event:
return
default_color = frappe.get_single("Education Settings")
meeting_event = frappe.get_doc({
"doctype":
"School Event",
"owner":
self.meeting_organizer,
"subject":
self.meeting_name,
"starts_on":
datetime.datetime.combine(getdate(self.date),
get_time(self.from_time)),
"ends_on":
datetime.datetime.combine(getdate(self.date),
get_time(self.to_time)),
"status":
"Open",
"event_category":
"Meeting",
"event_type":
"Private",
"color":
default_color.default_calendar_meeting_color,
"participants": [dict(participant=self.meeting_organizer)]
})
for attendee in self.attendees:
meeting_event.append("participants",
{"participant": attendee.attendee})
meeting_event.insert(ignore_permissions=True)
self.school_event = meeting_event.name
self.save(ignore_permissions=True)
def make_course_schedule(self, date):
course_schedule = frappe.get_doc({
"doctype":
"School Event",
"subject":
self.student_group.split("/")[0],
"event_category":
"Course",
"event_type":
"Private",
"room":
self.room,
"color":
self.color,
"starts_on":
datetime.datetime.combine(getdate(date), get_time(self.from_time)),
"ends_on":
datetime.datetime.combine(getdate(date), get_time(self.to_time)),
"reference_type":
"Student Group",
"reference_name":
self.student_group,
})
for instructor in self.instructors:
inst = frappe.get_doc("Instructor", instructor.instructor)
course_schedule.append("participants",
{"participant": inst.user_id})
for student in self.students:
stud = frappe.get_doc("Student", student.student)
course_schedule.append("participants",
{"participant": stud.student_email})
return course_schedule
def validate_inout(self):
if ((self.time_in and self.time_out) and (get_time(self.time_in) > get_time(self.time_out))):
frappe.throw(_("'Time In' ({0}) cannot be greater than 'Time Out' ({1})").format(self.time_in,
self.time_out))
if ((not self.time_out or not self.time_in) and self.status in ('Present', 'Half Day')):
frappe.throw(_("Please enter 'Time In' and 'Time Out'"))
def test_fetch_shift_for_assignment_with_end_date(self):
employee = make_employee("*****@*****.**",
company="_Test Company")
# shift setup for 8-12
shift1 = setup_shift_type()
# 12:30 - 16:30
shift2 = setup_shift_type(shift_type="Shift 2",
start_time="12:30:00",
end_time="16:30:00")
date = getdate()
make_shift_assignment(shift1.name, employee, date, add_days(date, 15))
make_shift_assignment(shift2.name, employee, date, add_days(date, 15))
timestamp = datetime.combine(date, get_time("08:45:00"))
log = make_checkin(employee, timestamp)
self.assertEqual(log.shift, shift1.name)
timestamp = datetime.combine(date, get_time("12:45:00"))
log = make_checkin(employee, timestamp)
self.assertEqual(log.shift, shift2.name)
# log after end date
timestamp = datetime.combine(add_days(date, 16), get_time("12:45:00"))
log = make_checkin(employee, timestamp)
self.assertIsNone(log.shift)
def _check_availability(item, date, duration, quotation=None, uom=None):
date = getdate(date)
day = calendar.day_name[date.weekday()]
schedule = get_item_calendar(item.name, uom)
availability = []
schedules = []
if schedule:
schedule_for_the_day = filter(lambda x: x.day == day, schedule)
for line in schedule_for_the_day:
start = now_datetime()
if datetime.datetime.combine(date, get_time(
line.end_time)) > start:
if datetime.datetime.combine(date, get_time(
line.start_time)) > start:
start = datetime.datetime.combine(
date, get_time(line.start_time))
schedules.append({
"start":
start,
"end":
datetime.datetime.combine(date, get_time(line.end_time)),
"duration":
datetime.timedelta(minutes=cint(duration))
})
if schedules:
availability.extend(
_get_availability_from_schedule(item, schedules, date,
quotation))
return availability
def validate(self):
if not self.booked_tables:
for bt in get_vacant_tables(self):
self.append(
"booked_tables", {
"doctype": "Applicable Table Config",
"table_config": bt.name,
"table_name": bt.table_name,
"capacity": bt.capacity,
"preferred_area": bt.preferred_area
})
for bt in self.booked_tables:
bt.validate()
if get_time(self.reservation_start_time) > get_time(
self.reservation_end_time):
frappe.throw(
"Reservation Start Time cannot be greater than Reservation End Time"
)
start_time = get_time(self.reservation_start_time).replace(second=0)
end_time = get_time(self.reservation_end_time).replace(second=0)
if start_time.minute % 30 or end_time.minute % 30:
frappe.throw(
"Reservation Start Time and Reservation End Time of Table Booking Days should be a multiple of 30 minutes"
)
self.reservation_start_time = cstr(start_time)
self.reservation_end_time = cstr(end_time)
def test_skip_auto_attendance_for_overlapping_shift(self):
# Skip auto attendance in case of overlapping shift attendance record
# this case won't occur in case of shift assignment, since it will not allow overlapping shifts to be assigned
# can happen if manual attendance records are created
from erpnext.hr.doctype.attendance.attendance import mark_attendance
from erpnext.hr.doctype.employee_checkin.test_employee_checkin import make_checkin
employee = make_employee("*****@*****.**", company="_Test Company")
shift_1 = setup_shift_type(shift_type="Shift 1", start_time="08:00:00", end_time="10:00:00")
shift_2 = setup_shift_type(shift_type="Shift 2", start_time="09:30:00", end_time="11:00:00")
date = getdate()
# mark attendance
mark_attendance(employee, date, "Present", shift=shift_1.name)
make_shift_assignment(shift_2.name, employee, date)
timestamp = datetime.combine(date, get_time("09:30:00"))
log_in = make_checkin(employee, timestamp)
self.assertEqual(log_in.shift, shift_2.name)
timestamp = datetime.combine(date, get_time("11:00:00"))
log_out = make_checkin(employee, timestamp)
self.assertEqual(log_out.shift, shift_2.name)
# auto attendance should be skipped for shift 2
# since it is already marked for overlapping shift 1
shift_2.process_auto_attendance()
log_in.reload()
log_out.reload()
self.assertEqual(log_in.skip_auto_attendance, 1)
self.assertEqual(log_out.skip_auto_attendance, 1)
def create_attendance(employee=None,
employee_name='',
attendance_date='',
in_store='',
out_store='',
in_time='',
out_time='',
company='',
new_in_time='',
new_out_time='',
status1='',
status2='',
total_working_hours='',
ot_hours='',
schedule_store='',
schedule_status='',
schedule_time=''):
attendance_doc = frappe.new_doc("Attendance")
if in_time == "":
in_time = "00:00"
if out_time == "":
out_time = "00:00"
if new_in_time == "":
new_in_time = "00:00"
if new_out_time == "":
new_out_time = "00:00"
attendance_doc.employee = employee
attendance_doc.employee_name = employee_name
attendance_doc.attendance_date = attendance_date
attendance_doc.in_store = in_store
attendance_doc.out_store = out_store
attendance_doc.in_time = get_time(in_time)
attendance_doc.out_time = get_time(out_time)
attendance_doc.company = company
attendance_doc.new_in_time = get_time(new_in_time)
attendance_doc.new_out_time = get_time(new_out_time)
attendance_doc.status1 = status1
attendance_doc.status2 = status2
attendance_doc.total_working_hours = total_working_hours
attendance_doc.ot_hours = ot_hours
attendance_doc.schedule_store = schedule_store
attendance_doc.schedule_status = schedule_status
attendance_doc.schedule_time = schedule_time
try:
attendance_doc.insert(ignore_permissions=True)
attendance_doc.save(ignore_permissions=True)
attendance_doc.submit()
frappe.db.commit()
return {
"status": "success",
"message": "New Attendance {0} Is Created".format(employee)
}
except Exception as e:
frappe.local.message_log = False
return {
"status": "failed",
"message": "New Attendance Is Not Created"
}
def hourly_reminder():
fields = ["from_time", "to_time"]
projects = get_projects_for_collect_progress("Hourly", fields)
for project in projects:
if (get_time(nowtime()) >= get_time(project.from_time)
or get_time(nowtime()) <= get_time(project.to_time)):
send_project_update_email_to_users(project.name)
def hourly_reminder():
fields = ["from_time", "to_time"]
projects = get_projects_for_collect_progress("Hourly", fields)
for project in projects:
if (get_time(nowtime()) >= get_time(project.from_time) or
get_time(nowtime()) <= get_time(project.to_time)):
send_project_update_email_to_users(project.name)
def check_availability(doctype, df, token, dt, dn, date):
# params doctype: doc to schedule,
#df: doctype relation(O2M) field name to resource,
#token: boolean, token generated or not,
#dt: resource doctype,
#dn: resource docname,
#date: date to check availability
if not token:
frappe.msgprint("Implementation Pending")
return
resource = frappe.get_doc(dt, dn)
date = getdate(date)
day = calendar.day_name[date.weekday()]
is_available_on_date = False
availability = []
if resource.schedule:
for line in resource.schedule:
if (line.day == day):
is_available_on_date = True
start = datetime.datetime.combine(date, get_time(line.start))
end = datetime.datetime.combine(date, get_time(line.end))
if token:
scheduled = frappe.db.sql(
"""select token, end_dt from `tab{0}` where {1}='{2}' and start_dt between '{3}' and '{4}' order by token desc"""
.format(doctype, df, dn, start, end))
if (len(scheduled) < line.limit):
if (len(scheduled) > 0):
token = scheduled[0][0] + 1
time = scheduled[0][1]
else:
token = 1
time = datetime.datetime.combine(
date, get_time(line.start))
#calc endtime
duration = get_time(line.average)
end_time = time + datetime.timedelta(
hours=duration.hour,
minutes=duration.minute,
seconds=duration.second)
availability.append({
"start": time,
"end": end_time,
"token": token
})
if not is_available_on_date:
availability.append(
{"msg": _("{0} not available on {1}").format(dn, date)})
else:
#resource is available 24/7, schedule for given time, validate overlaps
availability.append({
"msg":
_("No schedule for selected {0}. Please set consultation schedule for {1}"
).format(dt, dn)
})
return availability
def check_workstation_time(self, row):
workstation_doc = frappe.get_cached_doc("Workstation", self.workstation)
if not workstation_doc.working_hours or cint(
frappe.db.get_single_value("Manufacturing Settings", "allow_overtime")
):
if get_datetime(row.planned_end_time) < get_datetime(row.planned_start_time):
row.planned_end_time = add_to_date(row.planned_start_time, minutes=row.time_in_mins)
row.remaining_time_in_mins = 0.0
else:
row.remaining_time_in_mins -= time_diff_in_minutes(
row.planned_end_time, row.planned_start_time
)
self.update_time_logs(row)
return
start_date = getdate(row.planned_start_time)
start_time = get_time(row.planned_start_time)
new_start_date = workstation_doc.validate_workstation_holiday(start_date)
if new_start_date != start_date:
row.planned_start_time = datetime.datetime.combine(new_start_date, start_time)
start_date = new_start_date
total_idx = len(workstation_doc.working_hours)
for i, time_slot in enumerate(workstation_doc.working_hours):
workstation_start_time = datetime.datetime.combine(start_date, get_time(time_slot.start_time))
workstation_end_time = datetime.datetime.combine(start_date, get_time(time_slot.end_time))
if (
get_datetime(row.planned_start_time) >= workstation_start_time
and get_datetime(row.planned_start_time) <= workstation_end_time
):
time_in_mins = time_diff_in_minutes(workstation_end_time, row.planned_start_time)
# If remaining time fit in workstation time logs else split hours as per workstation time
if time_in_mins > row.remaining_time_in_mins:
row.planned_end_time = add_to_date(row.planned_start_time, minutes=row.remaining_time_in_mins)
row.remaining_time_in_mins = 0
else:
row.planned_end_time = add_to_date(row.planned_start_time, minutes=time_in_mins)
row.remaining_time_in_mins -= time_in_mins
self.update_time_logs(row)
if total_idx != (i + 1) and row.remaining_time_in_mins > 0:
row.planned_start_time = datetime.datetime.combine(
start_date, get_time(workstation_doc.working_hours[i + 1].start_time)
)
if row.remaining_time_in_mins > 0:
start_date = add_days(start_date, 1)
row.planned_start_time = datetime.datetime.combine(
start_date, get_time(workstation_doc.working_hours[0].start_time)
)
def is_slot_vacant(start_t, end_t, tb_list): for tb in tb_list: tb_start_time = get_time(cstr(tb.reservation_start_time)) tb_end_time = get_time(cstr(tb.reservation_end_time)) if ((start_t >= tb_start_time and start_t < tb_end_time) or (end_t > tb_start_time and end_t <= tb_end_time) or (start_t == tb_start_time and end_t == tb_end_time)): return False return True
def validate_inout(self):
if ((self.time_in and self.time_out)
and (get_time(self.time_in) > get_time(self.time_out))):
frappe.throw(
_("'Time In' ({0}) cannot be greater than 'Time Out' ({1})").
format(self.time_in, self.time_out))
if ((not self.time_out or not self.time_in)
and self.status in ('Present', 'Half Day')):
frappe.throw(_("Please enter 'Time In' and 'Time Out'"))
def is_slot_vacant(start_t, end_t, tb_list):
for tb in tb_list:
tb_start_time = get_time(cstr(tb.reservation_start_time))
tb_end_time = get_time(cstr(tb.reservation_end_time))
if ((start_t >= tb_start_time and start_t < tb_end_time)
or (end_t > tb_start_time and end_t <= tb_end_time)
or
(start_t == tb_start_time and end_t == tb_end_time)):
return False
return True
def allow_to_make_project_update(project, time, frequency):
data = frappe.db.sql(""" SELECT name from `tabProject Update`
WHERE project = %s and date = %s """, (project, today()))
# len(data) > 1 condition is checked for twicely frequency
if data and (frequency in ['Daily', 'Weekly'] or len(data) > 1):
return False
if get_time(nowtime()) >= get_time(time):
return True
def validate_inout(self):
if (get_time(self.time_in) > get_time(self.time_out)):
frappe.throw(_("'Time In' ({0}) cannot be greater than 'Time Out' ({1})").format(self.time_in,
self.time_out))
if (self.time_out and not self.time_in ):
frappe.throw(_("Please enter 'Time In' as you have entered 'Time Out'"))
if (self.time_in and not self.time_out ):
frappe.throw(_("Please enter 'Time Out' as you have entered 'Time In'"))
def set_status(self):
endtime = datetime.datetime.combine(getdate(self.date),
get_time(self.to_time))
starttime = datetime.datetime.combine(getdate(self.date),
get_time(self.from_time))
if get_datetime(endtime) < get_datetime(now_datetime()):
self.status = "Completed"
elif get_datetime(starttime) <= get_datetime(
now_datetime()) <= get_datetime(endtime):
self.status = "In Progress"
def get_shift_details(shift_type_name: str, for_timestamp: datetime = None) -> Dict:
"""Returns a Dict containing shift details with the following data:
'shift_type' - Object of DocType Shift Type,
'start_datetime' - datetime of shift start on given timestamp,
'end_datetime' - datetime of shift end on given timestamp,
'actual_start' - datetime of shift start after adding 'begin_check_in_before_shift_start_time',
'actual_end' - datetime of shift end after adding 'allow_check_out_after_shift_end_time' (None is returned if this is zero)
:param shift_type_name (str): shift type name for which shift_details are required.
:param for_timestamp (datetime, optional): Datetime value of checkin, if not provided considers current datetime
"""
if not shift_type_name:
return {}
if for_timestamp is None:
for_timestamp = now_datetime()
shift_type = frappe.get_doc("Shift Type", shift_type_name)
shift_actual_start = shift_type.start_time - timedelta(
minutes=shift_type.begin_check_in_before_shift_start_time
)
if shift_type.start_time > shift_type.end_time:
# shift spans accross 2 different days
if get_time(for_timestamp.time()) >= get_time(shift_actual_start):
# if for_timestamp is greater than start time, it's within the first day
start_datetime = datetime.combine(for_timestamp, datetime.min.time()) + shift_type.start_time
for_timestamp = for_timestamp + timedelta(days=1)
end_datetime = datetime.combine(for_timestamp, datetime.min.time()) + shift_type.end_time
elif get_time(for_timestamp.time()) < get_time(shift_actual_start):
# if for_timestamp is less than start time, it's within the second day
end_datetime = datetime.combine(for_timestamp, datetime.min.time()) + shift_type.end_time
for_timestamp = for_timestamp + timedelta(days=-1)
start_datetime = datetime.combine(for_timestamp, datetime.min.time()) + shift_type.start_time
else:
# start and end timings fall on the same day
start_datetime = datetime.combine(for_timestamp, datetime.min.time()) + shift_type.start_time
end_datetime = datetime.combine(for_timestamp, datetime.min.time()) + shift_type.end_time
actual_start = start_datetime - timedelta(
minutes=shift_type.begin_check_in_before_shift_start_time
)
actual_end = end_datetime + timedelta(minutes=shift_type.allow_check_out_after_shift_end_time)
return frappe._dict(
{
"shift_type": shift_type,
"start_datetime": start_datetime,
"end_datetime": end_datetime,
"actual_start": actual_start,
"actual_end": actual_end,
}
)
def check_time_and_get_machine(machine_name):
now_time = datetime.datetime.now().time()
today_date = datetime.date.today()
for m_name in machine_names:
minute_diff = get_time_difference_in_minutes(get_time(now_time), get_time(m_name["import_at"]))
if machine_name == m_name["name"]:
if (cint(m_name["import_enabled"]) and m_name["last_import_on"] != today_date \
and abs(minute_diff) <=50 and m_name["retries"] <= 3):
return frappe.get_doc("Biometric Machine", m_name["name"])
return None
def validate(self):
self.docstatus = 1
departure_clock_closing = frappe.db.get_value(
"HR Settings", None, "departure_clock_closing")
if departure_clock_closing and get_time(
self.departure_time) >= get_time(departure_clock_closing):
frappe.throw(
_("Not Allowed! Footprint Clock is closed, your departure should be before {0}"
.format(departure_clock_closing)))
self.validate_duplicate_record()
self.validate_attendance_date()
def validate_inout(self):
if (get_time(self.time_in) > get_time(self.time_out)):
frappe.throw(
_("'Time In' ({0}) cannot be greater than 'Time Out' ({1})").
format(self.time_in, self.time_out))
if (self.time_out and not self.time_in):
frappe.throw(
_("Please enter 'Time In' as you have entered 'Time Out'"))
if (self.time_in and not self.time_out):
frappe.throw(
_("Please enter 'Time Out' as you have entered 'Time In'"))
def get_expected_time_for(parameter, service_level, start_date_time):
current_date_time = start_date_time
expected_time = current_date_time
start_time = end_time = None
expected_time_is_set = 0
allotted_seconds = get_allotted_seconds(parameter, service_level)
support_days = get_support_days(service_level)
holidays = get_holidays(service_level.get("holiday_list"))
weekdays = get_weekdays()
while not expected_time_is_set:
current_weekday = weekdays[current_date_time.weekday()]
if not is_holiday(current_date_time,
holidays) and current_weekday in support_days:
if (getdate(current_date_time) == getdate(start_date_time)
and get_time_in_timedelta(current_date_time.time()) >
support_days[current_weekday].start_time):
start_time = current_date_time - datetime(
current_date_time.year, current_date_time.month,
current_date_time.day)
else:
start_time = support_days[current_weekday].start_time
end_time = support_days[current_weekday].end_time
time_left_today = time_diff_in_seconds(end_time, start_time)
# no time left for support today
if time_left_today <= 0:
pass
elif allotted_seconds:
if time_left_today >= allotted_seconds:
expected_time = datetime.combine(
getdate(current_date_time), get_time(start_time))
expected_time = add_to_date(expected_time,
seconds=allotted_seconds)
expected_time_is_set = 1
else:
allotted_seconds = allotted_seconds - time_left_today
if not expected_time_is_set:
current_date_time = add_to_date(current_date_time, days=1)
if end_time and allotted_seconds >= 86400:
current_date_time = datetime.combine(getdate(current_date_time),
get_time(end_time))
else:
current_date_time = expected_time
return current_date_time
def validate_posting_time(self):
# set Edit Posting Date and Time to 1 while data import
if frappe.flags.in_import and self.posting_date:
self.set_posting_time = 1
if not getattr(self, "set_posting_time", None):
now = now_datetime()
self.posting_date = now.strftime("%Y-%m-%d")
self.posting_time = now.strftime("%H:%M:%S.%f")
elif self.posting_time:
try:
get_time(self.posting_time)
except ValueError:
frappe.throw(_("Invalid Posting Time"))
def validate_posting_time(self):
# set Edit Posting Date and Time to 1 while data import
if frappe.flags.in_import and self.posting_date:
self.set_posting_time = 1
if not getattr(self, 'set_posting_time', None):
now = now_datetime()
self.posting_date = now.strftime('%Y-%m-%d')
self.posting_time = now.strftime('%H:%M:%S.%f')
elif self.posting_time:
try:
get_time(self.posting_time)
except ValueError:
frappe.throw(_('Invalid Posting Time'))
def validate_delivery_window(doc, method):
from erpnext.stock.doctype.delivery_trip.delivery_trip import get_delivery_window
if not frappe.db.get_single_value("Delivery Settings", "send_delivery_window_warning"):
return
if not (doc.get("delivery_start_time") and doc.get("delivery_end_time")):
return
if not doc.get("customer"):
return
delivery_window = get_delivery_window(customer=doc.get("customer"))
delivery_start_time = delivery_window.delivery_start_time
delivery_end_time = delivery_window.delivery_end_time
if not (delivery_start_time and delivery_end_time):
return
if to_timedelta(doc.delivery_start_time) < to_timedelta(delivery_start_time) \
or to_timedelta(doc.delivery_end_time) > to_timedelta(delivery_end_time):
if method == "validate":
frappe.msgprint(_("The delivery window is set outside the customer's default timings"))
elif method == "on_submit":
# send an email notifying users that the document is outside the customer's delivery window
role_profiles = ["Fulfillment Manager"]
role_profile_users = frappe.get_all("User", filters={"role_profile_name": ["IN", role_profiles]}, fields=["email"])
role_profile_users = [user.email for user in role_profile_users]
accounts_managers = get_users_with_role("Accounts Manager")
system_managers = get_users_with_role("System Manager")
recipients = list(set(role_profile_users + accounts_managers) - set(system_managers))
if not recipients:
return
# form the email
subject = _("[Info] An order may be delivered outside a customer's preferred delivery window")
message = _("""An order ({name}) has the following delivery window: {doc_start} - {doc_end}<br><br>
While the default delivery window is {customer_start} - {customer_end}""".format(
name=frappe.utils.get_link_to_form(doc.doctype, doc.name),
doc_start=get_time(doc.delivery_start_time).strftime("%I:%M %p"),
doc_end=get_time(doc.delivery_end_time).strftime("%I:%M %p"),
customer_start=get_time(delivery_start_time).strftime("%I:%M %p"),
customer_end=get_time(delivery_end_time).strftime("%I:%M %p"),
))
frappe.sendmail(recipients=recipients, subject=subject, message=message)
def validate_posting_time(self):
# set Edit Posting Date and Time to 1 while data import
if frappe.flags.in_import and self.posting_date:
self.set_posting_time = 1
if not getattr(self, 'set_posting_time', None):
now = now_datetime()
self.posting_date = now.strftime('%Y-%m-%d')
self.posting_time = now.strftime('%H:%M:%S.%f')
elif self.posting_time:
try:
get_time(self.posting_time)
except ValueError:
frappe.throw(_('Invalid Posting Time'))
self.validate_with_last_transaction_posting_time()
def validate_duplicate(self):
end_time = datetime.datetime.combine(getdate(self.start_date), get_time(self.start_time)) \
+ datetime.timedelta(minutes=flt(self.duration))
overlaps = frappe.db.sql(
"""
select
name
from
`tabTherapy Session`
where
start_date=%s and name!=%s and docstatus!=2
and (practitioner=%s or patient=%s) and
((start_time<%s and start_time + INTERVAL duration MINUTE>%s) or
(start_time>%s and start_time<%s) or
(start_time=%s))
""", (self.start_date, self.name, self.practitioner, self.patient,
self.start_time, end_time.time(), self.start_time,
end_time.time(), self.start_time))
if overlaps:
overlapping_details = _(
'Therapy Session overlaps with {0}').format(
get_link_to_form('Therapy Session', overlaps[0][0]))
frappe.throw(overlapping_details,
title=_('Therapy Sessions Overlapping'))
def check_attendance_monthly(mydate=None):
#date, hour
#now = now_datetime()
#get_datetime(date)
#month = frappe.db.get_value("HR Settings", None, "month")
if not mydate:
mydate = now_datetime()
#hour = frappe.db.get_value("HR Settings", None, "hour")
#if not hour: hour =18
#month_range= monthrange(cint(getdate(mydate).year), int(getdate(mydate).month))[1]
month_range = abs(date_diff(add_months(datetime.today(), -1), mydate))
for d in range(month_range):
try:
day = str(getdate(mydate).year) + '-' + str(
getdate(mydate).month) + '-' + str(
d + 1) #frappe.utils.add_days(datetime.today(), -d)
hour = get_time(mydate).hour
#print day
if getdate(day) < getdate(now_datetime()):
check_attendance(day, hour, True)
except ValueError:
continue
frappe.msgprint(_("Absent Records Added"))
def build_filter_conditions(self, filters, conditions):
"""build conditions from user filters"""
if isinstance(filters, dict):
filters = [filters]
for f in filters:
if isinstance(f, basestring):
conditions.append(f)
else:
f = self.get_filter_tuple(f)
tname = ('`tab' + f[0] + '`')
if not tname in self.tables:
self.append_table(tname)
# prepare in condition
if f[2] in ['in', 'not in']:
opts = f[3]
if not isinstance(opts, (list, tuple)):
opts = f[3].split(",")
opts = [frappe.db.escape(t.strip()) for t in opts]
f[3] = '("{0}")'.format('", "'.join(opts))
conditions.append('ifnull({tname}.{fname}, "") {operator} {value}'.format(
tname=tname, fname=f[1], operator=f[2], value=f[3]))
else:
df = frappe.get_meta(f[0]).get("fields", {"fieldname": f[1]})
df = df[0] if df else None
if df and df.fieldtype=="Date":
value, default_val = '"{0}"'.format(frappe.db.escape(getdate(f[3]).strftime("%Y-%m-%d"))), \
"'0000-00-00'"
elif df and df.fieldtype=="Datetime":
value, default_val = '"{0}"'.format(frappe.db.escape(get_datetime(f[3]).strftime("%Y-%m-%d %H:%M:%S.%f"))), \
"'0000-00-00 00:00:00'"
elif df and df.fieldtype=="Time":
value, default_val = '"{0}"'.format(frappe.db.escape(get_time(f[3]).strftime("%H:%M:%S.%f"))), \
"'00:00:00'"
elif f[2] == "like" or (isinstance(f[3], basestring) and
(not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])):
if f[2] == "like":
# because "like" uses backslash (\) for escaping
f[3] = f[3].replace("\\", "\\\\")
value, default_val = '"{0}"'.format(frappe.db.escape(f[3])), '""'
else:
value, default_val = flt(f[3]), 0
conditions.append('ifnull({tname}.{fname}, {default_val}) {operator} {value}'.format(
tname=tname, fname=f[1], default_val=default_val, operator=f[2],
value=value))
def validate(self):
self.table_name = cstr(self.table_name).strip()
if cint(self.capacity) < 0:
frappe.msgprint("Table capacity cannot be less than zero")
self.capacity = 0
# validate timings
for c in cstr(self.days_of_week):
if c not in " ,1234567":
frappe.throw("Days of week can be in range between 1 and 7")
start_time = get_time(self.start_time).replace(second=0)
end_time = get_time(self.end_time).replace(second=0)
if start_time.minute % 30 or end_time.minute % 30:
frappe.throw("Start Time and End Time of Table Booking Days should be a multiple of 30 minutes")
self.start_time = cstr(start_time)
self.end_time = cstr(end_time)
if not self.days_of_week:
frappe.throw("Days Of Week should be specified")
def get_events(start, end, filters=None):
"""Returns events for Gantt / Calendar view rendering.
:param start: Start date-time.
:param end: End date-time.
:param filters: Filters like workstation, project etc.
"""
from frappe.desk.calendar import get_event_conditions
conditions = get_event_conditions("Time Log", filters)
if (cint(get_defaults("fs_simplified_time_log"))):
date_cond = "date_worked between %(start)s and %(end)s"
else:
date_cond = "( from_time between %(start)s and %(end)s or to_time between %(start)s and %(end)s )"
data = frappe.db.sql("""select name, from_time, to_time,
activity_type, task, project, production_order, workstation, date_worked, employee, hours from `tabTime Log`
where docstatus < 2 and {date_cond}
{conditions}""".format(conditions=conditions,date_cond=date_cond), {
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})
#aligns the assorted time logs so they are layed out sequentially
if(cint(get_defaults("fs_simplified_time_log"))):
slist = {}
for idx,da in enumerate(data):
if (da.employee not in slist):
slist[da.employee]={}
if (da.date_worked not in slist[da.employee]):
slist[da.employee][da.date_worked]=[]
slist[da.employee][da.date_worked].append([idx,da.from_time,da.to_time,da.hours])
for e in slist:
for d in slist[e]:
temp = slist[e][d][0]
temp[1]= datetime.combine(d,get_time("8:00:00"))
temp[2]= temp[1] + timedelta(hours=temp[3])
for idx,l in enumerate(slist[e][d][1:]):
data[l[0]]["from_time"]= l[1] = slist[e][d][idx][2]
data[l[0]]["to_time"] = l[2] = l[1]+ timedelta(hours=l[3])
l= slist[e][d][0]
data[temp[0]]["from_time"]= slist[e][d][0][1]
data[temp[0]]["to_time"] = slist[e][d][0][2]
for d in data:
d.title = d.name + ": " + (d.activity_type or d.production_order or "")
if d.task:
d.title += " for Task: " + d.task
if d.project:
d.title += " for Project: " + d.project
return data
def move_to_next_working_slot(self):
"""Move to next working slot from workstation"""
workstation = frappe.get_doc("Workstation", self.workstation)
slot_found = False
for working_hour in workstation.working_hours:
if get_datetime(self.from_time).time() < get_time(working_hour.start_time):
self.from_time = getdate(self.from_time).strftime("%Y-%m-%d") + " " + working_hour.start_time
slot_found = True
break
if not slot_found:
# later than last time
self.from_time = getdate(self.from_time).strftime("%Y-%m-%d") + " " + workstation.working_hours[0].start_time
self.move_to_next_day()
def validate(self):
if not self.booked_tables:
for bt in get_vacant_tables(self):
self.append("booked_tables", {
"doctype": "Applicable Table Config",
"table_config": bt.name,
"table_name": bt.table_name,
"capacity": bt.capacity,
"preferred_area": bt.preferred_area
})
for bt in self.booked_tables:
bt.validate()
if get_time(self.reservation_start_time) > get_time(self.reservation_end_time):
frappe.throw("Reservation Start Time cannot be greater than Reservation End Time")
start_time = get_time(self.reservation_start_time).replace(second=0)
end_time = get_time(self.reservation_end_time).replace(second=0)
if start_time.minute % 30 or end_time.minute % 30:
frappe.throw("Reservation Start Time and Reservation End Time of Table Booking Days should be a multiple of 30 minutes")
self.reservation_start_time = cstr(start_time)
self.reservation_end_time = cstr(end_time)
def get_vacant_tables(table_booking, book_area=False):
if isinstance(table_booking, basestring):
table_booking = frappe.get_doc("Table Booking", table_booking)
start_time = get_time(cstr(table_booking.reservation_start_time))
end_time = get_time(cstr(table_booking.reservation_end_time))
weekday = getdate(cstr(table_booking.booking_date)).isoweekday()
holiday_dates = []
holiday_lists = [hl.holiday_list for hl in frappe.db.get_all("Applicable Holiday List", fields=["holiday_list"], filters={"parent": table_booking.outlet})]
for hl in holiday_lists:
holiday_dates += [getdate(cstr(h.holiday_date)) for h in frappe.db.get_all("Holiday", fields=["holiday_date"], filters={"parent": hl})]
if table_booking.preferred_area:
tables = frappe.db.sql("""select name, table_name, capacity, days_of_week, start_time, end_time
from `tabTable Config` where parent="{parent}" and preferred_area="{preferred_area}" and days_of_week like "%{weekday}%"
order by capacity desc""".format(parent=table_booking.outlet, preferred_area=table_booking.preferred_area, weekday=cstr(weekday)), as_dict=1)
else:
tables = frappe.db.sql("""select name, table_name, capacity, days_of_week, start_time, end_time
from `tabTable Config` where parent="{parent}" and days_of_week like "%{weekday}%"
order by capacity desc""".format(parent=table_booking.outlet, weekday=cstr(weekday)), as_dict=1)
no_of_people = cint(table_booking.no_of_people)
found_tables = []
for t in tables:
t_start_time = get_time(cstr(t.start_time))
t_end_time = get_time(cstr(t.end_time))
if (start_time < t_start_time or
start_time > t_end_time or
end_time > t_end_time or
getdate(cstr(table_booking.booking_date)) in holiday_dates):
continue
table_bookings = frappe.db.sql("""select name, reservation_start_time, reservation_end_time
from `tabTable Booking` where docstatus=1 and status="Approved" and booking_date=%s and outlet=%s
order by name asc""", (table_booking.booking_date, table_booking.outlet), as_dict=1)
table_bookings = [i for i in table_bookings if frappe.db.get_all("Applicable Table Config", filters={"parent": i.name, "table_config": t.name})]
for tb in table_bookings:
tb_start_time = get_time(cstr(tb.reservation_start_time))
tb_end_time = get_time(cstr(tb.reservation_end_time))
if ((start_time > tb_start_time and start_time < tb_end_time) or
(end_time > tb_start_time and end_time < tb_end_time) or
(start_time == tb_start_time and end_time == tb_end_time)):
break
else:
found_tables.append(t)
if book_area:
continue
no_of_people -= cint(t.capacity)
if no_of_people <= 0:
break
return found_tables
def validate(self):
end_time = datetime.datetime.combine(getdate(self.appointment_date), get_time(self.appointment_time)) + datetime.timedelta(minutes=float(self.duration))
overlaps = frappe.db.sql("""
select
name, practitioner, patient, appointment_time, duration
from
`tabPatient Appointment`
where
appointment_date=%s and name!=%s and status NOT IN ("Closed", "Cancelled")
and (practitioner=%s or patient=%s) and
((appointment_time<%s and appointment_time + INTERVAL duration MINUTE>%s) or
(appointment_time>%s and appointment_time<%s) or
(appointment_time=%s))
""", (self.appointment_date, self.name, self.practitioner, self.patient,
self.appointment_time, end_time.time(), self.appointment_time, end_time.time(), self.appointment_time))
if overlaps:
frappe.throw(_("""Appointment overlaps with {0}.<br> {1} has appointment scheduled
with {2} at {3} having {4} minute(s) duration.""").format(overlaps[0][0], overlaps[0][1], overlaps[0][2], overlaps[0][3], overlaps[0][4]))
def get_formated_time(time):
return babel.dates.format_time(get_time(time), format='short', locale=(frappe.local.lang or "").replace("-", "_"))
def outlet_capacity():
request = frappe.local.request
response_data = {
"success": True,
"tables": []
}
tables_data = []
if request.method == "GET":
frappe.set_user("Administrator")
import urlparse
data = frappe._dict(urlparse.parse_qsl(cstr(request.query_string)))
response_data["booking_date"] = data.booking_date
weekday = getdate(cstr(data.booking_date)).isoweekday()
holiday_dates = []
holiday_lists = [hl.holiday_list for hl in frappe.db.get_all("Applicable Holiday List", fields=["holiday_list"], filters={"parent": data.outlet})]
for hl in holiday_lists:
holiday_dates += [getdate(cstr(h.holiday_date)) for h in frappe.db.get_all("Holiday", fields=["holiday_date"], filters={"parent": hl})]
tables = frappe.db.sql("""select name, table_name, capacity, preferred_area, days_of_week, start_time, end_time
from `tabTable Config` where parent="{parent}" and days_of_week like "%{weekday}%"
order by table_name asc""".format(parent=data.outlet, weekday=cstr(weekday)), as_dict=1)
for t in tables:
t_start_time = get_time(cstr(t.start_time))
t_end_time = get_time(cstr(t.end_time))
if getdate(cstr(data.booking_date)) in holiday_dates:
continue
table_bookings = frappe.db.sql("""select name, reservation_start_time, reservation_end_time
from `tabTable Booking` where docstatus=1 and status="Approved" and booking_date=%s and outlet=%s
order by name asc""", (data.booking_date, data.outlet), as_dict=1)
table_bookings = [i for i in table_bookings if frappe.db.get_all("Applicable Table Config", filters={"parent": i.name, "table_config": t.name})]
vacant_time_slots = []
cur_t_start_time = t_start_time
while cur_t_start_time <= t_end_time:
next_t_start_time = (dt.combine(dt_date.today(), cur_t_start_time) + timedelta(minutes=30)).time()
vacant_time_slots.append([cur_t_start_time, next_t_start_time])
cur_t_start_time = next_t_start_time
def is_slot_vacant(start_t, end_t, tb_list):
for tb in tb_list:
tb_start_time = get_time(cstr(tb.reservation_start_time))
tb_end_time = get_time(cstr(tb.reservation_end_time))
if ((start_t >= tb_start_time and start_t < tb_end_time) or
(end_t > tb_start_time and end_t <= tb_end_time) or
(start_t == tb_start_time and end_t == tb_end_time)):
return False
return True
vacant_time_slots = [[cstr(v1), cstr(v2)] for v1, v2 in vacant_time_slots if is_slot_vacant(v1, v2, table_bookings)]
table_data = {
"name": t.name,
"table_name": t.table_name,
"capacity": t.capacity,
"preferred_area": t.preferred_area,
"vacant_time_slots": vacant_time_slots
}
tables_data.append(table_data)
continue
else:
return error_response("Unsupported HTTP method. Only GET method is supported.")
response_data["tables"] = tables_data
return response_data
def prepare_filter_condition(self, f):
"""Returns a filter condition in the format:
ifnull(`tabDocType`.`fieldname`, fallback) operator "value"
"""
f = self.get_filter(f)
tname = ('`tab' + f.doctype + '`')
if not tname in self.tables:
self.append_table(tname)
# prepare in condition
if f.operator in ('in', 'not in'):
values = f.value
if not isinstance(values, (list, tuple)):
values = values.split(",")
values = (frappe.db.escape(v.strip()) for v in values)
values = '("{0}")'.format('", "'.join(values))
condition = 'ifnull({tname}.{fname}, "") {operator} {value}'.format(
tname=tname, fname=f.fieldname, operator=f.operator, value=values)
else:
df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname})
df = df[0] if df else None
if df and df.fieldtype=="Date":
value = getdate(f.value).strftime("%Y-%m-%d")
fallback = "'0000-00-00'"
elif df and df.fieldtype=="Datetime":
value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f")
fallback = "'0000-00-00 00:00:00'"
elif df and df.fieldtype=="Time":
value = get_time(f.value).strftime("%H:%M:%S.%f")
fallback = "'00:00:00'"
elif f.operator == "like" or (isinstance(f.value, basestring) and
(not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])):
value = "" if f.value==None else f.value
fallback = '""'
if f.operator == "like" and isinstance(value, basestring):
# because "like" uses backslash (\) for escaping
value = value.replace("\\", "\\\\")
else:
value = flt(f.value)
fallback = 0
# put it inside double quotes
if isinstance(value, basestring):
value = '"{0}"'.format(frappe.db.escape(value))
condition = 'ifnull({tname}.{fname}, {fallback}) {operator} {value}'.format(
tname=tname, fname=f.fieldname, fallback=fallback, operator=f.operator,
value=value)
# replace % with %% to prevent python format string error
return condition.replace("%", "%%")
def prepare_filter_condition(self, f):
"""Returns a filter condition in the format:
ifnull(`tabDocType`.`fieldname`, fallback) operator "value"
"""
f = get_filter(self.doctype, f)
tname = ('`tab' + f.doctype + '`')
if not tname in self.tables:
self.append_table(tname)
column_name = '{tname}.{fname}'.format(tname=tname,
fname=f.fieldname)
can_be_null = True
# prepare in condition
if f.operator in ('in', 'not in'):
values = f.value
if not isinstance(values, (list, tuple)):
values = values.split(",")
fallback = "''"
value = (frappe.db.escape(v.strip(), percent=False) for v in values)
value = '("{0}")'.format('", "'.join(value))
else:
df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname})
df = df[0] if df else None
if df and df.fieldtype=="Check":
can_be_null = False
if df and df.fieldtype=="Date":
value = getdate(f.value).strftime("%Y-%m-%d")
fallback = "'0000-00-00'"
elif df and df.fieldtype=="Datetime":
value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f")
fallback = "'0000-00-00 00:00:00'"
elif df and df.fieldtype=="Time":
value = get_time(f.value).strftime("%H:%M:%S.%f")
fallback = "'00:00:00'"
elif f.operator in ("like", "not like") or (isinstance(f.value, basestring) and
(not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])):
value = "" if f.value==None else f.value
fallback = '""'
if f.operator in ("like", "not like") and isinstance(value, basestring):
# because "like" uses backslash (\) for escaping
value = value.replace("\\", "\\\\").replace("%", "%%")
else:
value = flt(f.value)
fallback = 0
# put it inside double quotes
if isinstance(value, basestring):
value = '"{0}"'.format(frappe.db.escape(value, percent=False))
if f.fieldname in ("creation", "modified"):
column_name = "date_format({tname}.{fname}, '%Y-%m-%d')".format(tname=tname,
fname=f.fieldname)
if self.ignore_ifnull or not can_be_null:
condition = '{column_name} {operator} {value}'.format(
column_name=column_name, operator=f.operator,
value=value)
else:
condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format(
column_name=column_name, fallback=fallback, operator=f.operator,
value=value)
return condition
def prepare_filter_condition(self, f):
"""Returns a filter condition in the format:
ifnull(`tabDocType`.`fieldname`, fallback) operator "value"
"""
f = get_filter(self.doctype, f)
tname = ('`tab' + f.doctype + '`')
if not tname in self.tables:
self.append_table(tname)
if 'ifnull(' in f.fieldname:
column_name = f.fieldname
else:
column_name = '{tname}.{fname}'.format(tname=tname,
fname=f.fieldname)
can_be_null = True
# prepare in condition
if f.operator.lower() in ('ancestors of', 'descendants of', 'not ancestors of', 'not descendants of'):
values = f.value or ''
# TODO: handle list and tuple
# if not isinstance(values, (list, tuple)):
# values = values.split(",")
ref_doctype = f.doctype
if frappe.get_meta(f.doctype).get_field(f.fieldname) is not None :
ref_doctype = frappe.get_meta(f.doctype).get_field(f.fieldname).options
result=[]
lft, rgt = frappe.db.get_value(ref_doctype, f.value, ["lft", "rgt"])
# Get descendants elements of a DocType with a tree structure
if f.operator.lower() in ('descendants of', 'not descendants of') :
result = frappe.db.sql_list("""select name from `tab{0}`
where lft>%s and rgt<%s order by lft asc""".format(ref_doctype), (lft, rgt))
else :
# Get ancestor elements of a DocType with a tree structure
result = frappe.db.sql_list("""select name from `tab{0}`
where lft<%s and rgt>%s order by lft desc""".format(ref_doctype), (lft, rgt))
fallback = "''"
value = (frappe.db.escape((v or '').strip(), percent=False) for v in result)
value = '("{0}")'.format('", "'.join(value))
# changing operator to IN as the above code fetches all the parent / child values and convert into tuple
# which can be directly used with IN operator to query.
f.operator = 'not in' if f.operator.lower() in ('not ancestors of', 'not descendants of') else 'in'
elif f.operator.lower() in ('in', 'not in'):
values = f.value or ''
if not isinstance(values, (list, tuple)):
values = values.split(",")
fallback = "''"
value = (frappe.db.escape((v or '').strip(), percent=False) for v in values)
value = '("{0}")'.format('", "'.join(value))
else:
df = frappe.get_meta(f.doctype).get("fields", {"fieldname": f.fieldname})
df = df[0] if df else None
if df and df.fieldtype in ("Check", "Float", "Int", "Currency", "Percent"):
can_be_null = False
if f.operator.lower() == 'between' and \
(f.fieldname in ('creation', 'modified') or (df and (df.fieldtype=="Date" or df.fieldtype=="Datetime"))):
value = get_between_date_filter(f.value, df)
fallback = "'0000-00-00 00:00:00'"
elif df and df.fieldtype=="Date":
value = getdate(f.value).strftime("%Y-%m-%d")
fallback = "'0000-00-00'"
elif (df and df.fieldtype=="Datetime") or isinstance(f.value, datetime):
value = get_datetime(f.value).strftime("%Y-%m-%d %H:%M:%S.%f")
fallback = "'0000-00-00 00:00:00'"
elif df and df.fieldtype=="Time":
value = get_time(f.value).strftime("%H:%M:%S.%f")
fallback = "'00:00:00'"
elif f.operator.lower() == "is":
if f.value == 'set':
f.operator = '!='
elif f.value == 'not set':
f.operator = '='
value = ""
fallback = '""'
can_be_null = True
if 'ifnull' not in column_name:
column_name = 'ifnull({}, {})'.format(column_name, fallback)
elif f.operator.lower() in ("like", "not like") or (isinstance(f.value, string_types) and
(not df or df.fieldtype not in ["Float", "Int", "Currency", "Percent", "Check"])):
value = "" if f.value==None else f.value
fallback = '""'
if f.operator.lower() in ("like", "not like") and isinstance(value, string_types):
# because "like" uses backslash (\) for escaping
value = value.replace("\\", "\\\\").replace("%", "%%")
else:
value = flt(f.value)
fallback = 0
# put it inside double quotes
if isinstance(value, string_types) and not f.operator.lower() == 'between':
value = '"{0}"'.format(frappe.db.escape(value, percent=False))
if (self.ignore_ifnull
or not can_be_null
or (f.value and f.operator.lower() in ('=', 'like'))
or 'ifnull(' in column_name.lower()):
condition = '{column_name} {operator} {value}'.format(
column_name=column_name, operator=f.operator,
value=value)
else:
condition = 'ifnull({column_name}, {fallback}) {operator} {value}'.format(
column_name=column_name, fallback=fallback, operator=f.operator,
value=value)
return condition
