UserStory1727 — Estimator schedules

Each field estimator gets a normal week, and extra hours on dates. The booking engine offers each estimator's own hours, and OptimoRoute's driver hours follow them.

What the business asked for

• Default: OptimoRoute driver hours 6am–4pm Mon–Fri; booking window 8am–2pm Mon–Fri.
• A different normal week per estimator. Grant's driver hours are 6am–4:30pm, to allow for his extra travel.
• A specific Saturday, per estimator, bookable in both FormsPortal and AdminPortal.
• Extended booking hours on chosen days during an event, for example 8am–4pm, for chosen estimators.
• Driver hours stretch automatically: the booking window plus 2 hours either side.
• Leave is out of scope: staff already block it out with appointments.

Three layers, most specific wins

dbo.fnEthBookingEstimatorDayHours() resolves one row per estimator, state and date:

Layer	Where	Meaning
1. Default	ethBookingDaySlot + config	Mon–Fri, not a public holiday. The state's day slot window (08:00–14:00) is the booking window. Driver hours are that window ± DriverHoursBufferMins (120), so 06:00–16:00.
2. Weekly pattern	ethBookingEstimatorWeeklyHours	One row per estimator per weekday that differs. NULL times fall through to the default. Grant: Mon–Fri, DriverTo 16:30.
3. Dated extra hours	ethBookingEstimatorDayHours	One row per estimator per date. That date becomes a working day with this booking window, whatever the weekday or public holiday.

Driver hours on a dated row:

• On a normal working day, the window ± the buffer, never narrower than normal. An 8–4 event day gives 06:00–18:00, for Grant too.
• On a day off, just the window ± the buffer. A Saturday booked 8–12 gives 06:00–14:00.

Dated rows only add time. They never remove a day: leave stays as appointments.

Readers

Object	Change
fnEthBookingDayAvailability	FromTime, ToTime, SlotMins and the remaining minutes use the estimator's window. "Working that day" replaces the Mon–Fri and public-holiday tests. The two tables are joined directly on plain columns: going through the function's own eligibility logic cost 78 → 256 ms CPU per job on dev, while direct joins cost the same as before.
vEthBookingEstimatorDayLoad	Joins the function in place of its own SlotEstimators copy. It uses the estimator's window and IsWorking.
spOptimoRouteGetAllowSpecificTimeBookings	For a named estimator, the slot is their window that day; 0 when they don't work.
spEthBookingGetAvailabilityGeo / ...Details	Unchanged. They read the function, so a Saturday or event day just appears.
AdminPortal BookingAdvanced / BookingConfirmTime	The window is the chosen estimator's for that day. Morning and afternoon are the first and last four hours: 8–12 and 10–2 on a normal day, 8–12 and 12–4 on an 8–4 day. The radio labels show the real times.
FormsPortal Estimator.razor	Unchanged. It already books the row's own From/To.

OptimoRoute

• The weekly pattern isn't pushed. Set the driver's normal hours on the driver in OptimoRoute to match. The Hours dialog says so on save.
• Dated hours ride on the relocation push (ethOptimoRouteDriverLocationPush, extended by migration 0030 with Enabled, WorkTimeFrom, WorkTimeTo and HoursOverride). spOptimoRouteDriverLocationPushQueue returns HoursAction:
  • Override: dated hours apply. The call sends enabled: true and workTime = driver hours.
  • Reset: dated hours were pushed and then removed. It sends the normal hours, and switches a normally-off day back off.
  • NULL: no work time is sent, so appointment-driven extensions survive.
• Same rules as relocations:
  • push on save and re-queue planning;
  • dates inside the plan window are recorded ManualRequired;
  • failed dates retry before each re-plan.
• CheckEstimatorWorkTime (OptimoRouteOrderService.ResolveWorkTime) uses the estimator's hours that date as the baseline, not a fixed 06:00–16:00. So it no longer narrows Grant back to 4pm, or an event day back to 6–4. The hours only ever widen. That also fixes an old quirk: when only the end time was extended, the start was reset to 06:00.
• Known gap: a ManualRequired date caused by hours shows in the relocation dialog's "OptimoRoute changes" list with the location wording.

AdminPortal

The Settings > Estimator Locations & Hours page (Admin and BookingManager) now has:

• an Hours column showing "Custom week" and "Extra days (n)", plus an Hours button that opens EstimatorScheduleEdit:

  • the normal week (blank = default);
  • that estimator's extra-hours dates, with push status, add and remove.

• an Extra hours toolbar button that opens EstimatorDayHoursBulk:

  • choose estimators, with "All in VIC/QLD" shortcuts;
  • a date range and the weekdays to include;
  • booking hours and a reason.

  Each estimator's dates are then pushed.

Objects

• Migration 0030_20260930_EstimatorSchedule.sql:
  • the two tables;
  • config DriverHoursBufferMins = 120;
  • the push table's hours columns.
• Function 07_fnEthBookingEstimatorDayHours.
• Procs:
  • spEthBookingEstimatorWeeklyHoursList and ...Save;
  • spEthBookingEstimatorDayHoursList, ...Upsert, ...Delete and ...Get;
  • changed: spOptimoRouteDriverLocationPushQueue, ...PushRecord and spEthBookingEstimatorLocationSummary.

Dev verification (2026-09-30)

• With no schedule rows:
  • the availability function returned all 3,481 rows identical to the previous version, at the same CPU (A/B in one batch);
  • the Geo proc output was identical for every common job.
• Grant test: normal week to 16:30, Saturday 10 Oct 8–12, Monday 12 Oct 8–4.
  • The function gave 06:00–16:30 on normal days, Saturday 06:00–14:00 and the event Monday 06:00–18:00.
  • The Details proc offered Saturday (240 min) and Monday (480 min).
  • The specific-time check allowed Saturday.
• Push to the dev OptimoRoute: both dates were pushed, then reset after the rows were removed (Saturday switched off, Monday back to 6–4). The queue was then empty.
• Clean-up: the test data was removed.