# Technical documentation — timesheet

Evidence date: 2026-10-01.

Source: `git@bitbucket.org:overdrive/timesheet.git`, `master` at `3baa25ba` (2025-07-16).

Passwords, the external login PIN, and other secrets are identified by file. Their values are not copied here. They are committed in git and should be treated as exposed.

## Architecture

Server-rendered PHP. Each screen is a root script that includes the Nemo framework, handles an `Action` post or query value, and prints HTML through a layout include.

```text
layouts/layout.back.incl.php
  header, menu, toolbar, message, content
      ^
Nemo (authenticated) or NemoBasic (login and some crons)
      ^
system.php
  host -> database credentials
  file session
  load sysSettings into $SystemSettings
      ^
page script (timesheet.php, ticket.php, ...)
  includes/*.cls.php
```

There is no router, no Composer autoload in the tree, and no front controller. `index.php` is the post-login home, not a dispatcher.

`NemoDetails::AssimulateTable` builds form fields from the live table's columns. The PHP files do not list every column. A schema dump is not in the repository.

## Application structure

| Path | Role |
|---|---|
| `*.php` at root | Page controllers |
| `includes/*.cls.php` | List, save, and report classes |
| `includes/*.rpt.php` | Report SQL and HTML included by `report.php` |
| `_nemo*.cls.php` | Framework |
| `layouts/` | `header.incl.php`, `layout.back.incl.php`, `layout.basic.incl.php`, `layout.menu.incl.php` |
| `css/`, `js/`, `images/` | Theme, jQuery, Chart.js, icons |
| `custom_controls/calendar_control` | Date control |
| `project.functions.inc.php` | Timesheet helpers, Convergence client, leftover course SQL |
| `system.functions.inc.php` | Login audit, lockout, shared helpers |
| `ajaxfunctions.php` | XML/JSON helpers and password-reset send |
| `cron.daily.php`, `cron.reminder.php` | Batch scripts |
| `fim_2.0/` | File integrity monitor |
| `PHPExcel/`, `PHPExcel18/` | Spreadsheet library and its own docs |
| `scripts/` | CodeDeploy hook scripts |
| `appspec.yml`, `bitbucket-pipelines.yml`, `codedeploy_deploy.py` | Deploy |
| `sonar-project.properties` | Sonar project key only |

`.gitignore` contains one line: `SESSION`.

## Languages and frameworks

- PHP, procedural pages plus classes. Database access is `mysqli` (`system.php`, comment 2018-12-10).
- Custom framework named Nemo. Not Laravel, CodeIgniter, or Symfony.
- JavaScript: jQuery 3.3.1 (`js/jquery-3.3.1.min.js`), jQuery UI 1.8.6, Chart.js (`js/Chart.min.js`), input masks, MD5 (`js/md5.js`), password strength.
- CSS themes: `nemo.blue.css`, `nemo.gold.css`, `nemo.red.css`, `nemo.teel.css`, plus menu and ticket styles.
- No `package.json`. No committed `composer.json` or `composer.lock`.

PHP version is not declared. Host name `timesheets.php8` is **Inferred** to mean a PHP 8 vhost. Error reporting in `system.php` is `E_ALL` minus strict, notice, and warning, while `display_errors` is `1`. Deprecated errors stay enabled because of the bitwise expression as written.

## Databases

Connection choice is `$_SERVER["SERVER_NAME"]`.

| Host name key | Database | Also configured |
|---|---|---|
| `timesheet.overdrive.co.za` | `timesheet2` on an RDS host in `eu-west-1` (hostname in `system.php`) | `$DATABASE_SETTINGS2` database `eitlivedb` on the same RDS host. `$DATABASE_SETTINGS3` database `quickbook_webservice` on `sql27.jnb1.host-h.net` |
| `timesheets.php8` | `timesheets` on `localhost` | `$DATABASE_SETTINGS2` also `timesheets` on localhost |

Charset set after connect: `utf8`.

Session path: `DOCUMENT_ROOT/SESSION/` for both host keys. `session.gc_maxlifetime` is `720000` (seconds).

If `mysqli_connect` fails, the script prints a critical message and does not load settings.

Settings load:

```sql
SELECT * FROM sysSettings WHERE strSetting NOT LIKE 'vie%'
```

Each `strSetting` becomes a `$SystemSettings` key. Known keys referenced by code include `intBaseRate`, `Brand`, `Title`, `System Version`, `LogoutRedirect`, `PageSize`, `SMTP Send As`, `SMTP CC`, `ProfileImageDirAdmin`, `PASSWORD_Enable_Policy`, `PASSWORD_Strength_Check`, `PASSWORD_MaxAge`, `PASSWORD_MaxHistory`, `PASSWORD_Restricted_Words`, `PASSWORD_MinLength`.

### Tables named by the timesheet code

