Warner-Vistex-Automater/PROJECT_HANDOFF.md

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:

   composer install
   

3. Update config.php with the new server's database settings.

4. Run migrations:

   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.