Payroll / Timesheet — Architecture & data

Timesheet export — server-side CSV via jsReport

• The browser only sends the id list + mode; no column definitions, value formatting, or leave-overlap logic.
• The payload keeps shift-row ids and leave-only-row ids separate — the system never infers the record type from an id value.
• The backend builds the dataset itself, pre-formatting everything (date, time range, 2-decimal hour totals) before rendering.
• A single jsReport template whose name matches the fixed ReportCode for Timesheet CSV export.

Real-world example: Export a rostered shift row 09:00–17:00. → The browser does not format it. → Cell Rostered Time = 09:00–17:00, Rostered Hours = 8.00, both pre-built by the backend.

Scope & permissions on export

• The BU/org scope of the caller resolves from the server-side context, not the payload.
• An id — shift row or leave-only — outside the caller's scope → the entire request is rejected, results are not narrowed.
• Missing Timesheet permission (view the Approved tab) → rejected, no rows marked exported.
• Export content comes from stored data, not the caller's screen state.

Real-world example: Caller scope does not cover org B but the request includes a row from org B. → Do not return a file missing rows. → The whole request is rejected, no row marked exported.

Marking exported

• exported is marked in the same export request, only after a successful render.
• Shift row → marked on Shift.Exported; leave-only row → marked on EmployeeAbsence.Exported.
• Render failure (e.g. Reporting Server error) → request fails, no row marked.
• Un-export keeps it simple, clears the exported flag for both row types.

Real-world example: Selection of 1 shift row + 1 leave-only row, render succeeds. → Both records marked at the same time. → Shift.Exported = true and EmployeeAbsence.Exported = true in one round-trip.

Column contract — separated mode

Column order in separated mode (header row always present):

#	Column
1–13	Date, Name, Org, Tracking Code (shift), Note, Rostered Time, Rostered Hours, Clocked Time, Clocked Hours, Actual Time, Actual Hours, To Pay Time, To Pay Hours
14–15	Leave Hours, then directly Tracking Code (leave)

• The leave columns are exactly 2: Leave Hours → Tracking Code; no Leave Tracking Code.
• Values escaped per RFC 4180 (comma, quote, newline) so rows do not break.
• A leave-only row uses this exact column set: Date/Name from the absence, Org empty, shift-hour columns empty, Leave Hours = the absence's hours, Tracking Code = the leave's code.
• The file contains only the selected rows, in grid order.

Real-world example: A row has note Late, rescheduled. → The CSV must not break columns. → The note is quoted, the row still parses to the correct number of fields.

Column contract — grouped mode

Column order in grouped mode (header row always present): Name, Rostered Hours, Clocked Hours, Actual Hours, To Pay Hours, Leave Hours.

• Groups by staff identity, not display name; 2 staff with the same name are still 2 separate records.
• Drops shift-level columns (Date, Org, Tracking Code, Note, every time-range column) instead of emitting them empty.
• Each hour column = per-staff total, formatted with 2 decimals; a row with no contribution reports 0.
• Leave Hours = total leave-overlap of the selected rows; staff with no overlap → 0.00.
• No leave Tracking Code column in this mode (codes do not sum); the leave-columns contract still constrains separated mode.
• Records sorted by staff name.

Real-world example: Selection of 7 rows across 3 staff (2 with the same name). → Do not merge them into one. → The CSV has the header + exactly 3 records; the hours of the 2 same-name staff are not summed.

Leave columns — data source

• Leave Hours = overlap hours between the leave interval and shift interval — not the EmployeeAbsence total hours.
• The leave Tracking Code column = the tracking code of the overlapping leave — not the shift tracking code.
• Multiple leaves overlapping one shift → merged into the same shift row; values keep overlap start-time order.
• Shift with no leave overlap → leave columns follow the empty-value convention.

Real-world example: Shift 09:00–17:00, leave 07:00–11:00 hours = 4, code AL. → Leave Hours = 2 (overlap), code AL. → Not 4 (total) or shift code OT.

Guardrails

• An export request must carry ids, not row data; row data in the payload is ignored.
• Scope and permission checked server-side; an id outside scope → entire request rejected.
• exported marking is always tied to a successful render; no separate request for marking.
• Leave-only row timesheet status never changes the leave's approval lifecycle.
• Every exported value must be built by the backend; the browser holds no formatting/leave-overlap logic.

Main data

Group	Data
Payroll settings	BusinessUnitSetting.DefaultPaidHoursOptionId (0/1).
Shifts & link	Shift, Shift.OrgEmployeeId, OrgEmployee.
Row status	Shift.TimesheetStatusId / Shift.Exported; EmployeeAbsence.TimesheetStatusId / EmployeeAbsence.Exported.
Leave	EmployeeAbsence (Date, TimeStart/TimeEnd, tracking code).
Classification codes	TrackingCode / ShiftTrackingCode (TypeId Leave/Shift).
Template	jsReport template matching the ReportCode Timesheet CSV.