# PRD — Paterson & Cooke Management System

Evidence date: 2026-10-08.

Source: `git@bitbucket.org:overdrive/patersoncooke.git`, `master` at `008c5ac` (2022-02-24).

There is no prior PRD in the source repository or in Silicon Ark. Requirements below are recovered from the implementation and from the short dependency list in `README.md`. Provenance is labelled. Connection passwords are not reproduced.

## Product purpose

**Implemented.** A logged-in person uses a browser to maintain offices, people, clients, and projects, to enter a weekly timesheet, to sign that week off, and to build a project invoice from time, fees, and disbursements. Managers run reports for utilisation, leave, project cost, and invoices.

**Documented.** `README.md` does not state a purpose. It lists Apache, PHP 7.4, and `short_open_tag = on`.

The on-screen name is Paterson & Cooke Management System, version 1.2.1 dated 4 Jan 2010 (`include/version.inc.php`).

## Users and roles

Accounts are rows in `tblPersonnel`. There is no separate user table in the SQL that the live pages call.

| Actor | Evidence | Status |
|---|---|---|
| Person at the login screen | `login.php`. Username and password. Submit runs client-side MD5, then the server compares that value with `md5` of `tblPersonnel.strPassword` | **Implemented** |
| Signed-in person | Cookie `ckyPCCE` holds personnel id, display name, security-level id, and username, separated by `\|` | **Implemented** |
| Person with a security level | `tblSecurityLevel`, edited on `securitylevel.php`. Each area is None (0), View (1), or Full (2) | **Implemented** |
| Personnel with timesheet scope | Flags `blnTimesheetOwn` (loaded, not shown on the form), `blnTimesheetOffice`, `blnTimesheetAll` | **Implemented**. Own-timesheet is the default when the office and all flags are 0 |
| Project manager | `tblProject.ProjectManagerID` points at personnel. Used as a report filter | **Implemented** role on a project, not a login role |
| Project director | `tblProject.ProjectDirectorID`. Same pattern | **Implemented** |
| Supervisor | `tblPersonnel.refSupervisorID`. Stored and edited. No workflow in the inspected code reads it to approve time | **Implemented** as data. Approval use is **Unknown** |
| Legacy numeric groups | `system.inc.php` defines 30 View Only, 60 RSVP, 90 Administrator. Only `checkSecurity_NOTUSED` and the unused `user.cls.php` list refer to them | **Implemented** leftover. Not the live permission check |
| Deploy operator | Not represented | **Unknown** |

`user.php` is not a working admin screen. It calls `checkSecurity("90")`, which looks up a column named `90` on the security-level row, and then calls `sql_user_*` functions that are not defined in `include/database/sql.inc.php`.

## Systems the product talks to

1. The person's browser (HTML, CSS, and JavaScript from this tree).
2. MySQL database `pcce` on the host compiled into `include/database/mysql.cls.php` (default `localhost`).
3. The server's `mail()` function, when a completed-timesheet reminder is sent.
4. Nothing else. No payment gateway, identity provider, object storage, or queue appears in the tree.

## Major workflows

### WF-1 — Sign in

`login.php` clears `ckyPCCE`, then shows username and password. On submit, JavaScript replaces the password field with its MD5 digest (`include/javascript/md5.js`) before the POST.

The server loads `tblPersonnel` by username (`sql_personnel_logon`). Login succeeds only when `md5` of the stored password equals the submitted digest and `blnActive` is `1`. The cookie is then set and the browser is sent to `index.php`.

Failure messages:

- Unknown username: "Login failed, please try again".
- Digest mismatch: "Login failed1, please try again".
- `blnActive` is not 1: "Login failed, your user account has been disabled."

Provenance: **Implemented**.

There is no forgot-password flow, no lockout, and no second factor.

### WF-2 — Open the home page

`index.php` requires the cookie (`checkLogin`). It shows "Welcome to Paterson & Cooke Management System" and the database-driven menu.

If the cookie is missing, `checkLogin` sends the browser to `login.php` and stops.

The cookie is not checked against `tblPersonnel` on later requests. Any non-empty `ckyPCCE` passes `checkLogin`. Provenance: **Implemented**.

### WF-3 — Maintain personnel

