Overdue Contractor

Jadestone only. Report code overdue_contractor, added by INA27-289 (Done). Opens the documents_master print mode with type=overdue_contractor; the dispatcher includes documents_master_handler_print_overdue_contractor.php, 614 lines, on $pdo. One row per MDR document that is waiting on the contractor: never submitted although its IFR plan date has passed, or returned and not yet resubmitted. Where the Routing Overdue reports blame the reviewer, this one blames the contractor.

How this page was verified

Traced in ina-jadestone on 2026-09-08 against the ticket. Screenshots from inact-jadestone.eris.place on the production snapshot of 2026-09-07, project AAL-VS. Where the code differs from the ticket text, the code is what is described here.

The filter form

Overdue Contractor, the filter form: Project and one Filter by row · click to enlarge

Project, and a Filter by row: a field, an operator and a value (field[], operator[], what[]). The fields are Document Number, Document Title, Discipline, Doc Type, Revision, Issued Status, Date Received, Date Return and Return Code; the value box turns into a dropdown for the reference fields. Operators: Contains, Start With, Equals, Not Contain, Not Equal, and Less than / Greater than for the dates. The filter is applied in PHP after the rows are built, on the printed text and, for the reference fields, on the code as well. Preview or Excel through overdue_contractor_download_as.

The three queries

1. The MDR. Every ts_documents_master row of the project, joined to ts_mdr_progress for the plan dates, ordered by discipline, document type, document number. This is the row order of the report.
2. The latest library row per document. ts_documents of the project without the _R return rows, ordered by doc_no DESC; the first row per document number wins. Joined to ts_doc_resultcode for the Return Code and Status, and to ts_issued_status on next_expected_id for Next Submission.
3. The latest routing per document. ts_routing joined to ts_map_routing_file and ts_map_routing_to, ordered by rout_id DESC; the first row per document wins. Only its rout_status matters, plus its result name as a fallback for Status.

No date window: the report is always "as of today".

Which documents appear

Project AAL-VS: PURPOSE and STATUS tell the condition apart. Not Yet Submitted rows have no revision and no dates · click to enlarge

Every MDR row is classified once, from its latest library row (doc_send_received filled means submitted) and its latest routing:

Condition	Test	Included when	Required Date	Days Overdue
A, never submitted	no doc_send_received on the latest library row, or no library row	the IFR plan date from MDR Progress (progress_content.two.IFR.plan_date) has passed	that IFR plan date	today minus the plan date
B, submitted, routing still running	submitted, no doc_appdate, and the latest routing is not closed (or there is no routing)	always	blank	blank
C, returned	submitted and doc_appdate filled	always, even when the deadline is still ahead	doc_appdate plus 5 working days	today minus Required Date, negative while still in time
not listed	submitted, no doc_appdate, latest routing closed	never

Two points differ from the ticket. Condition A prints the IFR plan date as Required Date and its lateness as Days Overdue (the ticket had both blank), and condition C rows are listed whatever the sign of Days Overdue, so a document returned yesterday shows with -4. Only positive values get the red highlight in HTML.

5 working days is hari_kerja($date_return, 5, $holidays) from business_day.inc.php: a Saturday or Sunday start moves to Monday, then five weekdays are added, skipping the dates in ts_holiday (getHolidayDate(), all years, no project filter). The Sunday-to-Monday jump means a return dated on the weekend gets the same Required Date as one dated the following Monday.

The columns

The right-hand columns: Required Date, Days Overdue (red when positive) and Next Submission · click to enlarge

Fifteen columns, the same in HTML and Excel. The ticket listed nineteen; the four routing-side columns (both Due Dates, the routing Days Overdue, Responsible / Consolidator) were not built.

#	Column	Source	Blank when
1	No.	row number
2	CTR	dm_ctr_number
3	DISCIPLINE / CATEGORY	docdisc_name of dm_discipline_identifier
4	Doc Type	doctype_name of dm_document_type
5	DELIVERABLES DOC. NUMBER	dm_docmaster_number
6	DOC. TITLE	dm_docmaster_title
7	CURRENT REV.	doc_rev of the latest library row	condition A
8	PURPOSE	issued_desc of the latest row's issued_code; Not Yet Submitted in condition A
9	DATE RECEIVED	doc_send_received	condition A
10	DATE RETURN	doc_appdate, the approver's sign-off date	A and B
11	RETURN CODE	doc_res_code via doc_res_id	A and B
12	STATUS	doc_res_name when a return code exists (the routing's result name as fallback); Not Yet Submitted in condition A	B
13	Required Date	see the table above	B
14	Days Overdue	see the table above	B
15	Next Submission	next_expected_id - issued_desc of the latest row	nothing chosen, or A

Dates print as stored, Y-m-d. The Excel file is Overdue_Contractor_Report_<timestamp>.xlsx with the title and the printed date above the header row.

Known issues

• Condition A ignores the ticket's blanks: Required Date and Days Overdue are filled from the IFR plan date, and a document with no IFR plan date at all is never listed, however old.
• Condition C never drops off. A returned document stays listed until it is resubmitted, and a negative Days Overdue is the only sign that it is still in time. Sorting or filtering on the sign is not possible from the form.
• The latest routing is the first recipient row, whatever the join order returns; its result name is only a fallback for Status, so this rarely shows.
• Whole-project scan in PHP. Three queries load every MDR row, library row and routing row of the project, then the filter runs on the built rows. Fine on AKT (55 rows); slow on a project the size of AAL-VS (4,897).
• The four routing-side columns of the ticket are computed as variables but never printed.

Related

• Overdue Contractor Summary: the pivot of this list.
• Master Doc. Register, Progress tab: where the IFR plan date comes from.
• DMS reports.