301 lines
8.7 KiB
Markdown
301 lines
8.7 KiB
Markdown
# Project Handoff
|
|
|
|
This document summarizes the current state of the spreadsheet importer so it can be copied into a new project or used as a reference for the next stage.
|
|
|
|
## What This App Does
|
|
|
|
This is a plain PHP and Bootstrap web app that:
|
|
|
|
- accepts an Excel upload
|
|
- validates the workbook headers
|
|
- ignores the `Revenue Type` column
|
|
- normalizes and stores imported row data in PostgreSQL
|
|
- calculates stage 1 derived fields during import
|
|
- wipes previous imported data before each new import
|
|
- shows a 20-row preview on the home page
|
|
- provides a full DataTables page for the latest imported batch
|
|
|
|
The app is structured so future import stages can add more rules without rewriting the UI or database layer.
|
|
|
|
## Move Notes
|
|
|
|
The codebase is largely relocatable because internal includes and asset URLs are built from the project root.
|
|
|
|
Before moving the project into a new folder:
|
|
|
|
- set `APP_URL` to the new base URL or folder path
|
|
- keep `DATABASE_URL` or the values in `config.php` aligned with the target environment
|
|
- rerun `composer install` if `vendor/` is not copied with the project
|
|
- confirm the web server is still pointing at `public/` as the document root
|
|
|
|
The only folder-sensitive setting in the app itself is the redirect base URL used after imports and clears.
|
|
|
|
## Current Stack
|
|
|
|
- PHP
|
|
- Bootstrap 5
|
|
- PostgreSQL
|
|
- Composer for the spreadsheet library
|
|
- OpenSpout for reading Excel files
|
|
|
|
## Important Files
|
|
|
|
- `config.php`
|
|
- Root config file for app name, URL, and PostgreSQL connection settings.
|
|
- This replaced `.env`-style configuration.
|
|
|
|
- `bootstrap.php`
|
|
- Starts the session.
|
|
- Loads Composer's autoloader if present.
|
|
- Requires the app files manually.
|
|
- Exposes the `app_config()` helper for config lookup.
|
|
|
|
- `index.php`
|
|
- Root entrypoint.
|
|
- Loads `public/index.php` so the app can be reached from the project root.
|
|
|
|
- `public/index.php`
|
|
- Main web entrypoint.
|
|
- Creates the controller and renders a friendly setup error page if startup fails.
|
|
|
|
- `public/assets/app.js`
|
|
- Handles file upload progress.
|
|
- Drives the chunked import loop with repeated `process-chunk` requests.
|
|
- Handles the clear-data button.
|
|
- Reloads the page after import or clear completes.
|
|
|
|
- `public/assets/styles.css`
|
|
- Custom Bootstrap styling and layout.
|
|
|
|
- `templates/index.php`
|
|
- Home page with upload form, status messages, and a 20-row preview table.
|
|
|
|
- `templates/table.php`
|
|
- Full-table page for the latest imported batch.
|
|
- DataTables server-side search and paging.
|
|
- ColumnControl header buttons and header filters.
|
|
|
|
- `templates/partials/navbar.php`
|
|
- Shared navigation between the preview and full-table pages.
|
|
|
|
- `bin/migrate.php`
|
|
- Creates the PostgreSQL schema.
|
|
|
|
- `app/Database.php`
|
|
- Builds the PDO connection from `config.php`.
|
|
|
|
- `app/Http/AppController.php`
|
|
- Coordinates upload, clear, render, and JSON responses.
|
|
|
|
- `app/Import/SpreadsheetReader.php`
|
|
- Streams workbook data with OpenSpout.
|
|
- Builds a temporary chunk cache from the uploaded file.
|
|
- Detects the real header row.
|
|
- Ignores `Revenue Type`.
|
|
- Normalizes cell values.
|
|
|
|
- `app/Import/SpreadsheetRowProcessor.php`
|
|
- Applies all stage 1 calculated field rules.
|
|
|
|
- `app/Import/ImportService.php`
|
|
- Orchestrates validation, clearing, processing, and storing.
|
|
|
|
- `app/Import/ImportRepository.php`
|
|
- Handles PostgreSQL inserts, truncation, counting, and pagination.
|
|
|
|
## Database Design
|
|
|
|
The schema is intentionally flexible and uses JSONB for future growth.
|
|
|
|
### `import_batches`
|
|
|
|
Stores metadata for each import run:
|
|
|
|
- source filename
|
|
- original headers
|
|
- calculated headers
|
|
- row count
|
|
- warning count
|
|
- created timestamp
|
|
|
|
### `import_rows`
|
|
|
|
Stores each imported row:
|
|
|
|
- batch id
|
|
- source row number
|
|
- original row data as JSONB
|
|
- normalized row data as JSONB
|
|
- calculated row data as JSONB
|
|
- merged row data as JSONB
|
|
|
|
The row-level JSONB storage makes it easier to add more calculated fields and import stages later without changing the schema every time.
|
|
|
|
## Import Flow
|
|
|
|
The current flow is:
|
|
|
|
1. User uploads an Excel workbook.
|
|
2. The app validates the file extension.
|
|
3. The upload is stored in a temp file and an import token is returned.
|
|
4. The workbook is streamed once into a temporary chunk cache.
|
|
5. The browser repeatedly calls `action=process-chunk` with that token.
|
|
6. Required columns are checked by header name.
|
|
7. Existing imported data is wiped.
|
|
8. A new batch record is created.
|
|
9. Each cached row is normalized.
|
|
10. Derived fields are calculated.
|
|
11. The row is inserted into PostgreSQL.
|
|
12. The home page reloads and shows a 20-row preview table.
|
|
13. The full table page provides server-side search and pagination for the latest batch.
|
|
|
|
## Validation Rules
|
|
|
|
Required columns:
|
|
|
|
- `Accrual Category`
|
|
- `Local Selling Company (S/T)`
|
|
- `Contracting Party`
|
|
- `Rights Holder Liable Flag`
|
|
- `Global Transfer Pricing Flag`
|
|
- `Domestic/ Foreign Flag`
|
|
- `Ownership Type`
|
|
|
|
The app also:
|
|
|
|
- handles blank and null values safely
|
|
- preserves formatted text values from Excel
|
|
- treats numeric-looking values consistently
|
|
- shows readable import errors
|
|
|
|
## Stage 1 Calculated Fields
|
|
|
|
### 1. `Actual/Accrual`
|
|
|
|
Rule:
|
|
|
|
- If `Accrual Category` equals `##`, return `Actual`
|
|
- Otherwise return `Accrual`
|
|
|
|
### 2. `US/ex-US sale`
|
|
|
|
Rule:
|
|
|
|
- If `Local Selling Company (S/T)` is between `1700` and `1809` inclusive, return `US`
|
|
- Otherwise return `ex-US`
|
|
|
|
### 3. `Contracting Party Filtered`
|
|
|
|
Rule:
|
|
|
|
- If `Contracting Party` equals `1756`, return `1756`
|
|
- If `Contracting Party` equals `1100`, return `1100`
|
|
- If `Contracting Party` equals `1902`, return `1902`
|
|
- Otherwise return `LOCAL`
|
|
|
|
### 4. `String`
|
|
|
|
Rule:
|
|
|
|
Concatenate, in this exact order with no separator:
|
|
|
|
- `Contracting Party Filtered`
|
|
- `Rights Holder Liable Flag`
|
|
- `Global Transfer Pricing Flag`
|
|
- `Domestic/ Foreign Flag`
|
|
- `Ownership Type`
|
|
|
|
Example:
|
|
|
|
- `1756YYFDI`
|
|
|
|
### 5. `2110 Applicable X201`
|
|
|
|
Rule:
|
|
|
|
- If the `String` value is in the approved list, return `X`
|
|
- Otherwise return blank
|
|
|
|
### 6. `Class`
|
|
|
|
Rules are applied top to bottom and the first match wins:
|
|
|
|
- `A` if `Contracting Party = 1902`
|
|
- `B` if `Contracting Party = 1756`, `Rights Holder Liable Flag = Y`, and `Local Selling Company (S/T)` is not between `1700` and `1808` inclusive
|
|
- `C` if `Contracting Party = 1756`, `Local Selling Company (S/T)` is between `1700` and `1808` inclusive, and `2110 Applicable X201 = X`
|
|
- `D` if `Contracting Party = 1756`, `Local Selling Company (S/T)` is between `1700` and `1808` inclusive, and `2110 Applicable X201` is not `X`
|
|
- `E` if `Contracting Party = 1100` and `Local Selling Company (S/T)` is not between `1700` and `1808` inclusive
|
|
- `F` if `Contracting Party = 1100` and `Local Selling Company (S/T)` is between `1700` and `1808` inclusive
|
|
- `G` if `Contracting Party Filtered = LOCAL`
|
|
- `H` if `Contracting Party = 1756`, `Rights Holder Liable Flag = N`, `Global Transfer Pricing Flag = Y`, and `Local Selling Company (S/T)` is not between `1700` and `1808` inclusive
|
|
|
|
## UI
|
|
|
|
The Bootstrap UI includes:
|
|
|
|
- upload form
|
|
- navbar for preview/full-table navigation
|
|
- progress bar
|
|
- success/error messages
|
|
- imported row count
|
|
- clear imported data button
|
|
- 20-row data preview table
|
|
- current batch summary
|
|
- full table page with search and pagination
|
|
|
|
The app uses relative asset paths so it works from either:
|
|
|
|
- the project root, or
|
|
- the `public/` directory
|
|
|
|
## How To Set This Up In A New Project
|
|
|
|
1. Copy these files/folders into the new project:
|
|
- `config.php`
|
|
- `bootstrap.php`
|
|
- `index.php`
|
|
- `bin/`
|
|
- `app/`
|
|
- `public/`
|
|
- `templates/`
|
|
- `composer.json`
|
|
- `composer.lock`
|
|
|
|
2. Install dependencies:
|
|
```bash
|
|
composer install
|
|
```
|
|
|
|
3. Update `config.php` with the new server's database settings.
|
|
|
|
4. Run migrations:
|
|
```bash
|
|
composer run migrate
|
|
```
|
|
|
|
5. Configure the web server to point at the project root or `public/`.
|
|
|
|
6. Open the app in a browser and upload the workbook.
|
|
|
|
## Notes For Future Stages
|
|
|
|
- Keep the processing logic out of the template.
|
|
- Add new calculated fields in `SpreadsheetRowProcessor`.
|
|
- Add new schema fields only if JSONB is no longer sufficient.
|
|
- If later stages need multiple import histories, stop truncating and switch to batch filtering in the UI.
|
|
- If the workbook structure changes, update header detection in `SpreadsheetReader`.
|
|
|
|
## Current Caveats
|
|
|
|
- PostgreSQL credentials must be correct in `config.php`.
|
|
- The app currently expects the database to exist before migration.
|
|
- The import path uses PhpSpreadsheet, so Composer remains required.
|
|
- Large workbooks should continue to use pagination in the preview table.
|
|
|
|
## Working Assumptions
|
|
|
|
- The header names remain stable.
|
|
- The example workbook is representative of the real import structure.
|
|
- `Revenue Type` should always be ignored.
|
|
- Future stages will extend the current batch/import model rather than replace it.
|