Domain:

- `tblClient` — client. `eitClientID`, `strClient`, `blnActive`.
- `tblProject` — `refClientID`, `strProject`, `blnActive`.
- `tblProjectItem` — `refProjectID`, `strProjectItem`, `refRateID`, `dblHoursEst`, `blnActive`.
- `tblProjectItemTemplate` — `strProjectTemplate`, `strProjectItemTemplate`, `refRateID`, `blnActive`.
- `tblUserProjectItem` — the time line. `refUserID`, `refProjectItemID`, `refTicketItemID`, `strDate`, `strTimeIn`, `strTimeOut`, `dblHours`, `dblRate`, `txtNotesInvoice`, `txtNotesInternal`, `blnInvoiced`, `eitBillingID`, `TimeID`, `strLastUser`, `dtLastEdit`.
- `tblTicket`, `tblTicketItem` — ticket and tasks. Status, priority, assignee, `blnCompleted`, estimate, percent-email flags, `refTaskID`.
- `tblTicketDisplay` — per-user ticket display string.
- `tblRate` — `strRate`, `dblRate`, `blnActive`.
- `tblSLAContracts` — description, `dblSLAHours`, start, end, active.
- `tblPublicHolidays` — `dtDate`, `strName`.
- `vieTicketItemTimesheet` — view used by a commented ticket-list query. The active query was not fully compared. **Requires Confirmation** that the view still exists.

Security and system:

- `sysUser` — `strUser`, `strEmail`, `strPassword`, `strPasswordMD5`, `blnActive`, `refSecurityGroupID`, `dblTarget`, `blnOfficeTarget`, `blnOverview`, `eitUserID`, `dblRemindCount`, `dtLastReminded`, `strPicturePath`, `strDisplayName`.
- `sysSecurityGroup` — `blnActive`.
- `sysSecurity` — flags per group and menu item.
- `sysMenuLevel1`, `sysMenuLevel2` — menu labels, URLs, `strEntity`, order, divider, `blnMenuItem`.
- `sysSettings`.
- `sysLogin` — login audit.
- `sysPassword` — salt, hash, date, type.
- `sysSpam` — written by `tblSpamInsert`. Call sites were not all traced.

Integration and leftover:

- `tblCronJob`, `xtblInvite`, `tblVoucher`, `tblLearner`, `tblCourse`, `tblCourseType`, `tblCourseVenue`, `tblVenue`, `tblLicence`, `tblInvoice` — used by `cron.daily.php` and course SQL in `project.functions.inc.php`. Not used by the timesheet screens that were read.
- FIM, if the commented `CREATE TABLE` is ever run: `sysAudit_master`, `sysAudit_<environment>`. The `mysqli_query` calls that would create them are commented out.

Commented `ALTER TABLE` blocks in the class files describe foreign keys from project to client, project item to project, ticket to user, client, and project, ticket item to ticket, and timesheet line to user, project item, and ticket item (`ON DELETE SET NULL` for the ticket item). Those statements are comments, not migrations. **Unknown** whether the live database matches them.

## Page and AJAX surface

Authenticated pages construct `Nemo`, which requires `$_SESSION["USER"]` and `blnView`.

| Script | Main table or job |
|---|---|
| `index.php` | Home widgets |
| `login.php` | Authentication |
| `resetPassword.php` | Password reset form |
| `message.php` | Session, logout, and permission messages |
| `timesheet.php` | `tblUserProjectItem` |
| `timesheet_ticket.php` | Same, ticket-oriented |
| `ticket.php` | `tblTicket` |
| `client.php` | `tblClient` |
| `project.php` | `tblProject` |
| `project.item.php` | `tblProjectItem` |
| `project.templates.php` | Templates |
| `overview.php` | Target dashboard |
| `report.php` | Reports and invoice actions |
| `user.php` | `sysUser` |
| `security.group.php` | `sysSecurityGroup` |
| `settings.php` | `sysSettings` |
| `rate.php` | `tblRate` |
| `sla.contracts.php` | `tblSLAContracts` |
| `public.holidays.php` | `tblPublicHolidays` |
| `email.php` | Stored mail |
| `email.template.php` | Templates |
| `zSandBox.php` | Scratch client form |
| `ajaxfunctions.php` | See below |
| `cron.reminder.php` | Under-target mail |
| `cron.daily.php` | Invite and course cases |

`ajaxfunctions.php` `type` values:

| type | Behaviour |
|---|---|
| `getProjectsXML` | Projects for a client |
| `getProjectItemsXML` | Items for a project |
| `getTicketItemsXML` | Ticket items for a project item |
| `getTicketDetails`, `getTicketDetailsAdv` | Ticket detail payloads |
| `getProjectItemRate` | Rate text |
| `getTimesheetDetails` | Timesheet detail |
| `getClientTicketXML` | Tickets for a client |
| `SendResetEmail` | Forgot-password mail |
| `updateTicketDisplay` | Writes `tblTicketDisplay` |
| `handleProjectTemplates` | Template helper |
| `handlePublicHolidays` | Holiday helper |
| `getCourseTypeDefaults` | Course helper. Timesheet UI usage **Unknown** |

