Routing Overdue

Report code: routing_01. Opens the routing module with show=report&print=due; routing_handler_print.php includes routing_handler_print_dueroute.php, 614 lines, on $pdo. One row per recipient of a routing (ts_map_routing_to), with the routing's document, the action asked of that person, the due date and how many days are left. Despite the name it is not limited to overdue rows: the default filter is Route Status = open, and the Days Remaining column is where "overdue" shows as a negative number.

How this page was verified

Traced in master on 2026-09-06 and compared with the big five and KTP. Screenshots from inact-ktp.eris.place, project SNS, Template A with Record Grouping.

The filter form

Routing Overdue, the filter form · click to enlarge

view=custom renders routing_print_due_custom.htm:

Control	Field	Effect
Project	project	the list comes from getProjectPersonByResource(): every active project for admins, the user's projects otherwise
Show All Data	condition[]=show_all	drops every condition below; the template choice still applies
Show Only Latest Revision	only_latest_rev	keeps, per document number, the routing with the highest rout_id whose main file is that document. It is "latest routing", not "latest revision"
Conditions	field[], operator[], what[]	default row: Route Status equals open. Fields are raw column names (r.rout_status, rout_doc_number, rt.rout_indicate, rout_due_date, ...); add search field adds rows, all joined with AND
Template A / B / C	condition[]=A etc.	the column set, from json/table_header.json
Record Grouping	order[]	when present, repeated values are blanked so rows read as groups
Preset	preset, name	a saved set of conditions; saved by act=save_preset in routing_handler_post.php, the name is printed in the title

Date conditions (rout_due_date, rout_date, rt.rout_resp_date, rt.rout_initials) are converted from the INACT date format; an empty date value turns into ISNULL(field), MySQL syntax that fails on PostgreSQL and SQL Server. The originator fields force the operator to =.

The query

One SELECT over ts_map_routing_to rt, joined to ts_routing r, the author (ts_org_person), the originator company, the recipient's resource and person, ts_routing_action_indicated, and a subquery on ts_map_routing_file that returns the first file of each routing (rout_type is not checked; the lowest map_id wins, which is normally the main document). Filtered by r.rout_project_code = <project>, the latest-revision list and the conditions.

The per-row computed column is Days Remaining. It compares two dates only:

• the recipient's due date, ts_map_routing_to.rout_due_date, the Due Date column of that same row (each recipient has their own; the routing itself has no due date), and
• today, the database server's clock at the moment the report runs. Not the sign-off date, not the receive date.

Days Remaining = rout_due_date − today        (in whole days)

So +3 means the due date is three days from now, 0 means it is due today, and -222 means the due date passed 222 days ago. The number is printed only while the row's Action Taken is still Open; once the recipient has signed off, the cell is blank, whatever the dates were.

In the Template B screenshot the report was run on 07/Sep/2026. The row with Due Date 28/Jan/2026 shows -222: 28 January to 7 September is 222 days, and the row is still Open, so the reviewer is 222 days late. The rows with Due Date 15/Aug/2024 show nothing because they are signed off.

Per driver:

Driver	Expression	Sign
MySQL	DATEDIFF(rt.rout_due_date, now())	due date minus today: positive = days left, negative = days late
PostgreSQL	EXTRACT(DAY FROM (rt.rout_due_date - now()))	same
SQL Server	DATEDIFF(DAY, rt.rout_due_date, GETDATE())	today minus due date, so the sign is inverted: positive = days late

The ordering is derived from the template: every column after the first, except Routing Sequence, becomes an ORDER BY key in template order. On MySQL the handler runs SET sql_mode = '' first, because the file subquery uses a loose GROUP BY. On SQL Server the subquery is rewritten with ROW_NUMBER() and rout_sequence_no is formatted with a comma decimal. The full SQL is written to the PHP error log on every run (error_log(print_r($sql, true))).

The columns