`personnel.php` requires login and `blnPersonnel` at least View (1). Full (2) can edit any person and see Save. View can edit only their own username and password fields; other fields are read-only or disabled.

The form captures display name, email, office, workgroup, supervisor, security level, username, password, default activity code, active flag, and a billing-code matrix (included, and one primary).

Client-side password rule: at least 6 characters and at least one digit. The stored value is the text from the form, not a hash.

Save rejects a duplicate username (`sql_personnel_username_check_unique`) and tells the user the display name already exists. If no primary billing code is selected, the person row is still written and the page shows "No default billing code selected!"

Delete does not remove the row. It sets `blnActive = 2` (`sql_personnel_delete`). Insert treats any active value other than 1 as 2.

Provenance: **Implemented**.

### WF-4 — Enter a weekly timesheet

`timesheet.php` requires login after it has already answered two XML requests (project areas and activity codes, or deliverables for an area). See FR-014.

The sheet defaults to the signed-in person, the current year, and the week after that person's last signed-off week in that year. If there is no signed-off week, it uses the current ISO week number.

The person picker is limited by security level:

- No office and no all-timesheet flag: own personnel only (`sql_TimesheetOwn`).
- Office flag greater than 0 and all-timesheet flag 0: people in that office.
- All-timesheet flag greater than 0: all personnel returned by `sql_TimesheetAll`.

Save is offered when the viewer is the timesheet owner, or when they have Full office access for the same office, or Full all-timesheet access for another office. A week with `blnPersonnelSignOff = 1` hides save.

Each day has rows. A row has project, billing code, activity code, area, deliverable, comment, and 36 half-hour cells (0.5 hour each). Hours are the count of selected cells. The script blocks a cell that is already selected on another row of the same day.

Activity codes offered depend on the project. Project id 0 loads codes where `blnProjectWork <> 1`. Any other project loads codes where `blnProjectWork = 1`. A default code is marked with `*`.

Projects in the list are those linked to the person's office through `tblProjectOffice` whose status has `blnEnter = 1`.

Save creates `tblWeek` if needed, then inserts or updates `tblTimesheet` and `tblTimesheetHours`. A row with zero hours, or with the delete checkbox, is deleted when it already has an id. A new row with no activity code is stored with activity 0 and comment "Error Saving this records! Please review it!"

Client-side, before save, each row's hours must be a multiple of the activity code's `strConstraint` when that constraint is greater than 0. Constraint `4` is described in the alert as "4 or 8" because 8 is a multiple of 4. Constraint `8` requires a multiple of 8. Constraint `0` is "none". This check is in the browser only (`validateTimesheetForm`).

Provenance: **Implemented**.

### WF-5 — Review, sign off, remind, reopen

From the timesheet, act 3 or 4 opens `timesheetreview.php` for the same person, week, and year.

The review page shows the week. If the week is not signed off, an **Accept Timesheet** button is shown. Accept sets `tblWeek.blnPersonnelSignOff = 1` for that `WeekID`. It does not record who accepted or when.

`completedtimesheet.php` lists personnel in the viewer's timesheet scope for a chosen year (2007 through the current year plus one) and week. Each row shows whether that week is signed off, the last signed-off week number when it is not, a link to the review page, and a **Send** button when it is not signed off.

Send calls `mail()` to the person's email. The subject is "Timesheet Reminder Week {n}". The body says the timesheet for that week and year is outstanding. From and CC come from the signed-in person and from a supervisor email field passed into the script. Delivery success is not shown in the inspected function.

A **Reactivate** button is rendered when the viewer has Full timesheet-office or Full timesheet-all permission and the week is signed off. It sets `blnPersonnelSignOff = 0`. The column that contains the button is labelled only when the viewer also has Full project permission. Provenance: **Implemented**. The reason those two checks differ is **Unknown**.

### WF-6 — Maintain a project

`project.php` requires login and `blnProject` at least View. Full can save and delete.

The form captures project number (`strProjectCode`), status, Paterson & Cooke project manager, project director, client, technical contact (text), commercial contact (text), order number, and billing details. An office list requires one primary office and a default fee structure per included office (`validateProjectForm` blocks save without a primary office).