Response content type is JSON when the request `Content-Type` contains `application/json`. Otherwise XML, with raw `&` replaced by `&amp;`. `getProjectItemRate` and `getCourseTypeDefaults` echo and `die` before that shared footer.

The script does not construct `Nemo`. It starts a session via `system.php` and then runs the requested type. **Requires Confirmation** of whether session absence is checked inside each function.

## External integrations

### eIT

- Direction: timesheet to eIT, HTTP GET.
- URL: `https://eit.overdrive.co.za/ajax.functions.php`.
- Trigger: Copy To Eit, Bulk Copy To Eit, and a save path that passes a non-array id (see business rule BR-019).
- Auth: none in the query builder. **Unknown** whether the eIT host checks the caller.
- Success: JSON `success == true` and `BillingID`. Local `eitBillingID` updated when the id changes.
- Failure: message prefixed `WARNING!` plus `response["message"]`. Save still returns "Details Saved" plus that text.

### Convergence

- Direction: timesheet to Convergence, HTTP GET.
- URL: `https://convergence.overdrive.co.za/ajaxFunctions.php`.
- Trigger: `Timesheet::Save` and `AddTimesheetItem`.
- Payload: `type=SaveTime` plus task, email, notes, start, end, date, hours, last user.
- Success stores `TimeID`. If no task id resolves, the function returns null and does not call the URL.
- `get_url` in `system.functions.inc.php` performs the request. Timeout and TLS behaviour were not fully read.

### QuickBooks helper

- Direction: browser's PHP to `https://qb.overdrive.co.za/auth/`, POST field `timesheet` = urlencoded JSON.
- JSON includes `stream`, `app=timesheet`, `request_url`, `bill_id`.
- The code redirects to `json_decode` of the body. No HTTP status check.

`report.php` also sets `$WebSeviceURL = "https://qb.overdrive.co.za"`. The month-end comment (2016-04-05) says the process uses an ajax call. The remainder of that flow was not line-traced.

### Mail

`NemoEmail` loads a row by template name, substitutes placeholders, and sends. Deploy hook installs PHPMailer. Which class actually uses PHPMailer was not confirmed by reading `_nemo.email.cls.php` end to end. **Requires Confirmation.**

Templates named in code: `Reset Password`, `Project Item Over Run`, `TimeEntryReminder`, `TimeEntryReminderMorning`.

### FIM

`fim_2.0/fim.config_live.php`, `fim.config_qa.php`, `fim.config_dev.php` each connect to MySQL database `timesheet2` and email `grp-fim@overdrive.co.za`. Live base URL in the live file is `https://timesheet.overdrive.co.za/fim_2.0/`. `fim.cron.php` is included by the config. Table creation SQL is present and commented out.

FIM configs contain a database password. It is not reproduced here.

## Infrastructure and deployment

Production document root from `appspec.yml`:

`/var/www/html/timesheet.overdrive.co.za/`

Owner and group: `www-data`. OS: linux.

Hooks:

| Hook | Script | Run as |
|---|---|---|
| BeforeInstall | `scripts/install_dependencies` | root |
| ApplicationStop | `scripts/remove_archives.sh` | root |
| ApplicationStart | `scripts/start_server` | root |

`install_dependencies` changes to that directory and runs `composer require` for `phpmailer/phpmailer` and `aws/aws-sdk-php`. Apache install is commented out.

`start_server` runs `service apache2 start`. `scripts/stop_server` exists. It is not referenced by `appspec.yml`.

`remove_archives.sh` deletes CodeDeploy deployment-root directories other than the last successful install.

Active pipeline (`bitbucket-pipelines.yml`), branch `master` only:

- Image `amazon/aws-cli`.
- OIDC: `PROD_AWS_DEFAULT_REGION`, `PROD_AWS_ROLE_ARN`, Bitbucket OIDC token written to `web-identity-token`.
- `yum install zip`, zip the workspace to `/tmp/timesheet-app.zip`, `aws s3 cp` to `s3://$PROD_S3_BUCKET` with `bucket-owner-full-control`.

The pipeline does not call `codedeploy_deploy.py`. That Python file uploads a zip and creates a CodeDeploy revision using `APPLICATION_NAME`, `S3_BUCKET`, and boto3. **Unknown** whether anything outside this YAML still runs it.

`bitbucket-pipelines.yml.bak.ftp` is an older git-ftp push using `FTP_USERNAME_LIVE`, `FTP_PASSWORD_LIVE`, and `FTP_HOST_LIVE`. Not active.