Three templates, from table_header.json. They are not three column sets of one list: the template also decides the sort order and the grouping, because every column after the first becomes an ORDER BY key in template order, and Record Grouping blanks the leading columns when they repeat. So the same rows read as three different reports.

	Template A	Template B	Template C
Question it answers	What is the state of each routing?	What is on each person's desk?	How did each routing move through its sequence?
Sorted and grouped by	routing (Route ID, title, status, document, rev, author), then recipients in sequence order	person (Routing To), then action, due date	routing, then recipients in sequence order
Blanked on repeat	the seven routing columns, printed once per routing	the four person columns (Routing To, Action Indicated, Due Date, Days Remaining), printed once per person and action	the seven routing columns, as A
Columns	No, Route ID, Route Title, Route Status, Doc.No., Rev, Author, Routing To, Action Indicated, Due Date, Action Taken, Response, Days Remaining	No, Routing To, Action Indicated, Due Date, Days Remaining, Action Taken, Response, Route ID, Route Title, Route Status, Doc No, Rev, Author	A plus Routing Sequence, Receive Date (rout_initials) and Sign-Off Date (rout_resp_date)
Use it for	the weekly overdue list per document	chasing reviewers: one block per person, overdue rows negative in Days Remaining	audits: when each sequence started, when each person signed

Template A: one block per routing, the recipients under it in sequence order · click to enlarge

Template B: one block per person; the routing columns move to the right, and Days Remaining shows how late each open item is · click to enlarge

Template C: Template A with Routing Sequence, Receive Date and Sign-Off Date, the audit trail of each routing · click to enlarge

Column	Source
Routing To	resource_name (person_id), built with CONCAT in the query; resource_name (No user) when the resource has no person. On Jadestone the bracket holds the person name instead of the id, see instance differences
Action Indicated	ts_routing_action_indicated.action_desc
Action Taken	rout_status_resp mapped through $_ROUT_STATUS: N Open, 1 re-Route, 2 Route as New, 3 Return, 4 Sign Off, 5 Closed Without Action
Response	rout_response, the free text or result code entered at sign-off
Days Remaining	rout_due_date − today for that recipient's row, see above; printed only while Action Taken is Open (rout_status_resp = 'N'); signed-off rows show nothing
Routing Sequence	rout_sequence_no, the step the recipient belongs to; every recipient on the same number is sent together
Receive Date	rout_initials, the planned start date of the recipient's step, see below. Despite the label it is not the day the routing reached the inbox
Due Date	rout_due_date, the recipient's own due date, computed from Receive Date
Sign-Off Date	rout_resp_date, the day the recipient answered; empty while Open

Jadestone only: Issued Status after Rev

Jadestone, Template A on project AAL-VS: Issued Status sits between Rev and Uploaded By · click to enlarge

Added by INA27-299 (Done) to all three templates, in the HTML output and the Excel export. The value is ts_issued_status.issued_desc for the routing file's issued_code, joined on the report's project (LEFT JOIN ts_issued_status i ON i.issued_code = rf.issued_code AND i.project_id = <project>), so it reads Issued for Review rather than IFR, the same text the Inbox shows. A routing whose file has no issued code prints blank.

Rev, Issued Status and Uploaded By on the Jadestone Template A · click to enlarge

Two knock-on changes: Record Grouping on Templates A and C now blanks eight repeated columns instead of seven, because the new column is document-level and must collapse with its routing; and the filter field list gained Issued Status (rf.issued_code). Master and the other forks do not have the column.

Receive Date and Due Date, and when they change

Both are date columns on ts_map_routing_to, one pair per recipient, and both are written once, when the recipient row is created. Nothing rewrites them afterwards: sending the next sequence only flips rout_is_oninbox, and signing off only writes rout_resp_date and rout_status_resp. There is no UPDATE ... SET rout_initials anywhere in the routing module.

How they are set depends on where the row came from:

Row created by	Receive Date (rout_initials)	Due Date (rout_due_date)
Upload from the Distribution Matrix	sequence 1: the upload day, moved off a weekend by get_santos_startdate(). Each later sequence: the day after the previous sequence's due date	Receive Date plus the project's Review Time for that issued status (ts_workingdays), skipping weekends and ts_holiday, by get_santos_duedate() in business_day_santos.inc.php. Rows with the same action copy the first row's pair, so every reviewer of one step shares the dates
Add on the Routing Slip (routing_handler_ajax.php)	today	the routing date plus the review days; if the routing is already past due, today plus one day
Submit Routing from the form (routing_handler_post.php)	whatever the user typed in the Start Date cell of the Routing To grid	whatever the user typed in Due Date

So the whole schedule of a routing is laid out on day one, sequence by sequence, from the review times. If reviewer 1 signs off late, reviewer 2's Receive Date and Due Date are not pushed back: reviewer 2 gets the routing in their inbox after the planned Receive Date, and Days Remaining for reviewer 2 counts against a due date that may already be close or past. That is the number this report shows, and it is why a late first reviewer makes every later step look overdue.

The Routing Slip shows the same two columns as Start Date and Due Date; the report renames the first one Receive Date. The filter form lists it under its old name, Sequence Start Date.

Action Taken, Response and Days Remaining; the last is blank once the recipient has signed off · click to enlarge

Record grouping

With order[] present the rows are already sorted by the template columns, and the handler blanks repeated cells so each routing (Templates A and C, the first seven columns) or each person (Template B, the first four) is printed once. The rows are not merged; a change in any earlier column starts a new group. Without grouping every cell is printed.

Known issues

• Days Remaining has the opposite sign on SQL Server. The Medco report reads "late" where the MySQL instances read "left".
• Latest Revision is really latest routing: the highest rout_id per document, whatever revision it carries.
• The file subquery ignores rout_type; a routing whose first mapped file is an attachment prints the attachment's number in Doc.No.
• A doc_title branch in the row loop prints the sign-off date, but no template has that key, so it is unreachable. Do not add a Doc Title column to table_header.json without fixing it.
• Every run logs the SQL to the web server error log.
• ISNULL(...) for empty date conditions is MySQL syntax; on PostgreSQL and SQL Server the query fails.

Instance differences

Checked on 2026-09-06 by diffing the handler.

Instance	Difference
JOTRE (116 lines)	A reworked file subquery and column set; its QA database installs the same single entry.
KTP (62 lines)	Older filter block; identical output columns.
Jadestone	INA27-293 (merged to main 2026-09, Ready for deployment): Routing To reads Resource Name (Person Name) instead of Resource Name (person_id), in all three templates, in the HTML output and in the Excel export (excel/export_excel.php), because Jadestone's person ids are email addresses nobody recognises. The Routing To filter dropdown reads Person Name (Resource Name) (routmakeFieldValue() in routing.inc.php); the value it sends is still the resource id, so saved presets keep working. Resource Name (No user) is unchanged. INA27-299 (Done): an Issued Status column after Rev in all three templates, see above. Also has its own Routing Overdue Summary (INA27-296).
Prima Energy (23 lines)	An older filter block: no Originator (Company / Person) and no Discipline filter fields, docdisc_id not carried in the file subquery, and the query only counts recipients still in an inbox (rt.rout_is_oninbox = 1). Two iw_mysql_query calls remain; the helper throws, so those paths fatal if reached. Checked by diff on 2026-09-08, not traced.
Timas (19 lines), Medco (3 lines)	Cosmetic. Medco adds the separate Comment Summary report (print=commentsummary).

Jadestone after INA27-293: Routing To reads Resource Name (Person Name), here Sr. Document Control (Atar Sukamto) · click to enlarge

Jadestone filter form: the Routing To values read Person Name (Resource Name) · click to enlarge

Related

• Action Indicated: what the action column means.
• Sign Off: what sets Action Taken and Response.
• General settings: the holidays behind the due dates.