`strVAT` is read from the project row into a local variable. The detail form that follows does not render a VAT input. Client records have their own VAT field. Provenance: **Implemented** load; the missing input is **Implemented** as absence.

Further project actions on the same page:

- Show, load-default, and load-new project billing rates, then save them (`act` 4, 41, 42, 5).
- Edit and save the project proposal (`act` 6 and 7), including areas, deliverables, hours, and values.

`projectbillingrate.php` is a separate page. It includes a class file that is not in the tree. See the project plan.

### WF-7 — Build an invoice

`invoice.php` requires login. The `blnInvoice` security check is commented out, and `blnInvoice` is not a column on the security-level form. Any person who can open the URL can use the page. Whether the menu shows it is **Unknown** (menu rows are not in git).

The list can be searched. Opening an invoice (`act` 1) shows project, office, invoice number, invoice date, item date range, external flag, internal flag, and fixed-price choice.

Save (`act` 2) rejects a duplicate invoice number (`sql_invoice_check_unique`). A new fixed-price invoice inserts fee rows for the project's offices (`sql_invoice_fee_insert_project_office`), all offices when a primary office exists, otherwise the selected office.

Other actions:

- `act` 4 links timesheet lines in the date range (`invoicetimesheet.cls.php`).
- `act` 5 and 6 edit invoice fees.
- `act` 9 and 10 link site allowance, internal disbursements, and external disbursements.
- `act` 7 deletes the invoice and its item, fee, site-allowance, internal-disbursement, and external-disbursement link rows.

Project dropdowns for invoicing use statuses with `blnReview = 1`.

When the project changes, an XML branch (it runs before `checkLogin`) returns the project's offices and a suggested start date: the later of the last invoice item date and the last timesheet date, or the first of the current month when neither exists.

Provenance: **Implemented**.

### WF-8 — Run a report

`reportconsole.php` requires login. Its security check is commented out and names a column `blnReportConsole` that the security-level form does not edit. The form does edit `blnReports`. Nothing in the inspected page reads `blnReports`. Provenance: **Implemented** mismatch.

The console lists:

- Close-Out
- Dashboard — Resource Utilization (Office)
- External Disbursements
- Incomplete Timesheet
- Individual Summary By Activity
- Individual Summary By Day
- Individual Summary By Project
- Internal Disbursements
- Invoice Report
- Leave
- Project Director
- Project Manager
- Project Summary
- Timesheet Detail (Project)
- Timesheet Summary (Project)
- Timesheet Detail (All)
- Utilisation graph

The operator picks the report, then the variables that report enables (project, invoice, office, project director, personnel, project manager, date range or "ignore dates", group-by, include admin hours, signed-off time only). **View Report** and **Printer-Friendly** open a new window on `{report}.php`. **Export** navigates the current window to the same URL. Export responses that were inspected set `Content-type: application/x-msexcel` and still emit an HTML table. The `ExcelWriter` class in `include/excelwriter.inc.php` is not included by any page.

Project choices are statuses with `blnReview = 1`. Personnel choices exclude display names starting with `Admin`. Project director and project manager lists are distinct people currently assigned on `tblProject`, also excluding `Admin`.

`leave.php`, `incompletetimesheet.php`, `internaldisbursement.php`, and `externaldisbursement.php` have `checkLogin()` commented out. The other report pages call `checkLogin()`.

## Functional requirements

### FR-001 — Login

The system accepts a username and a password, hashes the password in the browser, and signs the person in when the personnel row is active and the digest matches `md5` of the stored password.

- Actor: person at `login.php`.
- Trigger: Login submit.
- Inputs: `strUserName`, `strPassword` (MD5 hex after the click handler).
- Output: cookie `ckyPCCE`, redirect to `index.php`, or one of the three failure messages.
- Validation: active flag must be 1. Empty username finds no row.
- Error: the three messages above. Database errors are not given a product message.
- Evidence: `login.php`, `sql_personnel_logon`.
- Implementation: **Implemented**. Confidence: High.

### FR-002 — Session gate

Pages that call `checkLogin` redirect to `login.php` when `ckyPCCE` is unset.

- The cookie payload is trusted without a database lookup.
- Evidence: `checkLogin`, `ckyToArray` in `include/system.inc.php`.
- Implementation: **Implemented**. Confidence: High.