Sonar key: `overdrive_timesheet_5ff9d63c-581f-496a-8808-f64ed976ecde`. No scan step in the pipeline.

## Auth

1. File-based PHP session.
2. Login writes `$_SESSION["USER"]`.
3. Password check is SHA-256 of salt plus password against `sysPassword`.
4. External PIN is a constant comparison in `login.php`.
5. `Action=Redirect` and `Action=RedirectKnowmore` load a user and then fall through into Login. Knowmore selects by email only.
6. Page view requires `sysSecurity.blnView`.
7. Lockout after five incorrect-password audits.

`login.php` `Validate()` does not check the form. Enforcement is server-side.

User admin no longer shows password inputs (comment 2020-02-17). Reset is a separate action.

## Background work

No queue worker and no `tblCronJob` consumer was found in the timesheet pages. `cron.daily.php` inserts `tblCronJob` rows for Notice, Apology, and workshop-invite cases. A processor for those rows was not found in this repository. **Unknown.**

`cron.reminder.php` sends mail itself. It does not insert `tblCronJob`.

Scheduling mechanism (crontab, systemd, or a hit to the URL) is **Unknown**.

## Storage

- Relational data in MySQL.
- Sessions on local disk under `SESSION/`.
- User pictures: `sysUser.strPicturePath` displayed from `ProfileImageDirAdmin`. That directory setting is data.
- Deploy artifact: zip on S3.
- No application S3 SDK usage was found in the page scripts that were read. The AWS SDK is pulled in at deploy time.

## Monitoring

- Login rows in `sysLogin`, and a two-day table on `index.php` when permitted.
- `strLastUser` and `dtLastEdit` on most domain tables.
- FIM email report.
- Sonar project key file only.
- PHP `display_errors` is on. Connection failures `print_r` a message object. The failed-connection branch does not print the password array (that `print_rr($DATABASE_SETTINGS)` is commented).

## Security notes

These are observations, not a penetration test.

1. Production and local database passwords are plaintext in `system.php` and in all three `fim_2.0/fim.config_*.php` files. The pipeline zips `*` and uploads that zip to S3, so the artifact contains those files.
2. `sysUser` still has `strPassword` and `strPasswordMD5` columns used by the redirect login actions.
3. The external login PIN is a source constant.
4. `ajaxfunctions.php` is not behind `Nemo::iniSecurity`. `SendResetEmail` reveals whether an email exists (`1` or `0`).
5. Many SQL statements interpolate request values. Some paths use `$xdb->qs`. Others concatenate `$_POST` and ids directly, including report update and the no-bill insert.
6. `display_errors` is enabled in `system.php` and the FIM configs.
7. Session files live under the document root. `.gitignore` ignores `SESSION`. Whether the web server blocks that directory is **Unknown**.
8. eIT and Convergence calls are unauthenticated GETs with notes and hours on the query string.
9. QuickBooks customer name is hardcoded, which is a data-integrity issue as well as a behaviour issue.
10. `CopyToEit` from `Save` passes a scalar into a function that only loops arrays (BR-019).

## Development and testing

README section "Test" is empty. No PHPUnit, no CI test step, no fixture database.

Local entry depends on the HTTP host matching a key in `$DATABASE_SETTINGS`. `timesheets.php8` is the non-production key present in code.

`zSandBox.php` is an ad-hoc page.

Editor files `Timesheet2.sublime-project` and `Timesheet2.sublime-workspace` are committed.

Old copies kept beside live files:

- `includes/client.totals.rpt.php.old`
- `includes/client.totals.rpt.php20200325`
- `includes/overview.cls.php20230724`

## Configuration inventory

| Concern | Where | In git |
|---|---|---|
| DB host, user, password, database, session path | `system.php` | Yes, including secrets |
| FIM DB and mail | `fim_2.0/fim.config_*.php` | Yes, including secrets |
| Brand, SMTP, base rate, password policy, page size | `sysSettings` rows | No |
| Menu and permissions | `sysMenu*`, `sysSecurity*` | No |
| AWS region, role, bucket | Bitbucket deployment variables | No |
| FTP credentials | only in the `.bak` pipeline as variable names | Values not in git |

## Open technical questions

- Live crontab for both cron scripts.
- Whether CodeDeploy is still wired to the S3 zip.
- PHP version and whether `timesheets.php8` is still used.
- Whether `blnNew`, `blnSave`, `blnDelete`, and `blnSpecial` are enforced.
- Whether `booking.php` exists outside this commit.
- Whether course, voucher, and `tblCronJob` tables still exist on `timesheet2`.
- Full column list for each table (needs a schema dump).
- Git history before `3baa25ba` (this inspection used a depth-1 clone; that commit is also the tip recorded when the onboarding issue was created).