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 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 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