### FR-003 — Security level

An editor with Full `blnSecurityLevel` can create a level with a description and a code, and can set None, View, or Full on the areas listed in the security-level form (activity code, billing code, billing rate, client, currency, fee structure, office, personnel, project, project status, security level, settings, timesheet all, timesheet office, workgroup, reports).

Description is required in the browser. Description must be unique on save.

Page gates that were found: activity category and activity code use `blnActivityCode`; billing code, billing rate, client, currency, fee structure, office, personnel, project, project status (`blnProjectstatus`), security level, settings, workgroup. Value 1 is required to open the page. Value 2 is required to show New, Save, or Delete on those screens.

Timesheet behaviour uses the numeric flag directly (greater than 0 to widen the person list, equal to 2 to allow save on someone else's sheet).

- Evidence: `securitylevel.cls.php`, `checkSecurity`, page headers.
- Implementation: **Implemented**. Confidence: High.
- `blnTimesheetOwn` is selected from the row and is not drawn on the form. **Unknown** whether the column still exists and is maintained outside this screen.

### FR-004 — Personnel record

See WF-3. Required in the browser: display name, email, username, password. Password rule is browser-only.

- Evidence: `personnel.cls.php`, `sql_personnel_insert`, `sql_personnel_delete`.
- Implementation: **Implemented**. Confidence: High.

### FR-005 — Weekly timesheet

See WF-4.

- A signed-off week is not editable from the timesheet screen (`bSave` forced false). `Save()` itself does not re-read the sign-off flag.
- Evidence: `timesheet.cls.php`.
- Implementation: **Implemented**. Confidence: High.

### FR-006 — Sign-off and reminder

See WF-5.

- Evidence: `timesheetreview.cls.php` `AcceptTSR`, `completedtimesheet.cls.php`.
- Implementation: **Implemented**. Confidence: High.
- Whether `mail()` is configured on the host is **Unknown / Requires Confirmation**.

### FR-007 — Project

See WF-6. Browser required fields: project number, technical contact, commercial contact, and a primary office. Status, manager, director, and client are marked required on the form by a star icon; the JavaScript validator does not add them to the message list.

- Evidence: `project.cls.php` `validateProjectForm`.
- Implementation: **Implemented**. Confidence: High.

### FR-008 — Invoice

See WF-7. Invoice number must be unique. Fixed-price creation writes fee rows.

- Evidence: `invoice.cls.php` `Save`, `Delete`.
- Implementation: **Implemented**. Confidence: High.
- A permission dedicated to invoices is **not enforced**.

### FR-009 — Disbursements on an invoice

The invoice can attach site allowance, internal disbursements, and external disbursements for the project and date range. Site allowance rates are looked up with billing code text `Site Allowance` (`sql_internal_disbursement_PBR_get`).

- Evidence: `disbursement.cls.php`, `invoice.php` act 9 and 10.
- Implementation: **Implemented**. Confidence: High.
- The exact markup arithmetic for each disbursement type is in `disbursement.cls.php` and is data-dependent (project billing rates). A worked example cannot be confirmed without the database.

### FR-010 — Reports

See WF-8. Each report page reads query parameters and renders a table. Export sets an Excel content type on the HTML table.

Leave rows are timesheet lines whose activity category has `blnLeave = 1`. Duration is shown as days using `roundUp(hours, 8, 5)`. The heading says "Presented as days (hour / 8)".

The office utilisation dashboard sums hours by person and splits billable billing codes from activity code `PROP` (proposal). `PROP` is excluded from the first aggregate and unioned back as billable bucket `3`.

- Evidence: `reportconsole.cls.php`, `include/reports/leave.cls.php`, `dashboard.cls.php`, `sql_leave_report_get`.
- Implementation: **Implemented**. Confidence: High.

### FR-011 — Reference data

The following screens are list / add / edit / delete (or deactivate) maintenance, each behind the security flag named in FR-003, except where noted:

| Screen | Main fields |
|---|---|
| Office | Code, name, telephone, fax, email, postal and physical address lines, active flag, fee structures (included and one primary) |
| Client | Name, postal address lines, VAT, active flag |
| Contact | Client, name, surname, position, email, telephone, cell, fax |
| Currency | Name, code |
| Fee structure | Name, currency, then billing rates (external, internal, cost) |
| Billing code | Code, description, billable or internal-disbursement (the two checkboxes clear each other), linked activity codes |
| Billing rate | Per fee structure and billing code: external, internal, cost. Uniqueness is checked |
| Activity category | Name, project-work flag, leave flag (the two checkboxes clear each other) |
| Activity code | Code, description, category, project-work flag, hour constraint (none, 4, or 8) |
| Workgroup | Name |
| Project status | Name, description, Enter, Review, Documents |
| Settings | One value, rows per page (`intRowsPerPage`), used by `show_page_nums` |

Project status **Enter** limits which projects appear on a timesheet (`blnEnter = 1`). **Review** limits which projects appear on the invoice and report project lists (`blnReview = 1`). **Documents** is stored and shown. No other reader of `blnDocuments` was found.

Provenance: **Implemented**. Confidence: High for the flags that have a reader. Documents is **Implemented** as stored data with no further behaviour found.

### FR-012 — List paging

Lists page by `tblSettings.intRowsPerPage` via `show_page_nums`. The settings screen is the editor for that number.

- Evidence: `system.inc.php`, `settings.cls.php`.
- Implementation: **Implemented**. Confidence: High.
- The default before a settings row exists is **Unknown**. `get_system_settings` returns 0 when the query has no row, and later code indexes `intRowsPerPage` on that result.

### FR-013 — Menu

After the page body, `include/menu.inc.php` loads every `tblmenulevel0` row and its `tblmenulevel1` children and builds a JavaScript menu.

- The labels and URLs are **Unknown** without the database.
- Evidence: `menu.inc.php`.
- Implementation: **Implemented** mechanism. Confidence: High for the mechanism, Low for the actual menu.

### FR-014 — XML helpers before the login check

These branches run before `checkLogin` and return XML:

| Page | Trigger | Returns |
|---|---|---|
| `timesheet.php` | `ProjectID` set | Areas for the project, then activity codes (non-project codes when project is 0, otherwise project-work codes) |
| `timesheet.php` | `AreaID` set | Deliverables for the area |
| `project.php` | `GetClientContact` set | Contacts for that client |
| `invoice.php` | `ProjectID` set | Offices for the project and a suggested start date |
| `reportconsole.php` | `refProjectID` set | Invoice numbers for the project |

The project form's client dropdown calls `onchange=''`. The contact loader is present and not wired to that dropdown.

Provenance: **Implemented**. Confidence: High.

## Integrations

| System | Purpose | Trigger | Direction | Auth | Failure |
|---|---|---|---|---|---|
| MySQL `pcce` | All product data | Every page that includes `system.inc.php` | Read and write | Username and password compiled into the connection class | `openConn` does not show a product error page. Many queries `die` with the SQL text |
| PHP `mail()` | Timesheet reminder | Send on completed timesheet | Outbound email | The host mailer. Not configured in git | Return value is not surfaced in `SendMail` |
| None other | | | | | |

## Important constraints

- PHP short open tags. **Documented** in the README and **Implemented** in class files.
- Database name `pcce`. **Implemented** default.
- Dates are stored as `YYYYMMDD` strings in many columns (`strDate`). Display format is `dd-mm-YYYY` or `dd-Mon-YYYY`.
- Week numbering was adjusted on 2010-01-07 so week 1 follows the Monday on or before 4 January. The same block is copied in the timesheet, the review, and the completed-timesheet screens.
- Passwords are stored as the characters typed on the personnel form. Login compares an MD5 of that stored value to the MD5 the browser already computed.
- Menu contents, security-level names, offices, and fee numbers are operational data. They are not in git.

## Requirements that could not be confirmed

- Production URL, host, and whether the 2022 mysqli change is what production runs.
- Contents of the menu tables and of the security-level rows.
- Whether `blnDocuments` was meant to control a document store. No document upload was found.
- Whether supervisor is used outside the personnel form and the reminder CC.
- Business meaning of activity code `PROP` beyond the dashboard query that special-cases it.
- Whether invoice external versus internal changes customer-facing documents outside this database. The flags change which rates and office controls the invoice screen uses.