> **SUPERSEDED — HISTORICAL RECORD ONLY (archived 2026-07-18).** This document is part of the original "superpowers" wayleave build, whose design diverged from the projects-module patterns it should have mirrored. Do NOT use it as a pattern source for wayleave or any new module. The wayleave module is being realigned to the projects module (meta-driven fields, OpenLayers, shared JS) — see CLAUDE.md. # Wayleave Module Implementation Plan > **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking. **Goal:** Build a full wayleave agreement management module for GeoLynx mirroring the structure of the projects module, with a flexible three-source coverage model (polygon / direct UPRN / stocklist) and an approval workflow for polygon-derived UPRN changes. **Architecture:** PHP 7+ with PDO against PostgreSQL+PostGIS. Data stored in a new set of tables under the existing empty `wayleave` schema. Geometry stored in SRID 27700 (British National Grid), transformed to 4326 for Leaflet rendering. Polygon → UPRN resolution is a snapshot-with-approval-workflow tracked in `wayleave.agreement_polygon_uprns`. Direct UPRNs and stocklist UPRNs resolve live at query time. Frontend is vanilla JS + jQuery + Bootstrap 5 + Tabulator + Leaflet + Leaflet-Geoman, matching the project editor patterns. **Tech Stack:** PHP 7+, PDO (pdo_pgsql), PostgreSQL 13+ with PostGIS, vanilla JS + jQuery 3.6, Bootstrap 5, Tabulator.js, Leaflet.js, Leaflet-Geoman, Chart.js. **Spec reference:** `docs/superpowers/specs/2026-04-20-wayleave-module-design.md` **Notes for the engineer:** - **Phase 0 (database setup) is already complete.** The four SQL files under `sql/wayleave_*.sql` have been written to disk and are run manually by the user against the `netplanner` database BEFORE this plan executes. Do not run them as part of any task. Do not modify them unless a later task surfaces a schema error — if that happens, stop and report rather than silently altering a pre-applied DDL file. - The repo is **not** a git repository — ignore any `git commit` guidance. Do not run `git` commands. - There is **no test framework**. Verification steps below are manual (SQL round-trip, AJAX round-trip in browser, tail the PHP error log). - `www/fn/db.php` defines `$hostname`, `$dbname`, `$username`, `$password`. Every endpoint includes `db.php` and opens its own `PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password")`. - All endpoints must `require_once __DIR__ . '/global_vars.php'` / `global_functions.php` / `db.php` and then `include 'login_check.php'; $check = loginCheck('func'); if($check == false){ echo 'no access'; exit; } else { $userID = $_SESSION['id']; }` — this is the universal auth preamble. See `www/fn/project_journal_save.php:1-19` for the canonical form. - `userID` is always `$_SESSION['id']` after the auth preamble. - Every save must write a journal row and (for static field changes) a history row. - The existing `projects` module is your template — when in doubt, read `www/fn/project_load.php`, `www/fn/project_save.php`, `www/js/project_edit_v2.js` and port the pattern. Do not invent a new pattern. --- ## File Structure ### Database (pre-applied by the user in Phase 0 — do **not** re-run) - `sql/wayleave_01_core.sql` — agreements, agreements_history, agreement_status (+6 status rows) - `sql/wayleave_02_coverage.sql` — agreement_uprns, agreement_stocklists, agreement_polygons, agreement_polygon_uprns (approval workflow) - `sql/wayleave_03_dynamic_fields.sql` — field sections/categories/sub_categories, agreement_fields, dropdown_options, 5× value tables + 5× history, +3 seed dynamic fields (`wl_annual_fee`, `wl_landowner_solicitor`, `wl_access_date`) - `sql/wayleave_04_supporting.sql` — agreement_journal, agreement_releases, agreement_landregistry - `sql/wayleave_05_file_uploads_integration.sql` — extends shared `public.file_uploads` with an `agreement_id` column + creates `wayleave.vw_file_uploads` view filtered on `entity='wayleave'`. Files themselves are NOT stored in a wayleave-specific table — they share `public.file_uploads` with the projects/stocklists/accounts/opportunity modules, matching the existing `vw_file_uploads` view pattern (`accounts.vw_file_uploads`, `projects.vw_file_uploads`, `stocklists.vw_file_uploads`). ### Backend (`www/fn/`) - `wayleave_list_load.php` — paginated list for Tabulator - `wayleave_load.php` — load single agreement (static, dynamic, audit log, counts) - `wayleave_save.php` — save main details + dynamic fields + journal/history - `wayleave_journal_save.php` — free-text journal entries - `wayleave_field_values_load.php` — returns dynamic field values for one agreement - `wayleave_field_values_save.php` — upserts one dynamic field value + history - `wayleave_coverage_polygon_save.php` — add/update/delete polygon + run spatial join - `wayleave_coverage_polygon_uprns_load.php` — pending add/remove UPRNs - `wayleave_coverage_polygon_uprns_approve.php` — approve / reject polygon UPRN changes - `wayleave_coverage_uprn_save.php` — add/remove direct UPRNs - `wayleave_coverage_stocklist_save.php` — attach/detach stocklists - `wayleave_premises_load.php` — resolved premise union - `wayleave_files_load.php` — wayleave-specific wrapper over `wayleave.vw_file_uploads`. Uploads and downloads go to the **shared** `www/fn/file_upload.php` and `www/fn/serve_file.php` endpoints (both extended in Task 10 to whitelist `entity=wayleave`). There is **no** `wayleave_file_upload.php`. - `wayleave_releases_load.php` / `wayleave_releases_save.php` — release tracking - `wayleave_projects_load.php` — linked projects by UPRN overlap - `wayleave_landreg_load.php` / `wayleave_landreg_save.php` — linked land registry accounts - `wayleave_map_load.php` — GeoJSON bundle for map tab ### Frontend - `www/html/wayleave.php` — list page shell (rewrite) - `www/html/wayleave_edit.php` — editor shell (rewrite) - `www/js/wayleave_list.js` — list logic - `www/js/wayleave_edit.js` — editor logic (all tabs) - `www/css/wayleave_list.css` — list styles - `www/css/wayleave_edit.css` — editor styles ### Router - `www/fn/global_functions.php:680-694` — update `wayleave` + `wayleaveedit` js/css entries - `www/fn/global_functions.php:100-140` — extend `getItemCompanyId` tableMap with `wayleave` - `www/fn/global_functions.php` — add `checkWayleaveEditField`, `getWayleaveEditCurrentValue` (value-reader, NOT a change-detector — see Task 1 Step 3), `checkWayleaveEditCurrentValueStatic` --- ## Phase 0: Database Setup — MANUAL (complete before executing any task below) > **Who runs this?** The **user**, not the plan executor. The five SQL files listed below have already been written to `sql/`. The executor must not run them, must not modify them, and must not ALTER TABLE anything in the `wayleave` schema (or `public.file_uploads`) as part of a task. If any later task hits a schema mismatch, stop and surface the error instead of patching the DDL. **Prereq:** the `wayleave` schema already exists (defined in `sql/geolynx_ddl.sql`). No `CREATE SCHEMA` needed. **Apply in this order:** ```bash psql -U postgres -d netplanner -f sql/wayleave_01_core.sql psql -U postgres -d netplanner -f sql/wayleave_02_coverage.sql psql -U postgres -d netplanner -f sql/wayleave_03_dynamic_fields.sql psql -U postgres -d netplanner -f sql/wayleave_04_supporting.sql psql -U postgres -d netplanner -f sql/wayleave_05_file_uploads_integration.sql ``` **What each file creates:** | File | Objects | |---|---| | `sql/wayleave_01_core.sql` | `agreement_status` (seed: Draft, In Progress, On Hold, Signed, Complete, Cancelled), `agreements` (with `geom geometry(MultiPolygon,27700)`, `parent_agreement_id` self-ref, multi-tenant `company_id`), `agreements_history` (full column mirror + `history_action` / `history_field` / `history_old_value` / `history_new_value` / `history_datetime` / `history_user`) | | `sql/wayleave_02_coverage.sql` | `agreement_uprns` (direct UPRNs), `agreement_stocklists` (attached stocklists), `agreement_polygons` (`geometry(Polygon,27700)` — multiple per agreement), `agreement_polygon_uprns` (4-state workflow via `is_assigned` × `is_approved`) | | `sql/wayleave_03_dynamic_fields.sql` | `agreement_field_sections` (seed: Commercial, Legal, Delivery), `agreement_field_category` (seed: General per section), `agreement_field_sub_category`, `agreement_fields` (seed: `wl_annual_fee` numeric, `wl_landowner_solicitor` text, `wl_access_date` date), `agreement_field_dropdown_options`, `agreement_field_values_{text,int,numeric,date,boolean}` + `_history` mirrors | | `sql/wayleave_04_supporting.sql` | `agreement_journal` (with `is_system` flag), `agreement_releases` (per-UPRN release tracking), `agreement_landregistry` (link to Land Registry titles) — **no** wayleave-specific file upload table; see next file | | `sql/wayleave_05_file_uploads_integration.sql` | Adds `agreement_id integer` column to the shared `public.file_uploads` table; creates `wayleave.vw_file_uploads` view that filters `entity='wayleave'` and joins `users.users` for the uploader display name. Matches `accounts.vw_file_uploads` / `projects.vw_file_uploads` / `stocklists.vw_file_uploads` shape. | **Verification (user runs, then confirms to executor):** ```bash psql -U postgres -d netplanner -c "\dt wayleave.*" psql -U postgres -d netplanner -c "SELECT id, description FROM wayleave.agreement_status ORDER BY display_order;" psql -U postgres -d netplanner -c "SELECT field_id, field_form_id, field_data_type, field_name FROM wayleave.agreement_fields ORDER BY field_id;" psql -U postgres -d netplanner -c "\d wayleave.agreement_polygon_uprns" psql -U postgres -d netplanner -c "\d public.file_uploads" | grep -i agreement_id psql -U postgres -d netplanner -c "\dv wayleave.*" ``` Expected: - First query lists ~19 tables under `wayleave.*` including `agreements`, `agreements_history`, `agreement_status`, `agreement_uprns`, `agreement_stocklists`, `agreement_polygons`, `agreement_polygon_uprns`, `agreement_journal`, `agreement_releases`, `agreement_landregistry`, `agreement_fields`, `agreement_field_sections`, `agreement_field_category`, `agreement_field_sub_category`, `agreement_field_dropdown_options`, and the 5 value + 5 history tables. **No** `agreement_file_uploads` — files are on `public.file_uploads`. - Second query: 6 status rows (`Draft` → `Cancelled`). - Third query: 3 seed dynamic fields (`wl_annual_fee`, `wl_landowner_solicitor`, `wl_access_date`). - Fourth query: shows `agreement_polygon_uprns` with columns `id`, `agreement_id`, `uprn`, `is_assigned`, `is_approved`, `actioned_user_id`, `action_datetime`, `approved_user_id`, `approved_datetime`. - Fifth query: one line containing `agreement_id | integer` — confirms the shared table was extended. - Sixth query: lists `wayleave.vw_file_uploads` (the per-schema files view). **Gate:** before starting Task 1, the user must confirm all six verification queries returned the expected results. If an object is missing, the user should re-run the file that defines it — **not** the executor. --- ## Task 1: Router Registration + Helper Functions **Files:** - Modify: `www/fn/global_functions.php` (three discrete edits) - [ ] **Step 1: Update router for `wayleave` and `wayleaveedit`** Find the `'wayleave' => [...]` and `'wayleaveedit' => [...]` entries (around line 680) and set their `css` / `js` arrays. Replace: ```php 'wayleave' => [ 'head_nav' => ['html/head_nav_default.php'], 'nav_html' => ['html/nav_main_html.php'], 'html' => ['html/wayleave.php'], 'css' => [''], 'js' => [''], 'version' => ['1'] ], 'wayleaveedit' => [ 'head_nav' => ['html/head_nav_default.php'], 'nav_html' => ['html/nav_main_html.php'], 'html' => ['html/wayleave_edit.php'], 'css' => [''], 'js' => [''], 'version' => ['1'] ], ``` with: ```php 'wayleave' => [ 'head_nav' => ['html/head_nav_default.php'], 'nav_html' => ['html/nav_main_html.php'], 'html' => ['html/wayleave.php'], 'css' => ['css/wayleave_list.css'], 'js' => ['js/wayleave_list.js'], 'version' => ['1'] ], 'wayleaveedit' => [ 'head_nav' => ['html/head_nav_default.php'], 'nav_html' => ['html/nav_main_html.php'], 'html' => ['html/wayleave_edit.php'], 'css' => ['css/wayleave_edit.css'], 'js' => ['js/wayleave_edit.js'], 'version' => ['1'] ], ``` - [ ] **Step 2: Extend `getItemCompanyId` with a `wayleave` case** Open `www/fn/global_functions.php`, locate the `getItemCompanyId($dbh, $itemType, $itemID)` function (around line 100-115). Its `$tableMap` currently handles `'project'`, `'account'`, `'stocklist'`. The actual file uses a flat two-element array (`['schema.table', 'pk_column']`) — **not** a keyed associative array. Add a `'wayleave'` entry so this: ```php $tableMap = [ 'project' => ['projects.projects', 'project_id'], 'account' => ['accounts.accounts', 'account_id'], 'stocklist' => ['stocklists.stocklists', 'stocklist_id'], ]; ``` becomes: ```php $tableMap = [ 'project' => ['projects.projects', 'project_id'], 'account' => ['accounts.accounts', 'account_id'], 'stocklist' => ['stocklists.stocklists', 'stocklist_id'], 'wayleave' => ['wayleave.agreements', 'agreement_id'], ]; ``` Preserve existing formatting; only the new `'wayleave'` line is added. - [ ] **Step 3: Add three wayleave field helpers** Append the following functions to the end of `www/fn/global_functions.php` (before the closing `?>` if present, otherwise at end of file). These mirror `checkProjectEditField` / `checkProjectEditCurrentValue` / `checkProjectEditCurrentValueStatic` but against the `wayleave` schema. ```php /** * Look up a dynamic wayleave agreement field by its form/POST key. * Returns ['field_data_type' => ..., 'field_id' => ...] or false. */ function checkWayleaveEditField($dbh, $field_form_id) { $sql = "SELECT field_data_type, field_id FROM wayleave.agreement_fields WHERE field_form_id = :field_form_id AND field_active = true"; $stmt = $dbh->prepare($sql); $stmt->bindValue(':field_form_id', $field_form_id); $stmt->execute(); return $stmt->fetch(PDO::FETCH_ASSOC); } /** * Return current dynamic field value for an agreement, given a known data type. * Value-reader (NOT a change-detector): unlike checkProjectEditCurrentValue / * checkAccountEditCurrentValue / checkStocklistEditCurrentValue — which take an incoming * $value and return a boolean "did it change?" — this helper just returns the existing * DB value (or null). Callers do their own comparison. */ function getWayleaveEditCurrentValue($dbh, $agreement_id, $field_id, $field_data_type) { switch ($field_data_type) { case 'text': $table = 'wayleave.agreement_field_values_text'; break; case 'int': $table = 'wayleave.agreement_field_values_int'; break; case 'numeric': $table = 'wayleave.agreement_field_values_numeric'; break; case 'date': $table = 'wayleave.agreement_field_values_date'; break; case 'boolean': $table = 'wayleave.agreement_field_values_boolean'; break; default: return null; } $sql = "SELECT field_value FROM $table WHERE agreement_id = :agreement_id AND field_id = :field_id"; $stmt = $dbh->prepare($sql); $stmt->bindValue(':agreement_id', $agreement_id, PDO::PARAM_INT); $stmt->bindValue(':field_id', $field_id, PDO::PARAM_INT); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); return $row ? $row['field_value'] : null; } /** * Return a static wayleave.agreements column value for the given agreement. * $column is trusted — only called with hardcoded allow-listed column names. */ function checkWayleaveEditCurrentValueStatic($dbh, $agreement_id, $column) { $allowed = [ 'agreement_name','agreement_reference','agreement_status_id','agreement_type', 'wayleave_team','bd_manager','ecd_date','signed_date','parent_agreement_id', 'account_id' ]; if (!in_array($column, $allowed, true)) return null; $sql = "SELECT $column AS v FROM wayleave.agreements WHERE agreement_id = :aid"; $stmt = $dbh->prepare($sql); $stmt->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); return $row ? $row['v'] : null; } ``` - [ ] **Step 4: Verify PHP still parses** Run: ```bash php -l "www/fn/global_functions.php" ``` Expected: `No syntax errors detected in www/fn/global_functions.php`. - [ ] **Step 5: Verify router wiring** Open `http://localhost/netplanner/?do=wayleave` in the browser; open devtools Network tab. Expected: request returns 200, loads `js/wayleave_list.js` 404 (the file doesn't exist yet — that's fine), and the existing placeholder `wayleave.php` renders. The 404 confirms the router is now pointing at the new JS path. --- ## Task 2: Backend — `wayleave_list_load.php` **Files:** - Create: `www/fn/wayleave_list_load.php` This endpoint returns the rows for the Tabulator list view, including resolved premise counts (polygon-approved + direct + stocklist UPRNs, de-duplicated). - [ ] **Step 1: Create the endpoint** ```php setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $search = isset($_POST['search']) ? trim($_POST['search']) : ''; $status = isset($_POST['status']) ? trim($_POST['status']) : ''; $team = isset($_POST['team']) ? trim($_POST['team']) : ''; // Resolved premise count per agreement = union of: // - active polygon UPRNs (is_assigned=true AND is_approved=true) // - direct UPRNs (is_deleted=false) // - stocklist UPRNs (from stocklists.stocklist_premises where the linked stocklists are attached) // Note: stocklists.stocklist_premises has NO is_deleted column (older legacy schema) — // do not filter on it here. $sql = " WITH poly_uprns AS ( SELECT agreement_id, uprn FROM wayleave.agreement_polygon_uprns WHERE is_assigned = true AND is_approved = true ), direct_uprns AS ( SELECT agreement_id, uprn FROM wayleave.agreement_uprns WHERE is_deleted = false ), stocklist_uprns AS ( SELECT ast.agreement_id, sp.uprn FROM wayleave.agreement_stocklists ast JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ast.stocklist_id WHERE ast.is_deleted = false ), all_uprns AS ( SELECT agreement_id, uprn FROM poly_uprns UNION SELECT agreement_id, uprn FROM direct_uprns UNION SELECT agreement_id, uprn FROM stocklist_uprns ) SELECT a.agreement_id, a.agreement_name, a.agreement_reference, a.agreement_type, a.wayleave_team, a.bd_manager, a.ecd_date, a.signed_date, s.description AS status, (SELECT COUNT(DISTINCT uprn) FROM all_uprns u WHERE u.agreement_id = a.agreement_id) AS premise_count FROM wayleave.agreements a LEFT JOIN wayleave.agreement_status s ON s.id = a.agreement_status_id WHERE a.is_deleted = false AND (:search1 = '' OR a.agreement_name ILIKE '%' || :search2 || '%' OR a.agreement_reference ILIKE '%' || :search3 || '%') AND (:status = '' OR s.description = :status) AND (:team = '' OR a.wayleave_team = :team) ORDER BY a.agreement_id DESC"; try { $stmt = $dbh->prepare($sql); $stmt->bindValue(':search1', $search); $stmt->bindValue(':search2', $search); $stmt->bindValue(':search3', $search); $stmt->bindValue(':status', $status); $stmt->bindValue(':team', $team); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode(['success' => true, 'data' => $rows]); } catch (PDOException $e) { echo json_encode(['success' => false, 'error' => $e->getMessage()]); } ``` - [ ] **Step 2: Syntax check** Run: `php -l "www/fn/wayleave_list_load.php"` Expected: `No syntax errors detected`. - [ ] **Step 3: Smoke-test with a browser POST** Insert one agreement first so the query has data: ```bash psql -U postgres -d netplanner -c "INSERT INTO wayleave.agreements (agreement_name, agreement_status_id, created_user, modified_user) VALUES ('Test Agreement 1', (SELECT id FROM wayleave.agreement_status WHERE description='Draft'), 1, 1);" ``` Then hit the endpoint via the browser once logged in: `http://localhost/netplanner/www/fn/wayleave_list_load.php`. Expected JSON: `{"success":true,"data":[{"agreement_id":1,"agreement_name":"Test Agreement 1",...,"premise_count":0}]}`. --- ## Task 3: Backend — `wayleave_load.php` **Files:** - Create: `www/fn/wayleave_load.php` Loads a single agreement: static fields, dynamic field definitions + current values, journal, counts. - [ ] **Step 1: Create the endpoint** ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { // 1. Static agreement fields $sqlA = "SELECT a.*, s.description AS status_description FROM wayleave.agreements a LEFT JOIN wayleave.agreement_status s ON s.id = a.agreement_status_id WHERE a.agreement_id = :aid AND a.is_deleted = false"; $stmt = $dbh->prepare($sqlA); $stmt->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $agreement = $stmt->fetch(PDO::FETCH_ASSOC); if (!$agreement) { echo json_encode(['success'=>false,'error'=>'not found']); exit; } // 2. Status options $stmt = $dbh->query("SELECT id, description FROM wayleave.agreement_status WHERE is_active=true ORDER BY display_order"); $statuses = $stmt->fetchAll(PDO::FETCH_ASSOC); // 3. Dynamic field definitions with dropdown options $sqlF = "SELECT f.field_id, f.field_form_id, f.field_name, f.field_data_type, f.field_input_type, f.field_required, f.field_display_order, f.field_spacer_after, s.section_name, s.display_order AS section_order, c.category_name, c.display_order AS category_order, sc.sub_category_name, sc.display_order AS sub_order FROM wayleave.agreement_fields f LEFT JOIN wayleave.agreement_field_sections s ON s.id = f.field_section LEFT JOIN wayleave.agreement_field_category c ON c.id = f.field_category LEFT JOIN wayleave.agreement_field_sub_category sc ON sc.id = f.field_sub_category WHERE f.field_active = true ORDER BY s.display_order, c.display_order, sc.display_order, f.field_display_order"; $fields = $dbh->query($sqlF)->fetchAll(PDO::FETCH_ASSOC); $sqlOpt = "SELECT field_id, option_value, option_label FROM wayleave.agreement_field_dropdown_options WHERE is_active = true ORDER BY field_id, display_order"; $options = $dbh->query($sqlOpt)->fetchAll(PDO::FETCH_ASSOC); $optionsByField = []; foreach ($options as $o) { $optionsByField[$o['field_id']][] = ['value'=>$o['option_value'],'label'=>$o['option_label']]; } foreach ($fields as &$f) { $f['dropdown_options'] = $optionsByField[$f['field_id']] ?? []; } unset($f); // 4. Current dynamic field values (union across type tables, wrapped in CTE so the // placeholder appears once — pdo_pgsql does not allow reusing a named placeholder // within one prepared statement). $sqlV = " WITH v AS ( SELECT agreement_id, field_id, field_value::text AS field_value, 'text' AS dtype FROM wayleave.agreement_field_values_text UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'int' AS dtype FROM wayleave.agreement_field_values_int UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'numeric' AS dtype FROM wayleave.agreement_field_values_numeric UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'date' AS dtype FROM wayleave.agreement_field_values_date UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'boolean' AS dtype FROM wayleave.agreement_field_values_boolean ) SELECT field_id, field_value, dtype FROM v WHERE agreement_id = :aid"; $stmt = $dbh->prepare($sqlV); $stmt->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $valRows = $stmt->fetchAll(PDO::FETCH_ASSOC); $values = []; foreach ($valRows as $v) { $values[$v['field_id']] = $v['field_value']; } // 5. Counts — pdo_pgsql rejects repeated named placeholders in one prepared statement, // so suffix each :aidN uniquely. File count reads the shared-table view // (wayleave.vw_file_uploads — filters entity='wayleave' AND file_is_deleted=false). $sqlCounts = "SELECT (SELECT COUNT(*) FROM wayleave.agreement_polygons WHERE agreement_id=:aid1 AND is_deleted=false) AS polygon_count, (SELECT COUNT(*) FROM wayleave.agreement_uprns WHERE agreement_id=:aid2 AND is_deleted=false) AS direct_uprn_count, (SELECT COUNT(*) FROM wayleave.agreement_stocklists WHERE agreement_id=:aid3 AND is_deleted=false) AS stocklist_count, (SELECT COUNT(*) FROM wayleave.vw_file_uploads WHERE agreement_id=:aid4) AS file_count, (SELECT COUNT(*) FROM wayleave.agreement_releases WHERE agreement_id=:aid5 AND is_deleted=false) AS release_count, (SELECT COUNT(*) FROM wayleave.agreement_polygon_uprns WHERE agreement_id=:aid6 AND is_assigned=true AND is_approved=false) AS pending_add_count, (SELECT COUNT(*) FROM wayleave.agreement_polygon_uprns WHERE agreement_id=:aid7 AND is_assigned=false AND is_approved=true) AS pending_remove_count"; $stmt = $dbh->prepare($sqlCounts); for ($i = 1; $i <= 7; $i++) { $stmt->bindValue(':aid' . $i, $agreement_id, PDO::PARAM_INT); } $stmt->execute(); $counts = $stmt->fetch(PDO::FETCH_ASSOC); // 6. Journal (free text + system entries — reverse chronological) $sqlJ = "SELECT j.id, j.log_datetime, j.log_text, j.is_system, u.user_name FROM wayleave.agreement_journal j LEFT JOIN users.users u ON u.id = j.user_id WHERE j.agreement_id = :aid ORDER BY j.log_datetime DESC LIMIT 500"; $stmt = $dbh->prepare($sqlJ); $stmt->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $journal = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode([ 'success' => true, 'agreement' => $agreement, 'statuses' => $statuses, 'fields' => $fields, 'values' => $values, 'counts' => $counts, 'journal' => $journal, ]); } catch (PDOException $e) { echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 2: Syntax + round-trip test** ```bash php -l "www/fn/wayleave_load.php" ``` Then in the browser (logged in) POST `agreement_id=1`. Expected: JSON with `agreement.agreement_name = "Test Agreement 1"`, three seed `fields`, `values = {}`, `counts.polygon_count = 0`. --- ## Task 4: Backend — `wayleave_save.php` **Files:** - Create: `www/fn/wayleave_save.php` Handles saving of the Main Details tab. Mirrors `www/fn/project_save.php`: - Iterate POST keys. - Known static keys → update `wayleave.agreements` + write a row to `wayleave.agreements_history`. - Unknown keys → look up in `wayleave.agreement_fields` via `checkWayleaveEditField`; if dynamic, upsert into the right value table + write history. - Every change writes an entry to `wayleave.agreement_journal` with `is_system = true`. - [ ] **Step 1: Create the endpoint** ```php false,'error'=>'POST required']); exit; } $agreement_id = isset($_POST['agreement_id']) ? intval($_POST['agreement_id']) : 0; $create_new = isset($_POST['create_new']) ? ($_POST['create_new'] == '1') : false; $agreement_name = isset($_POST['agreement_name']) ? trim($_POST['agreement_name']) : ''; $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); // Create path (used by the list page "New Agreement" modal) if ($create_new) { if ($agreement_name === '') throw new Exception('agreement_name required'); $ins = $dbh->prepare("INSERT INTO wayleave.agreements (agreement_name, agreement_status_id, created_user, modified_user) VALUES (:name, (SELECT id FROM wayleave.agreement_status WHERE description='Draft'), :u1, :u2) RETURNING agreement_id"); $ins->bindValue(':name', $agreement_name); $ins->bindValue(':u1', $userID, PDO::PARAM_INT); $ins->bindValue(':u2', $userID, PDO::PARAM_INT); $ins->execute(); $agreement_id = (int)$ins->fetchColumn(); $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,'Agreement created',true)") ->execute([':a'=>$agreement_id, ':u'=>$userID]); $dbh->prepare("INSERT INTO wayleave.agreements_history (agreement_id, agreement_name, history_action, history_user) VALUES (:a, :n, 'INSERT', :u)") ->execute([':a'=>$agreement_id, ':n'=>$agreement_name, ':u'=>$userID]); $dbh->commit(); echo json_encode(['success'=>true, 'agreement_id'=>$agreement_id, 'created'=>true]); exit; } if ($agreement_id < 1) throw new Exception('missing agreement_id'); // Static columns allow-list and their POST keys $staticColumns = [ 'agreement_name' => 'agreement_name', 'agreement_reference' => 'agreement_reference', 'agreement_status_id' => 'agreement_status_id', 'agreement_type' => 'agreement_type', 'wayleave_team' => 'wayleave_team', 'bd_manager' => 'bd_manager', 'ecd_date' => 'ecd_date', 'signed_date' => 'signed_date', 'parent_agreement_id' => 'parent_agreement_id', 'account_id' => 'account_id', ]; $changedStatic = []; foreach ($staticColumns as $col => $postKey) { if (!array_key_exists($postKey, $_POST)) continue; $newVal = $_POST[$postKey]; if ($newVal === '') $newVal = null; $oldVal = checkWayleaveEditCurrentValueStatic($dbh, $agreement_id, $col); if ((string)$oldVal === (string)$newVal) continue; if (in_array($col, ['agreement_status_id','bd_manager','parent_agreement_id','account_id'], true)) { $cast = ' :v::integer '; } elseif (in_array($col, ['ecd_date','signed_date'], true)) { $cast = ' :v::date '; } else { $cast = ' :v '; } $sqlU = "UPDATE wayleave.agreements SET $col = $cast, modified_user = :u, modified_datetime = now() WHERE agreement_id = :aid"; $u = $dbh->prepare($sqlU); $u->bindValue(':v', $newVal); $u->bindValue(':u', $userID, PDO::PARAM_INT); $u->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $u->execute(); $h = $dbh->prepare("INSERT INTO wayleave.agreements_history (agreement_id, history_action, history_field, history_old_value, history_new_value, history_user) VALUES (:aid, 'UPDATE', :f, :old, :new, :u)"); $h->execute([ ':aid'=>$agreement_id, ':f'=>$col, ':old'=>$oldVal, ':new'=>$newVal, ':u'=>$userID ]); $changedStatic[] = $col; } // Dynamic fields — any remaining POST keys $reserved = array_merge(['agreement_id','create_new'], array_values($staticColumns)); $changedDynamic = []; foreach ($_POST as $postKey => $postVal) { if (in_array($postKey, $reserved, true)) continue; $fld = checkWayleaveEditField($dbh, $postKey); if (!$fld) continue; $fieldID = (int)$fld['field_id']; $dataType = $fld['field_data_type']; $newVal = ($postVal === '') ? null : $postVal; $oldVal = getWayleaveEditCurrentValue($dbh, $agreement_id, $fieldID, $dataType); if ((string)$oldVal === (string)$newVal) continue; switch ($dataType) { case 'text': $table='wayleave.agreement_field_values_text'; $histTable='wayleave.agreement_field_values_text_history'; $cast=''; break; case 'int': $table='wayleave.agreement_field_values_int'; $histTable='wayleave.agreement_field_values_int_history'; $cast='::bigint'; break; case 'numeric': $table='wayleave.agreement_field_values_numeric'; $histTable='wayleave.agreement_field_values_numeric_history'; $cast='::numeric'; break; case 'date': $table='wayleave.agreement_field_values_date'; $histTable='wayleave.agreement_field_values_date_history'; $cast='::date'; break; case 'boolean': $table='wayleave.agreement_field_values_boolean'; $histTable='wayleave.agreement_field_values_boolean_history'; $cast='::boolean'; break; default: continue 2; } $sqlUp = "INSERT INTO $table (agreement_id, field_id, field_value, modified_user, modified_datetime) VALUES (:aid, :fid, :v$cast, :u, now()) ON CONFLICT (agreement_id, field_id) DO UPDATE SET field_value = EXCLUDED.field_value, modified_user = EXCLUDED.modified_user, modified_datetime = EXCLUDED.modified_datetime"; $up = $dbh->prepare($sqlUp); $up->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $up->bindValue(':fid', $fieldID, PDO::PARAM_INT); $up->bindValue(':v', $newVal); $up->bindValue(':u', $userID, PDO::PARAM_INT); $up->execute(); $sqlH = "INSERT INTO $histTable (agreement_id, field_id, field_value, history_action, history_user) VALUES (:aid, :fid, :v$cast, 'UPDATE', :u)"; $h = $dbh->prepare($sqlH); $h->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $h->bindValue(':fid', $fieldID, PDO::PARAM_INT); $h->bindValue(':v', $newVal); $h->bindValue(':u', $userID, PDO::PARAM_INT); $h->execute(); $changedDynamic[] = $postKey; } if (count($changedStatic) + count($changedDynamic) > 0) { $summary = 'Fields updated: ' . implode(', ', array_merge($changedStatic, $changedDynamic)); $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>$summary]); $dbh->prepare("UPDATE wayleave.agreements SET modified_user=:u, modified_datetime=now() WHERE agreement_id=:aid") ->execute([':u'=>$userID, ':aid'=>$agreement_id]); } $dbh->commit(); echo json_encode(['success'=>true, 'agreement_id'=>$agreement_id, 'changed_static'=>$changedStatic, 'changed_dynamic'=>$changedDynamic]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 2: Syntax check + smoke test** ```bash php -l "www/fn/wayleave_save.php" ``` Logged in, POST `agreement_id=1&agreement_name=Renamed Test&wl_annual_fee=1234.50`. Expected JSON: `{"success":true,"agreement_id":1,"changed_static":["agreement_name"],"changed_dynamic":["wl_annual_fee"]}`. Verify: ```bash psql -U postgres -d netplanner -c "SELECT agreement_name FROM wayleave.agreements WHERE agreement_id=1;" psql -U postgres -d netplanner -c "SELECT field_id, field_value FROM wayleave.agreement_field_values_numeric WHERE agreement_id=1;" psql -U postgres -d netplanner -c "SELECT history_field, history_old_value, history_new_value FROM wayleave.agreements_history WHERE agreement_id=1 ORDER BY history_id DESC LIMIT 3;" ``` --- ## Task 5: Backend — Journal Save + Field-Values Load/Save **Files:** - Create: `www/fn/wayleave_journal_save.php` - Create: `www/fn/wayleave_field_values_load.php` - Create: `www/fn/wayleave_field_values_save.php` - [ ] **Step 1: Create `wayleave_journal_save.php`** (mirror of `project_journal_save.php`) ```php true]; if ($_SERVER['REQUEST_METHOD'] === 'POST') { $agreement_id = isset($_POST['agreement_id']) ? intval($_POST['agreement_id']) : 0; $journal_text = isset($_POST['journal_text']) ? trim($_POST['journal_text']) : ''; if ($agreement_id < 1 || $journal_text === '') { echo json_encode(['success'=>false,'error'=>'missing fields']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $stmt = $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_datetime, log_text, is_system) VALUES (:a, :u, now(), :t, false)"); $stmt->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>$journal_text]); } catch (PDOException $e) { $data = ['success'=>false, 'error'=>$e->getMessage()]; } } echo json_encode($data); ``` - [ ] **Step 2: Create `wayleave_field_values_load.php`** ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // CTE wraps the 5-way UNION ALL so :aid appears once — pdo_pgsql does not allow // reusing a named placeholder within a single prepared statement. $sql = " WITH v AS ( SELECT agreement_id, field_id, field_value::text AS field_value, 'text' AS dtype FROM wayleave.agreement_field_values_text UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'int' AS dtype FROM wayleave.agreement_field_values_int UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'numeric' AS dtype FROM wayleave.agreement_field_values_numeric UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'date' AS dtype FROM wayleave.agreement_field_values_date UNION ALL SELECT agreement_id, field_id, field_value::text AS field_value, 'boolean' AS dtype FROM wayleave.agreement_field_values_boolean ) SELECT field_id, field_value, dtype FROM v WHERE agreement_id = :aid"; try { $stmt = $dbh->prepare($sql); $stmt->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $values = []; foreach ($rows as $r) { $values[$r['field_id']] = $r['field_value']; } echo json_encode(['success'=>true, 'values'=>$values]); } catch (PDOException $e) { echo json_encode(['success'=>false, 'error'=>$e->getMessage()]); } ``` - [ ] **Step 3: Create `wayleave_field_values_save.php`** (single-field upsert) ```php false,'error'=>'missing fields']); exit; } if ($field_value === '') $field_value = null; $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $fld = checkWayleaveEditField($dbh, $field_form_id); if (!$fld) throw new Exception('unknown field'); $fid = (int)$fld['field_id']; $type = $fld['field_data_type']; $old = getWayleaveEditCurrentValue($dbh, $agreement_id, $fid, $type); if ((string)$old === (string)$field_value) { echo json_encode(['success'=>true,'changed'=>false]); exit; } switch ($type) { case 'text': $table='wayleave.agreement_field_values_text'; $hist='wayleave.agreement_field_values_text_history'; $cast=''; break; case 'int': $table='wayleave.agreement_field_values_int'; $hist='wayleave.agreement_field_values_int_history'; $cast='::bigint'; break; case 'numeric': $table='wayleave.agreement_field_values_numeric'; $hist='wayleave.agreement_field_values_numeric_history'; $cast='::numeric'; break; case 'date': $table='wayleave.agreement_field_values_date'; $hist='wayleave.agreement_field_values_date_history'; $cast='::date'; break; case 'boolean': $table='wayleave.agreement_field_values_boolean'; $hist='wayleave.agreement_field_values_boolean_history'; $cast='::boolean'; break; default: throw new Exception('bad data type'); } $dbh->beginTransaction(); $dbh->prepare("INSERT INTO $table (agreement_id, field_id, field_value, modified_user, modified_datetime) VALUES (:a,:f,:v$cast,:u,now()) ON CONFLICT (agreement_id, field_id) DO UPDATE SET field_value = EXCLUDED.field_value, modified_user=EXCLUDED.modified_user, modified_datetime=EXCLUDED.modified_datetime") ->execute([':a'=>$agreement_id, ':f'=>$fid, ':v'=>$field_value, ':u'=>$userID]); $dbh->prepare("INSERT INTO $hist (agreement_id, field_id, field_value, history_action, history_user) VALUES (:a,:f,:v$cast,'UPDATE',:u)") ->execute([':a'=>$agreement_id, ':f'=>$fid, ':v'=>$field_value, ':u'=>$userID]); $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>"Field '$field_form_id' updated"]); $dbh->commit(); echo json_encode(['success'=>true, 'changed'=>true]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ``` - [ ] **Step 4: Syntax check all three** ```bash php -l "www/fn/wayleave_journal_save.php" php -l "www/fn/wayleave_field_values_load.php" php -l "www/fn/wayleave_field_values_save.php" ``` --- ## Task 6: Backend — `wayleave_coverage_polygon_save.php` **Files:** - Create: `www/fn/wayleave_coverage_polygon_save.php` Handles three actions (`create` / `update` / `delete`) for a polygon, and on any write runs the approval-workflow spatial join. **Polygon → UPRN state transitions:** After recomputing the set `S` of UPRNs within the union of all remaining active polygons (using `basedata.abp.geom`, SRID 27700), update `wayleave.agreement_polygon_uprns`: 1. UPRN in `S`, no row → insert `(is_assigned=true, is_approved=false)` — pending add. 2. UPRN in `S`, existing `(false,true)` (pending removal) → flip to `(true,true)` — re-entered, restore active. 3. UPRN in `S`, existing `(false,false)` (inactive) → update to `(true,false)` — pending re-add. 4. UPRN not in `S`, existing `(true,true)` (active) → update to `(false,true)` — pending removal. 5. UPRN not in `S`, existing `(true,false)` (pending add) → update to `(false,false)` — withdrawn before approval. 6. All other rows untouched. - [ ] **Step 1: Create the endpoint** ```php false,'error'=>'bad params']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); if ($action === 'create') { if ($geojson === '') throw new Exception('geojson required'); $ins = $dbh->prepare("INSERT INTO wayleave.agreement_polygons (agreement_id, geom, label, added_user) VALUES (:a, ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(:g), 4326), 27700), :l, :u) RETURNING id"); $ins->execute([':a'=>$agreement_id, ':g'=>$geojson, ':l'=>$label, ':u'=>$userID]); $polygon_id = (int)$ins->fetchColumn(); } elseif ($action === 'update') { if ($polygon_id < 1 || $geojson === '') throw new Exception('polygon_id and geojson required'); $upd = $dbh->prepare("UPDATE wayleave.agreement_polygons SET geom = ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(:g), 4326), 27700), label = :l, modified_user = :u, modified_datetime = now() WHERE id = :pid AND agreement_id = :a"); $upd->execute([':g'=>$geojson, ':l'=>$label, ':u'=>$userID, ':pid'=>$polygon_id, ':a'=>$agreement_id]); } elseif ($action === 'delete') { if ($polygon_id < 1) throw new Exception('polygon_id required'); $del = $dbh->prepare("UPDATE wayleave.agreement_polygons SET is_deleted = true, modified_user = :u, modified_datetime = now() WHERE id = :pid AND agreement_id = :a"); $del->execute([':u'=>$userID, ':pid'=>$polygon_id, ':a'=>$agreement_id]); } // Recompute agreement.geom as bounding union of remaining polygons. // :a1 and :a2 are the same agreement — split because pdo_pgsql rejects placeholder reuse. $dbh->prepare("UPDATE wayleave.agreements SET geom = ( SELECT ST_Multi(ST_Union(geom)) FROM wayleave.agreement_polygons WHERE agreement_id = :a1 AND is_deleted = false ), modified_user = :u, modified_datetime = now() WHERE agreement_id = :a2") ->execute([':a1'=>$agreement_id, ':a2'=>$agreement_id, ':u'=>$userID]); // Spatial join: UPRNs within the union of remaining polygons (basedata.abp, SRID 27700 points) $found = $dbh->prepare(" WITH union_geom AS ( SELECT ST_Union(geom) AS g FROM wayleave.agreement_polygons WHERE agreement_id = :a AND is_deleted = false ) SELECT abp.uprn FROM basedata.abp abp, union_geom u WHERE u.g IS NOT NULL AND ST_Within(abp.geom, u.g)"); $found->execute([':a'=>$agreement_id]); $newSet = []; foreach ($found->fetchAll(PDO::FETCH_COLUMN) as $u) { $newSet[(string)$u] = true; } // Current tracked state — cast booleans to int so strict comparisons below work // regardless of pdo_pgsql driver boolean-string/stringify behaviour. $cur = $dbh->prepare("SELECT uprn, is_assigned::int AS is_assigned, is_approved::int AS is_approved FROM wayleave.agreement_polygon_uprns WHERE agreement_id = :a"); $cur->execute([':a'=>$agreement_id]); $tracked = []; foreach ($cur->fetchAll(PDO::FETCH_ASSOC) as $r) { $tracked[(string)$r['uprn']] = [ 'uprn' => $r['uprn'], 'is_assigned' => (int)$r['is_assigned'], 'is_approved' => (int)$r['is_approved'], ]; } $insPending = $dbh->prepare("INSERT INTO wayleave.agreement_polygon_uprns (agreement_id, uprn, is_assigned, is_approved, actioned_user_id, action_datetime) VALUES (:a, :u, true, false, :uid, now()) ON CONFLICT (agreement_id, uprn) DO UPDATE SET is_assigned=true, is_approved=false, actioned_user_id=EXCLUDED.actioned_user_id, action_datetime=now()"); $restoreActive = $dbh->prepare("UPDATE wayleave.agreement_polygon_uprns SET is_assigned=true, is_approved=true, actioned_user_id=:uid, action_datetime=now() WHERE agreement_id=:a AND uprn=:u"); $pendingRemove = $dbh->prepare("UPDATE wayleave.agreement_polygon_uprns SET is_assigned=false, is_approved=true, actioned_user_id=:uid, action_datetime=now() WHERE agreement_id=:a AND uprn=:u"); $withdrawPending = $dbh->prepare("UPDATE wayleave.agreement_polygon_uprns SET is_assigned=false, is_approved=false, actioned_user_id=:uid, action_datetime=now() WHERE agreement_id=:a AND uprn=:u"); foreach ($newSet as $uprn => $_) { if (!isset($tracked[$uprn])) { $insPending->execute([':a'=>$agreement_id, ':u'=>$uprn, ':uid'=>$userID]); continue; } $t = $tracked[$uprn]; if ($t['is_assigned'] === 0 && $t['is_approved'] === 1) { $restoreActive->execute([':a'=>$agreement_id, ':u'=>$uprn, ':uid'=>$userID]); } elseif ($t['is_assigned'] === 0 && $t['is_approved'] === 0) { $insPending->execute([':a'=>$agreement_id, ':u'=>$uprn, ':uid'=>$userID]); } } foreach ($tracked as $uprn => $t) { if (isset($newSet[$uprn])) continue; if ($t['is_assigned'] === 1 && $t['is_approved'] === 1) { $pendingRemove->execute([':a'=>$agreement_id, ':u'=>$uprn, ':uid'=>$userID]); } elseif ($t['is_assigned'] === 1 && $t['is_approved'] === 0) { $withdrawPending->execute([':a'=>$agreement_id, ':u'=>$uprn, ':uid'=>$userID]); } } $msg = ucfirst($action) . " polygon #$polygon_id; " . count($newSet) . " UPRN(s) within boundary union."; $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>$msg]); $dbh->commit(); echo json_encode(['success'=>true, 'polygon_id'=>$polygon_id, 'uprn_in_boundary'=>count($newSet)]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ``` - [ ] **Step 2: Syntax check** ```bash php -l "www/fn/wayleave_coverage_polygon_save.php" ``` - [ ] **Step 3: Quick state-matrix SQL verification** After the endpoint is called for `action=create` with a polygon covering some UPRNs: ```bash psql -U postgres -d netplanner -c "SELECT is_assigned, is_approved, COUNT(*) FROM wayleave.agreement_polygon_uprns WHERE agreement_id = 1 GROUP BY 1,2 ORDER BY 1,2;" ``` Expected: one row with `(true, false)` and count > 0. --- ## Task 7: Backend — Polygon UPRN Approval Endpoints **Files:** - Create: `www/fn/wayleave_coverage_polygon_uprns_load.php` - Create: `www/fn/wayleave_coverage_polygon_uprns_approve.php` - [ ] **Step 1: Create `wayleave_coverage_polygon_uprns_load.php`** — returns pending add/remove sets ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Each row includes basic ABP attributes (address) so the UI can render a useful list. $sql = " SELECT apu.uprn, apu.is_assigned, apu.is_approved, apu.action_datetime, CASE WHEN apu.is_assigned=true AND apu.is_approved=false THEN 'pending_add' WHEN apu.is_assigned=false AND apu.is_approved=true THEN 'pending_remove' ELSE 'other' END AS state, abp.address AS address_full FROM wayleave.agreement_polygon_uprns apu LEFT JOIN basedata.abp abp ON abp.uprn = apu.uprn WHERE apu.agreement_id = :aid AND ( (apu.is_assigned = true AND apu.is_approved = false) OR (apu.is_assigned = false AND apu.is_approved = true) ) ORDER BY apu.action_datetime DESC"; $stmt = $dbh->prepare($sql); $stmt->bindValue(':aid', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $pending_add = array_values(array_filter($rows, function($r){ return $r['state']==='pending_add'; })); $pending_remove = array_values(array_filter($rows, function($r){ return $r['state']==='pending_remove'; })); echo json_encode(['success'=>true, 'pending_add'=>$pending_add, 'pending_remove'=>$pending_remove]); ?> ``` > **Note on `basedata.abp`:** The authoritative UPRN/address table is `basedata.abp` (confirmed in `sql/geolynx_ddl.sql` — NOT `public.abp`, which does not exist). The address column is `address text` — the plan aliases it as `AS address_full` so the downstream JS field name is preserved. - [ ] **Step 2: Create `wayleave_coverage_polygon_uprns_approve.php`** — accept or reject pending changes ```php false,'error'=>'bad params']); exit; } $uprns = json_decode($uprns_json, true); if (!is_array($uprns)) { echo json_encode(['success'=>false,'error'=>'bad uprns json']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); // State transitions on approve / reject: // approve pending_add : (true,false) -> (true,true) // reject pending_add : (true,false) -> (false,false) // approve pending_remove : (false,true) -> (false,false) // reject pending_remove : (false,true) -> (true,true) (restore active; user kept it in) $sqlApproveAdd = "UPDATE wayleave.agreement_polygon_uprns SET is_assigned=true, is_approved=true, approved_user_id=:u, approved_datetime=now() WHERE agreement_id=:a AND uprn=:u2 AND is_assigned=true AND is_approved=false"; $sqlRejectAdd = "UPDATE wayleave.agreement_polygon_uprns SET is_assigned=false, is_approved=false, approved_user_id=:u, approved_datetime=now() WHERE agreement_id=:a AND uprn=:u2 AND is_assigned=true AND is_approved=false"; $sqlApproveRemove = "UPDATE wayleave.agreement_polygon_uprns SET is_assigned=false, is_approved=false, approved_user_id=:u, approved_datetime=now() WHERE agreement_id=:a AND uprn=:u2 AND is_assigned=false AND is_approved=true"; $sqlRejectRemove = "UPDATE wayleave.agreement_polygon_uprns SET is_assigned=true, is_approved=true, approved_user_id=:u, approved_datetime=now() WHERE agreement_id=:a AND uprn=:u2 AND is_assigned=false AND is_approved=true"; $sApAdd = $dbh->prepare($sqlApproveAdd); $sRjAdd = $dbh->prepare($sqlRejectAdd); $sApRm = $dbh->prepare($sqlApproveRemove); $sRjRm = $dbh->prepare($sqlRejectRemove); $countApproved = 0; $countRejected = 0; foreach ($uprns as $item) { if (!isset($item['uprn']) || !isset($item['state'])) continue; $uprn = (string)$item['uprn']; $state = $item['state']; if ($state === 'pending_add' && $action === 'approve') { $sApAdd->execute([':u'=>$userID, ':a'=>$agreement_id, ':u2'=>$uprn]); $countApproved++; } elseif ($state === 'pending_add' && $action === 'reject') { $sRjAdd->execute([':u'=>$userID, ':a'=>$agreement_id, ':u2'=>$uprn]); $countRejected++; } elseif ($state === 'pending_remove' && $action === 'approve') { $sApRm->execute([':u'=>$userID, ':a'=>$agreement_id, ':u2'=>$uprn]); $countApproved++; } elseif ($state === 'pending_remove' && $action === 'reject') { $sRjRm->execute([':u'=>$userID, ':a'=>$agreement_id, ':u2'=>$uprn]); $countRejected++; } } $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>"Polygon UPRN approval: $action — approved=$countApproved, rejected=$countRejected"]); $dbh->commit(); echo json_encode(['success'=>true, 'approved'=>$countApproved, 'rejected'=>$countRejected]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 3: Syntax check** ```bash php -l "www/fn/wayleave_coverage_polygon_uprns_load.php" php -l "www/fn/wayleave_coverage_polygon_uprns_approve.php" ``` --- ## Task 8: Backend — Direct UPRN + Stocklist Coverage Endpoints **Files:** - Create: `www/fn/wayleave_coverage_uprn_save.php` - Create: `www/fn/wayleave_coverage_stocklist_save.php` - [ ] **Step 1: Create `wayleave_coverage_uprn_save.php`** ```php false,'error'=>'bad params']); exit; } $uprns = json_decode($uprns_json, true); if (!is_array($uprns)) { echo json_encode(['success'=>false,'error'=>'bad uprns json']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); if ($action === 'add') { $sql = "INSERT INTO wayleave.agreement_uprns (agreement_id, uprn, added_user) VALUES (:a, :u, :uid) ON CONFLICT (agreement_id, uprn) DO UPDATE SET is_deleted=false, added_user=EXCLUDED.added_user, added_datetime=now()"; } else { $sql = "UPDATE wayleave.agreement_uprns SET is_deleted=true WHERE agreement_id=:a AND uprn=:u"; } $stmt = $dbh->prepare($sql); $count = 0; foreach ($uprns as $u) { if ($action === 'add') { $stmt->execute([':a'=>$agreement_id, ':u'=>(string)$u, ':uid'=>$userID]); } else { $stmt->execute([':a'=>$agreement_id, ':u'=>(string)$u]); } $count++; } $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>ucfirst($action).' direct UPRNs: '.$count]); $dbh->commit(); echo json_encode(['success'=>true, 'count'=>$count]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 2: Create `wayleave_coverage_stocklist_save.php`** ```php false,'error'=>'bad params']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); if ($action === 'attach') { $dbh->prepare("INSERT INTO wayleave.agreement_stocklists (agreement_id, stocklist_id, added_user) VALUES (:a, :s, :u) ON CONFLICT (agreement_id, stocklist_id) DO UPDATE SET is_deleted=false, added_user=EXCLUDED.added_user, added_datetime=now()") ->execute([':a'=>$agreement_id, ':s'=>$stocklist_id, ':u'=>$userID]); } else { $dbh->prepare("UPDATE wayleave.agreement_stocklists SET is_deleted=true WHERE agreement_id=:a AND stocklist_id=:s") ->execute([':a'=>$agreement_id, ':s'=>$stocklist_id]); } $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>ucfirst($action).' stocklist #'.$stocklist_id]); $dbh->commit(); echo json_encode(['success'=>true]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 3: Syntax check** ```bash php -l "www/fn/wayleave_coverage_uprn_save.php" php -l "www/fn/wayleave_coverage_stocklist_save.php" ``` --- ## Task 9: Backend — `wayleave_premises_load.php` **Files:** - Create: `www/fn/wayleave_premises_load.php` Returns the resolved premise list: union of (active polygon UPRNs) ∪ (direct UPRNs) ∪ (stocklist UPRNs), de-duplicated, tagged with source. - [ ] **Step 1: Create the endpoint** ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Per-UPRN source aggregation using array_agg so each UPRN reports which sources contributed it. // pdo_pgsql rejects placeholder reuse — suffix :aid1..3 and bind each separately. $sql = " WITH poly_uprns AS ( SELECT uprn, 'polygon'::text AS src, NULL::integer AS src_id FROM wayleave.agreement_polygon_uprns WHERE agreement_id = :aid1 AND is_assigned = true AND is_approved = true ), direct_uprns AS ( SELECT uprn, 'direct'::text AS src, NULL::integer AS src_id FROM wayleave.agreement_uprns WHERE agreement_id = :aid2 AND is_deleted = false ), stocklist_uprns AS ( -- stocklists.stocklist_premises has NO is_deleted column (legacy schema) — do not filter on it. SELECT sp.uprn, 'stocklist'::text AS src, ast.stocklist_id AS src_id FROM wayleave.agreement_stocklists ast JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ast.stocklist_id WHERE ast.agreement_id = :aid3 AND ast.is_deleted = false ), all_uprns AS ( SELECT * FROM poly_uprns UNION ALL SELECT * FROM direct_uprns UNION ALL SELECT * FROM stocklist_uprns ) SELECT u.uprn, array_agg(DISTINCT u.src) AS sources, array_remove(array_agg(DISTINCT u.src_id), NULL) AS source_ids, abp.address AS address_full, ST_X(ST_Transform(abp.geom, 4326)) AS lng, ST_Y(ST_Transform(abp.geom, 4326)) AS lat FROM all_uprns u LEFT JOIN basedata.abp abp ON abp.uprn = u.uprn GROUP BY u.uprn, abp.address, abp.geom ORDER BY u.uprn"; $stmt = $dbh->prepare($sql); for ($i = 1; $i <= 3; $i++) { $stmt->bindValue(':aid' . $i, $agreement_id, PDO::PARAM_INT); } $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // Postgres returns array columns as strings like '{polygon,direct}' — decode to PHP arrays. foreach ($rows as &$r) { if (is_string($r['sources'])) { $r['sources'] = array_filter(explode(',', trim($r['sources'], '{}')), function($s){ return $s !== ''; }); $r['sources'] = array_values($r['sources']); } if (is_string($r['source_ids'])) { $r['source_ids'] = array_filter(explode(',', trim($r['source_ids'], '{}')), function($s){ return $s !== ''; }); $r['source_ids'] = array_map('intval', array_values($r['source_ids'])); } } unset($r); echo json_encode(['success'=>true, 'premises'=>$rows, 'count'=>count($rows)]); ?> ``` - [ ] **Step 2: Syntax + smoke** ```bash php -l "www/fn/wayleave_premises_load.php" ``` Then POST `agreement_id=1`. Expected: `{"success":true, "premises":[], "count":0}` initially; once polygon UPRNs are approved or direct UPRNs are added, they appear. --- ## Task 10: Backend — File Upload + List **Design note — do NOT create a wayleave-specific upload endpoint.** The codebase already has a single shared upload endpoint (`www/fn/file_upload.php`) and a single shared download endpoint (`www/fn/serve_file.php`) that write/read `public.file_uploads` using an `entity` discriminator column. The canonical pattern is: extend both endpoints' `$allowedEntities` whitelist, reuse the existing file-storage directory (`/var/www/netplanner-files///`), and read file lists via a per-schema view (`wayleave.vw_file_uploads`, already created in Phase 0 step 5). The journal insert is done by `file_upload.php` itself — it `INSERT INTO ._journal` using the whitelist config (see `www/fn/file_upload.php:272-289`). `wayleave.agreement_journal` has the exact column shape required (`agreement_id`, `user_id`, `log_datetime`, `log_text`), so the built-in journal write works with no extra code. **Files:** - Modify: `www/fn/file_upload.php` (two discrete edits — add whitelist entry, add wayleave-ID capture block, add column to INSERT) - Modify: `www/fn/serve_file.php` (one edit — add whitelist entry) - Create: `www/fn/wayleave_files_load.php` (thin wrapper around `wayleave.vw_file_uploads`) - [ ] **Step 1: Extend `file_upload.php` `$allowedEntities` whitelist** Open `www/fn/file_upload.php` and locate the `$allowedEntities` array around lines 32-49. It currently ends with the `'opportunity'` entry. Replace: ```php $allowedEntities = [ 'project' => [ 'table' => 'projects.project_journal', 'primary_key' => 'project_id' ], 'stocklist' => [ 'table' => 'stocklists.stocklist_journal', 'primary_key' => 'stocklist_id' ], 'account' => [ 'table' => 'accounts.account_journal', 'primary_key' => 'account_id' ], 'opportunity' => [ 'table' => 'prospector.project_journal', 'primary_key' => 'opportunity_id' ] ]; ``` with: ```php $allowedEntities = [ 'project' => [ 'table' => 'projects.project_journal', 'primary_key' => 'project_id' ], 'stocklist' => [ 'table' => 'stocklists.stocklist_journal', 'primary_key' => 'stocklist_id' ], 'account' => [ 'table' => 'accounts.account_journal', 'primary_key' => 'account_id' ], 'opportunity' => [ 'table' => 'prospector.project_journal', 'primary_key' => 'opportunity_id' ], 'wayleave' => [ 'table' => 'wayleave.agreement_journal', 'primary_key' => 'agreement_id' ] ]; ``` - [ ] **Step 2: Add the `wayleave` ID capture block** Still in `www/fn/file_upload.php`, after the existing `opportunity_id` block (around lines 106-119) and before the `try {` at line 122, append a new block mirroring the existing pattern. Find: ```php $opportunity_id = null; if (isset($_POST['opportunity']) && is_numeric($_POST['opportunity'])) { $id = (int)$_POST['opportunity']; $opportunity_id = $id; } if (!$id && $attechmentSourceEntity == 'opportunity') { http_response_code(400); echo json_encode([ 'success' => false, 'error' => 'Opportunity ID' ]); exit; } try { ``` and replace with: ```php $opportunity_id = null; if (isset($_POST['opportunity']) && is_numeric($_POST['opportunity'])) { $id = (int)$_POST['opportunity']; $opportunity_id = $id; } if (!$id && $attechmentSourceEntity == 'opportunity') { http_response_code(400); echo json_encode([ 'success' => false, 'error' => 'Opportunity ID' ]); exit; } $agreement_id = null; if (isset($_POST['wayleave']) && is_numeric($_POST['wayleave'])) { $id = (int)$_POST['wayleave']; $agreement_id = $id; } if (!$id && $attechmentSourceEntity == 'wayleave') { http_response_code(400); echo json_encode([ 'success' => false, 'error' => 'Wayleave Agreement ID' ]); exit; } try { ``` - [ ] **Step 3: Add `agreement_id` to the `public.file_uploads` INSERT** Still in `www/fn/file_upload.php`, locate the INSERT around lines 219-249. Replace the full INSERT (column list, VALUES list, and its trailing `$sth->bindParam(...)` for `opportunity_id` through `file_size_raw`) so that the `agreement_id` column and bind come in alongside the existing IDs. Replace: ```php $q = " INSERT INTO public.file_uploads (file_id, file_location, file_upload_datetime, file_upload_user, file_name, file_type, entity, project_id, stocklist_id, account_id, opportunity_id, file_category, file_description, file_size, file_size_raw ) VALUES( :file_id, :file_location, now(), :user, :original_filename, :file_type, :entity, :project_id, :stocklist_id, :account_id, :opportunity_id, :file_category, :file_description, :file_size, :file_size_raw ); "; ``` with: ```php $q = " INSERT INTO public.file_uploads (file_id, file_location, file_upload_datetime, file_upload_user, file_name, file_type, entity, project_id, stocklist_id, account_id, opportunity_id, agreement_id, file_category, file_description, file_size, file_size_raw ) VALUES( :file_id, :file_location, now(), :user, :original_filename, :file_type, :entity, :project_id, :stocklist_id, :account_id, :opportunity_id, :agreement_id, :file_category, :file_description, :file_size, :file_size_raw ); "; ``` Then locate the bindParam block a few lines below (after `$sth = $dbh->prepare($q);`). Immediately after the `:opportunity_id` bindParam line, add a new bind: ```php $sth->bindParam(':opportunity_id', $opportunity_id, PDO::PARAM_INT); $sth->bindParam(':agreement_id', $agreement_id, PDO::PARAM_INT); ``` - [ ] **Step 4: Syntax-check `file_upload.php`** ```bash php -l "www/fn/file_upload.php" ``` Expected: `No syntax errors detected`. - [ ] **Step 5: Extend `serve_file.php` `$allowedEntities`** Open `www/fn/serve_file.php` around lines 26-43. Replace: ```php $allowedEntities = [ 'project' => [ 'table' => 'projects.project_journal', 'primary_key' => 'project_id' ], 'stocklist' => [ 'table' => 'stocklists.stocklist_journal', 'primary_key' => 'stocklist_id' ], 'account' => [ 'table' => 'accounts.account_journal', 'primary_key' => 'account_id' ], 'opportunity' => [ 'table' => 'prospector.project_journal', 'primary_key' => 'opportunity_id' ] ]; ``` with: ```php $allowedEntities = [ 'project' => [ 'table' => 'projects.project_journal', 'primary_key' => 'project_id' ], 'stocklist' => [ 'table' => 'stocklists.stocklist_journal', 'primary_key' => 'stocklist_id' ], 'account' => [ 'table' => 'accounts.account_journal', 'primary_key' => 'account_id' ], 'opportunity' => [ 'table' => 'prospector.project_journal', 'primary_key' => 'opportunity_id' ], 'wayleave' => [ 'table' => 'wayleave.agreement_journal', 'primary_key' => 'agreement_id' ] ]; ``` - [ ] **Step 6: Syntax-check `serve_file.php`** ```bash php -l "www/fn/serve_file.php" ``` Expected: `No syntax errors detected`. - [ ] **Step 7: Create `wayleave_files_load.php`** This is a thin wrapper around the Phase 0 view. It returns the same column shape the project/stocklist/account `*_load.php` endpoints already return (via their `vw_file_uploads` SELECTs) so the reused frontend upload modal can consume it without conditional logic. ```php false, 'error' => 'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $stmt = $dbh->prepare("SELECT * FROM wayleave.vw_file_uploads WHERE agreement_id = :agreement_id"); $stmt->bindParam(':agreement_id', $agreement_id, PDO::PARAM_INT); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode([ 'success' => true, 'results_file_attachments' => count($rows), 'data_file_attachments' => $rows ]); } catch (PDOException $e) { echo json_encode(['success' => false, 'error' => $e->getMessage()]); } ?> ``` - [ ] **Step 8: Syntax check the new file** ```bash php -l "www/fn/wayleave_files_load.php" ``` Expected: `No syntax errors detected`. - [ ] **Step 9: Smoke test the round trip in the browser** With an agreement row existing (created in Task 2's test), open DevTools → Console on any logged-in page and run: ```javascript // Prereq: `agreementId` matches a real wayleave.agreements row (e.g. 1) const fd = new FormData(); const blob = new Blob(['hello wayleave'], {type: 'text/csv'}); fd.append('file', blob, 'smoketest.csv'); fd.append('entity', 'wayleave'); fd.append('wayleave', 1); fd.append('project', ''); fd.append('stocklist', ''); fd.append('account', ''); fd.append('file_description', 'Smoke-test upload'); fd.append('file_category', 'test'); fetch('/netplanner/fn/file_upload.php', {method: 'POST', body: fd}) .then(r => r.json()).then(console.log); ``` Expected: `{success: true, ...}`. Then: ```javascript fetch('/netplanner/fn/wayleave_files_load.php', { method: 'POST', headers: {'Content-Type': 'application/x-www-form-urlencoded'}, body: 'agreement_id=1' }).then(r => r.json()).then(console.log); ``` Expected: `{success: true, results_file_attachments: 1, data_file_attachments: [{..., agreement_id: 1, file_name: 'smoketest.csv', ...}]}`. Verify the journal entry landed: ```bash psql -U postgres -d netplanner -c "SELECT id, log_text, is_system FROM wayleave.agreement_journal WHERE agreement_id = 1 ORDER BY id DESC LIMIT 3;" ``` Expected: the most recent row's `log_text` includes the uploaded filename (`file_upload.php` writes a journal row — see `www/fn/file_upload.php:272-289`). --- ## Task 11: Backend — Releases Load + Save **Files:** - Create: `www/fn/wayleave_releases_load.php` - Create: `www/fn/wayleave_releases_save.php` - [ ] **Step 1: Create `wayleave_releases_load.php`** ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $dbh->prepare("SELECT r.id, r.uprn, r.release_date, r.sales_ref, r.pic_ref, r.notes, r.released_user, r.released_datetime, r.created_datetime, r.modified_datetime, abp.address AS address_full FROM wayleave.agreement_releases r LEFT JOIN basedata.abp abp ON abp.uprn = r.uprn WHERE r.agreement_id = :a AND r.is_deleted = false ORDER BY r.release_date DESC NULLS LAST, r.id DESC"); $stmt->execute([':a'=>$agreement_id]); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode(['success'=>true, 'releases'=>$rows]); ?> ``` - [ ] **Step 2: Create `wayleave_releases_save.php`** ```php false,'error'=>'bad params']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); if ($action === 'upsert') { // pdo_pgsql rejects placeholder reuse within one prepared statement — :rd appears twice // (VALUES cast + CASE guard) and the user id appears in three columns, so suffix them. // $release_date is either null or a valid YYYY-MM-DD string (PHP normalizes empty to null // at the top of this file), so :rd::date either becomes NULL::date or a valid date cast. $dbh->prepare("INSERT INTO wayleave.agreement_releases (agreement_id, uprn, release_date, sales_ref, pic_ref, notes, released_user, released_datetime, created_user) VALUES (:a, :u, :rd::date, :sr, :pr, :n, :ruser, CASE WHEN :rd2 IS NULL THEN NULL ELSE now() END, :cuser) ON CONFLICT (agreement_id, uprn) DO UPDATE SET release_date = EXCLUDED.release_date, sales_ref = EXCLUDED.sales_ref, pic_ref = EXCLUDED.pic_ref, notes = EXCLUDED.notes, released_user = EXCLUDED.released_user, released_datetime = EXCLUDED.released_datetime, modified_user = :muser, modified_datetime = now(), is_deleted = false") ->execute([':a'=>$agreement_id, ':u'=>$uprn, ':rd'=>$release_date, ':rd2'=>$release_date, ':sr'=>$sales_ref, ':pr'=>$pic_ref, ':n'=>$notes, ':ruser'=>$userID, ':cuser'=>$userID, ':muser'=>$userID]); $msg = "Release upserted for UPRN $uprn"; } else { // delete $dbh->prepare("UPDATE wayleave.agreement_releases SET is_deleted=true, modified_user=:u, modified_datetime=now() WHERE agreement_id=:a AND uprn=:u2") ->execute([':u'=>$userID, ':a'=>$agreement_id, ':u2'=>$uprn]); $msg = "Release deleted for UPRN $uprn"; } $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>$msg]); $dbh->commit(); echo json_encode(['success'=>true]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 3: Syntax check** ```bash php -l "www/fn/wayleave_releases_load.php" php -l "www/fn/wayleave_releases_save.php" ``` --- ## Task 12: Backend — Projects, Land Registry, Map **Files:** - Create: `www/fn/wayleave_projects_load.php` - Create: `www/fn/wayleave_landreg_load.php` - Create: `www/fn/wayleave_landreg_save.php` - Create: `www/fn/wayleave_map_load.php` - [ ] **Step 1: Create `wayleave_projects_load.php`** — projects sharing UPRNs with this agreement ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // pdo_pgsql rejects placeholder reuse — suffix :a1..3 and bind each separately. // projects.projects has NO is_deleted column (legacy schema) — do not filter on it. // projects.project_status join uses project_status_id (primary key) and project_status_desc // (the description column). Confirmed against sibling project_load.php and geolynx_ddl.sql. $sql = " WITH resolved AS ( SELECT uprn FROM wayleave.agreement_polygon_uprns WHERE agreement_id=:a1 AND is_assigned=true AND is_approved=true UNION SELECT uprn FROM wayleave.agreement_uprns WHERE agreement_id=:a2 AND is_deleted=false UNION SELECT sp.uprn FROM wayleave.agreement_stocklists ast JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ast.stocklist_id -- stocklists.stocklist_premises has NO is_deleted column (legacy schema) — do not filter on it. WHERE ast.agreement_id=:a3 AND ast.is_deleted=false ) SELECT p.project_id, p.project_name, p.project_status_id, ps.project_status_desc AS status, COUNT(DISTINCT pp.uprn) AS overlap_count FROM projects.projects p JOIN projects.project_premises pp ON pp.project_id = p.project_id JOIN resolved r ON r.uprn = pp.uprn LEFT JOIN projects.project_status ps ON ps.project_status_id = p.project_status_id GROUP BY p.project_id, p.project_name, p.project_status_id, ps.project_status_desc ORDER BY overlap_count DESC, p.project_id DESC"; $stmt = $dbh->prepare($sql); for ($i = 1; $i <= 3; $i++) { $stmt->bindValue(':a' . $i, $agreement_id, PDO::PARAM_INT); } $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); echo json_encode(['success'=>true, 'projects'=>$rows]); ``` - [ ] **Step 2: Create `wayleave_landreg_load.php`** ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $dbh->prepare("SELECT id, title_number, tenure, added_datetime FROM wayleave.agreement_landregistry WHERE agreement_id = :a AND is_deleted = false ORDER BY added_datetime DESC"); $stmt->execute([':a'=>$agreement_id]); echo json_encode(['success'=>true, 'titles'=>$stmt->fetchAll(PDO::FETCH_ASSOC)]); ?> ``` - [ ] **Step 3: Create `wayleave_landreg_save.php`** ```php false,'error'=>'bad params']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try { $dbh->beginTransaction(); if ($action === 'add') { $dbh->prepare("INSERT INTO wayleave.agreement_landregistry (agreement_id, title_number, tenure, added_user) VALUES (:a,:t,:te,:u) ON CONFLICT (agreement_id, title_number) DO UPDATE SET tenure=EXCLUDED.tenure, is_deleted=false, added_user=EXCLUDED.added_user, added_datetime=now()") ->execute([':a'=>$agreement_id, ':t'=>$title_number, ':te'=>$tenure, ':u'=>$userID]); } else { $dbh->prepare("UPDATE wayleave.agreement_landregistry SET is_deleted=true WHERE agreement_id=:a AND title_number=:t") ->execute([':a'=>$agreement_id, ':t'=>$title_number]); } $dbh->prepare("INSERT INTO wayleave.agreement_journal (agreement_id, user_id, log_text, is_system) VALUES (:a,:u,:t,true)") ->execute([':a'=>$agreement_id, ':u'=>$userID, ':t'=>"Land registry title ".($action==='add'?'added':'removed').": $title_number"]); $dbh->commit(); echo json_encode(['success'=>true]); } catch (Exception $e) { if ($dbh->inTransaction()) $dbh->rollBack(); echo json_encode(['success'=>false,'error'=>$e->getMessage()]); } ?> ``` - [ ] **Step 4: Create `wayleave_map_load.php`** — GeoJSON bundle ```php false,'error'=>'missing agreement_id']); exit; } $dbh = new PDO("pgsql:host=$hostname;port=5432;dbname=$dbname;user=$username;password=$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Polygons (4326 GeoJSON) $stmt = $dbh->prepare("SELECT id, label, ST_AsGeoJSON(ST_Transform(geom, 4326)) AS geojson FROM wayleave.agreement_polygons WHERE agreement_id = :a AND is_deleted = false"); $stmt->execute([':a'=>$agreement_id]); $polygons = []; foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { $polygons[] = [ 'type' => 'Feature', 'properties' => ['id' => (int)$r['id'], 'label' => $r['label']], 'geometry' => json_decode($r['geojson'], true), ]; } // Resolved premises with lat/lng + sources + release status. // pdo_pgsql rejects placeholder reuse — suffix :a1..4 and bind each separately. $sqlR = " WITH resolved AS ( SELECT uprn, 'polygon'::text AS src FROM wayleave.agreement_polygon_uprns WHERE agreement_id=:a1 AND is_assigned=true AND is_approved=true UNION ALL SELECT uprn, 'direct'::text FROM wayleave.agreement_uprns WHERE agreement_id=:a2 AND is_deleted=false UNION ALL SELECT sp.uprn, 'stocklist'::text FROM wayleave.agreement_stocklists ast JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ast.stocklist_id -- stocklists.stocklist_premises has NO is_deleted column (legacy schema) — do not filter on it. WHERE ast.agreement_id=:a3 AND ast.is_deleted=false ), agg AS ( SELECT uprn, array_agg(DISTINCT src) AS sources FROM resolved GROUP BY uprn ) SELECT agg.uprn, agg.sources, ST_X(ST_Transform(abp.geom, 4326)) AS lng, ST_Y(ST_Transform(abp.geom, 4326)) AS lat, abp.address AS address_full, CASE WHEN r.release_date IS NOT NULL THEN true ELSE false END AS released FROM agg LEFT JOIN basedata.abp abp ON abp.uprn = agg.uprn LEFT JOIN wayleave.agreement_releases r ON r.agreement_id = :a4 AND r.uprn = agg.uprn AND r.is_deleted = false"; $stmt = $dbh->prepare($sqlR); for ($i = 1; $i <= 4; $i++) { $stmt->bindValue(':a' . $i, $agreement_id, PDO::PARAM_INT); } $stmt->execute(); $premises = []; foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) { if ($r['lng'] === null || $r['lat'] === null) continue; if (is_string($r['sources'])) { $r['sources'] = array_values(array_filter(explode(',', trim($r['sources'], '{}')), function($s){ return $s !== ''; })); } $premises[] = [ 'type' => 'Feature', 'properties' => [ 'uprn' => $r['uprn'], 'address' => $r['address_full'], 'sources' => $r['sources'], 'released' => (bool)$r['released'], ], 'geometry' => ['type' => 'Point', 'coordinates' => [(float)$r['lng'], (float)$r['lat']]], ]; } echo json_encode([ 'success' => true, 'polygons' => ['type' => 'FeatureCollection', 'features' => $polygons], 'premises' => ['type' => 'FeatureCollection', 'features' => $premises], ]); ?> ``` - [ ] **Step 5: Syntax check all four** ```bash php -l "www/fn/wayleave_projects_load.php" php -l "www/fn/wayleave_landreg_load.php" php -l "www/fn/wayleave_landreg_save.php" php -l "www/fn/wayleave_map_load.php" ``` --- ## Task 13: Frontend — List Page Shell + CSS **Files:** - Modify (overwrite): `www/html/wayleave.php` - Create: `www/css/wayleave_list.css` - [ ] **Step 1: Rewrite `www/html/wayleave.php`** (replace entire file) ```php

Wayleave Agreements

``` - [ ] **Step 2: Create `www/css/wayleave_list.css`** ```css /* www/css/wayleave_list.css */ .wl-filter-bar .form-control, .wl-filter-bar .form-select { font-size: 0.9rem; } #wl-list-table { min-height: 400px; } /* Tabulator status cell pills */ .wl-status-pill { display: inline-block; padding: 2px 10px; border-radius: 12px; font-size: 0.8rem; font-weight: 600; color: #fff; background: #6c757d; } .wl-status-pill[data-status="Draft"] { background: #6c757d; } .wl-status-pill[data-status="In Progress"] { background: #0d6efd; } .wl-status-pill[data-status="On Hold"] { background: #ffc107; color: #212529; } .wl-status-pill[data-status="Signed"] { background: #198754; } .wl-status-pill[data-status="Complete"] { background: #20c997; } .wl-status-pill[data-status="Cancelled"] { background: #dc3545; } ``` - [ ] **Step 3: Visual verification** Load `http://localhost/netplanner/?do=wayleave`. Expected: a clean page with a search bar, two filter selects, a Refresh button, a "+ New Agreement" button in the header, and an empty `#wl-list-table` div. No JS console errors for this step — functionality comes in Task 14. --- ## Task 14: Frontend — List Page JS **Files:** - Create: `www/js/wayleave_list.js` - [ ] **Step 1: Create the JS file** ```javascript // www/js/wayleave_list.js // List page controller. Loads rows from wayleave_list_load.php into a Tabulator table, // handles search/filter, opens the "new agreement" modal and creates a record. (function () { var table = null; var rawRows = []; function buildColumns() { return [ { title: "Agreement", field: "agreement_name", widthGrow: 3, formatter: function (cell) { var d = cell.getRow().getData(); var ref = d.agreement_reference ? ' ('+escapeHtml(d.agreement_reference)+')' : ''; return ''+escapeHtml(d.agreement_name || '(no name)')+''+ref; }}, { title: "Type", field: "agreement_type", widthGrow: 1 }, { title: "Team", field: "wayleave_team", widthGrow: 1 }, { title: "Status", field: "status", widthGrow: 1, formatter: function (cell) { var v = cell.getValue() || 'Draft'; return ''+escapeHtml(v)+''; }}, { title: "ECD", field: "ecd_date", widthGrow: 1, formatter: dateFmt }, { title: "Signed", field: "signed_date", widthGrow: 1, formatter: dateFmt }, { title: "Premises", field: "premise_count", widthGrow: 1, hozAlign: "right" }, ]; } function dateFmt(cell) { var v = cell.getValue(); if (!v) return ''; // pg returns 'YYYY-MM-DD' return v.substring(0,10); } function escapeHtml(s) { if (s === null || s === undefined) return ''; return String(s).replace(/[&<>"']/g, function (c) { return {'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]; }); } function load() { var payload = { search: $('#wl-search').val(), status: $('#wl-filter-status').val(), team: $('#wl-filter-team').val() }; return $.post('fn/wayleave_list_load.php', payload, null, 'json') .done(function (resp) { if (!resp || !resp.success) { console.error('list_load failed', resp); return; } rawRows = resp.data || []; refreshFilterOptions(rawRows); if (table) table.replaceData(rawRows); }) .fail(function (xhr) { console.error('list_load error', xhr.status, xhr.responseText); }); } function refreshFilterOptions(rows) { // Populate status and team dropdowns from observed distinct values. var statuses = {}, teams = {}; rows.forEach(function (r) { if (r.status) statuses[r.status] = true; if (r.wayleave_team) teams[r.wayleave_team] = true; }); syncSelect('#wl-filter-status', Object.keys(statuses).sort(), 'All statuses'); syncSelect('#wl-filter-team', Object.keys(teams).sort(), 'All teams'); } function syncSelect(selector, values, placeholder) { var $sel = $(selector); var current = $sel.val(); var html = ''; values.forEach(function (v) { html += ''; }); $sel.html(html); if (current) $sel.val(current); } function initTable() { table = new Tabulator('#wl-list-table', { data: [], layout: 'fitColumns', pagination: 'local', paginationSize: 25, placeholder: 'No agreements found', columns: buildColumns(), initialSort: [{ column: 'agreement_id', dir: 'desc' }], }); } function wireEvents() { var searchTimer = null; $('#wl-search').on('input', function () { clearTimeout(searchTimer); searchTimer = setTimeout(load, 250); }); $('#wl-filter-status, #wl-filter-team').on('change', load); $('#wl-refresh').on('click', load); $('#wl-new-btn').on('click', function () { $('#wl-new-name').val(''); $('#wl-new-type').val(''); var modal = new bootstrap.Modal(document.getElementById('wl-new-modal')); modal.show(); }); $('#wl-new-save').on('click', function () { var name = $.trim($('#wl-new-name').val()); var type = $('#wl-new-type').val(); if (!name) { alert('Name is required'); return; } $.post('fn/wayleave_save.php', { create_new: '1', agreement_name: name, agreement_type: type }, null, 'json') .done(function (resp) { if (!resp || !resp.success) { alert('Create failed: '+(resp && resp.error || 'unknown')); return; } window.location.href = '?do=wayleaveedit&agreement_id='+resp.agreement_id; }) .fail(function (xhr) { alert('Create failed: HTTP '+xhr.status); }); }); } $(function () { initTable(); wireEvents(); load(); }); })(); ``` - [ ] **Step 2: Verify in browser** Reload `?do=wayleave`. Expected: - Tabulator renders the existing test agreement (from Task 2). - The status filter shows "Draft" as an option (after the first `load()` completes). - Click `+ New Agreement`, enter "Smoke test agreement", click `Create & Open`. The browser should redirect to `?do=wayleaveedit&agreement_id=N` (where N is new). - Verify DB: ```bash psql -U postgres -d netplanner -c "SELECT agreement_id, agreement_name FROM wayleave.agreements ORDER BY agreement_id DESC LIMIT 3;" ``` --- ## Task 15: Frontend — Editor Shell HTML + CSS **Files:** - Modify (overwrite): `www/html/wayleave_edit.php` - Create: `www/css/wayleave_edit.css` The editor tabs: 1. Main Details (static + dynamic fields) 2. Coverage (polygons / direct UPRNs / stocklists + approval UI) 3. Premises (resolved list) 4. Land Registry 5. Files 6. Projects 7. Releases 8. Map 9. Journal & Audit Log - [ ] **Step 1: Rewrite `www/html/wayleave_edit.php`** ```php

Wayleave Agreement

← Back to list
Polygons

Draw boundary polygons. Each change triggers a spatial join; results appear in the approval panel.

Pending Approval
Pending add 0
Pending removal 0
Direct UPRNs
Attached Stocklists
Resolved premises: 0

Projects that share at least one UPRN with this agreement's resolved premise list.

``` - [ ] **Step 2: Create `www/css/wayleave_edit.css`** ```css /* www/css/wayleave_edit.css */ #wl-editor-title { margin-bottom: 0; } .wl-pending-list { max-height: 180px; overflow-y: auto; border: 1px solid #e9ecef; border-radius: 4px; padding: 6px; margin-top: 4px; font-size: 0.85rem; background: #fbfbfd; } .wl-pending-row { display: flex; justify-content: space-between; gap: 6px; padding: 2px 0; } .wl-pending-row .u { font-family: monospace; } .wl-pending-row .a { color: #6c757d; flex: 1; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; } .wl-source-tag { display: inline-block; padding: 0 6px; border-radius: 8px; font-size: 0.75rem; background: #e9ecef; color: #495057; margin-right: 4px; } .wl-source-tag.polygon { background: #cfe2ff; color: #084298; } .wl-source-tag.direct { background: #d1e7dd; color: #0f5132; } .wl-source-tag.stocklist { background: #fff3cd; color: #664d03; } .wl-field-spacer { height: 8px; grid-column: 1 / -1; } #wl-coverage-map, #wl-view-map { background: #f8f9fa; } /* Leaflet-Geoman toolbar tweak */ .leaflet-pm-toolbar { font-size: 12px; } ``` - [ ] **Step 3: Visual verification** Open `?do=wayleaveedit&agreement_id=1`. Expected: nine tab buttons, empty panels. No JS errors. The `#wl-coverage-map` and `#wl-view-map` divs render as empty grey boxes. --- ## Task 16: Frontend — Editor JS Core (Tab Init + Main Details) **Files:** - Create: `www/js/wayleave_edit.js` This is split across Tasks 16-18. Each appends to the same file. Structure the file as an IIFE with a shared `state` object and per-tab modules. - [ ] **Step 1: Create the initial file with tab init and Main Details tab** ```javascript // www/js/wayleave_edit.js // Wayleave Agreement editor. Tab-based UI, each tab loaded lazily. (function () { var state = { agreementId: null, agreement: null, statuses: [], fields: [], values: {}, counts: {}, journal: [], loaded: { main: false, coverage: false, premises: false, landreg: false, files: false, projects: false, releases: false, map: false, log: false } }; window.WL = { state: state }; // exposed for per-tab modules in later files function escapeHtml(s) { if (s === null || s === undefined) return ''; return String(s).replace(/[&<>"']/g, function (c) { return {'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]; }); } window.WL.escapeHtml = escapeHtml; function toast(msg, type) { $('#wl-save-indicator').text(msg).css('color', type === 'err' ? '#dc3545' : '#198754'); clearTimeout(window.WL._tt); window.WL._tt = setTimeout(function () { $('#wl-save-indicator').text(''); }, 3000); } window.WL.toast = toast; function loadMain() { return $.post('fn/wayleave_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) { alert('Load failed: '+(resp&&resp.error||'unknown')); return; } state.agreement = resp.agreement; state.statuses = resp.statuses; state.fields = resp.fields; state.values = resp.values; state.counts = resp.counts; state.journal = resp.journal; renderMain(); updateBadges(); state.loaded.main = true; }); } function updateBadges() { var pendingTotal = (+state.counts.pending_add_count || 0) + (+state.counts.pending_remove_count || 0); if (pendingTotal > 0) { $('#wl-coverage-pending-badge').text(pendingTotal).show(); } else { $('#wl-coverage-pending-badge').hide(); } $('#wl-premise-count-badge').text(''); // set when premises tab loads } function renderMain() { var a = state.agreement; $('#wl-editor-title').text(a.agreement_name || 'Wayleave Agreement #'+a.agreement_id); // Static fields grid var statusOpts = '' + state.statuses.map(function (s) { return ''; }).join(''); var staticHtml = '' + col(6, field('agreement_name', 'Agreement Name', '')) + col(6, field('agreement_reference', 'Reference', '')) + col(3, field('agreement_status_id', 'Status', '')) + col(3, field('agreement_type', 'Type', typeSelect(a.agreement_type))) + col(3, field('wayleave_team', 'Wayleave Team', '')) + col(3, field('bd_manager', 'BD Manager (ID)','')) + col(3, field('ecd_date', 'ECD Date', '')) + col(3, field('signed_date', 'Signed Date', '')) + col(3, field('parent_agreement_id', 'Parent Agreement (ID)', '')) + col(3, field('account_id', 'Account (ID)', '')); $('#wl-main-static-fields').html(staticHtml); // Dynamic fields, grouped by section var bySection = {}; state.fields.forEach(function (f) { var key = f.section_name || '— Uncategorised —'; (bySection[key] = bySection[key] || []).push(f); }); var html = ''; Object.keys(bySection).forEach(function (sec) { html += '
'+escapeHtml(sec)+'
'; bySection[sec].forEach(function (f) { var v = state.values[f.field_id] !== undefined ? state.values[f.field_id] : ''; html += col(4, field(f.field_form_id, f.field_name, dynamicInput(f, v))); if (f.field_spacer_after) html += '
'; }); }); $('#wl-main-dynamic-fields').html(html); } function col(n, inner) { return '
'+inner+'
'; } function field(id, label, input) { return ''+input.replace(/^<(input|select|textarea)/, '<$1 id="'+id+'"'); } function typeSelect(current) { var opts = ['','SDU','MDU','Estate','Building','Route']; return ''; } function dynamicInput(f, value) { var name = f.field_form_id; var val = value === null || value === undefined ? '' : value; if (f.dropdown_options && f.dropdown_options.length) { return ''; } switch (f.field_input_type) { case 'textarea': return ''; case 'date': return ''; case 'checkbox': return ''; case 'number': return ''; default: return ''; } } function wireMainSave() { $('#wl-main-form').on('submit', function (e) { e.preventDefault(); var data = $(this).serialize(); $.post('fn/wayleave_save.php', data, null, 'json') .done(function (resp) { if (!resp || !resp.success) { toast('Save failed: '+(resp&&resp.error||'unknown'), 'err'); return; } var n = (resp.changed_static.length + resp.changed_dynamic.length); toast(n>0 ? 'Saved '+n+' change(s)' : 'No changes'); if (n>0) loadMain(); }) .fail(function (xhr) { toast('Save failed: HTTP '+xhr.status, 'err'); }); }); } // Tab lazy loading function wireTabs() { $('[data-bs-toggle="tab"]').on('shown.bs.tab', function (e) { var target = $(e.target).attr('data-bs-target'); switch (target) { case '#wl-tab-main': if (!state.loaded.main) loadMain(); break; case '#wl-tab-coverage': if (!state.loaded.coverage) WL.loadCoverage && WL.loadCoverage(); break; case '#wl-tab-premises': WL.loadPremises && WL.loadPremises(); break; case '#wl-tab-landreg': WL.loadLandreg && WL.loadLandreg(); break; case '#wl-tab-files': WL.loadFiles && WL.loadFiles(); break; case '#wl-tab-projects': WL.loadProjects && WL.loadProjects(); break; case '#wl-tab-releases': WL.loadReleases && WL.loadReleases(); break; case '#wl-tab-map': WL.loadMap && WL.loadMap(); break; case '#wl-tab-log': WL.loadJournal && WL.loadJournal(); break; } }); } $(function () { state.agreementId = parseInt($('#main').attr('data-agreement-id'), 10); if (!state.agreementId) { alert('Missing agreement_id'); return; } wireTabs(); wireMainSave(); loadMain(); }); })(); ``` - [ ] **Step 2: Verify in browser** Open `?do=wayleaveedit&agreement_id=1`. Expected: - Main Details tab renders with static fields pre-populated and the three dynamic fields (Annual Fee, Landowner Solicitor, Access Agreement Date) grouped by section. - Change a field, click Save; the small save indicator shows "Saved N change(s)". - DB verification: ```bash psql -U postgres -d netplanner -c "SELECT modified_datetime, modified_user FROM wayleave.agreements WHERE agreement_id=1;" ``` --- ## Task 17: Frontend — Editor JS Coverage Tab **Files:** - Modify (append): `www/js/wayleave_edit.js` The Coverage tab needs: - A Leaflet map + Leaflet-Geoman for drawing polygons. - Persist polygons via `wayleave_coverage_polygon_save.php` on `pm:create` / `pm:edit` / `pm:remove` events. - Reload pending-approval lists after each polygon change. - Direct-UPRN add/remove UI. - Stocklist attach/detach UI. **Assumptions:** - Leaflet + Leaflet-Geoman CSS/JS are loaded globally by the site layout (consistent with the project editor that uses them). If not, see Task 19 below for loading them dynamically. - The OS basemap tile URL used elsewhere is `https://tile.openstreetmap.org/{z}/{x}/{y}.png`; the project editor uses the same. Copy that exactly. - [ ] **Step 1: Append the Coverage module to `www/js/wayleave_edit.js`** Append the code below to the **end** of `www/js/wayleave_edit.js` (outside the previous IIFE, as a new IIFE). ```javascript // ---- Coverage tab ---- (function () { if (!window.WL) return; var state = WL.state; var map = null; var polyLayer = null; // L.FeatureGroup of drawn polygons var polygonsById = {}; // { polygonId: leafletLayerRef } var initialized = false; function initMap() { map = L.map('wl-coverage-map').setView([54.5, -2.0], 6); L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', { attribution: '© OpenStreetMap', maxZoom: 19 }).addTo(map); polyLayer = L.featureGroup().addTo(map); map.pm.addControls({ position: 'topleft', drawPolygon: true, drawMarker: false, drawPolyline: false, drawRectangle: true, drawCircle: false, drawCircleMarker: false, drawText: false, editMode: true, dragMode: false, cutPolygon: false, removalMode: true }); map.on('pm:create', function (e) { var gj = e.layer.toGeoJSON(); savePolygon('create', 0, gj.geometry, null, e.layer); }); map.on('pm:remove', function (e) { var id = e.layer._wlPolygonId; if (!id) return; savePolygon('delete', id, null, null, null); }); } function bindLayerEvents(layer, polygonId) { layer._wlPolygonId = polygonId; polygonsById[polygonId] = layer; layer.on('pm:edit', function () { var gj = layer.toGeoJSON(); savePolygon('update', polygonId, gj.geometry, null, layer); }); } function savePolygon(action, polygonId, geom, label, newLayer) { var payload = { agreement_id: state.agreementId, action: action }; if (polygonId) payload.polygon_id = polygonId; if (geom) payload.geojson = JSON.stringify(geom); if (label) payload.label = label; $.post('fn/wayleave_coverage_polygon_save.php', payload, null, 'json') .done(function (resp) { if (!resp || !resp.success) { WL.toast('Polygon save failed: '+(resp&&resp.error||'unknown'), 'err'); return; } WL.toast('Polygon '+action+': '+resp.uprn_in_boundary+' UPRN(s) in boundary'); if (action === 'create' && newLayer) bindLayerEvents(newLayer, resp.polygon_id); refreshPending(); refreshPolygonSummary(); }) .fail(function (xhr) { WL.toast('Polygon save HTTP '+xhr.status, 'err'); }); } function loadPolygons() { $.post('fn/wayleave_map_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; polyLayer.clearLayers(); polygonsById = {}; var hasAny = false; (resp.polygons.features || []).forEach(function (f) { var layer = L.geoJSON(f).getLayers()[0]; polyLayer.addLayer(layer); bindLayerEvents(layer, f.properties.id); hasAny = true; }); if (hasAny) { map.fitBounds(polyLayer.getBounds(), { padding: [20,20] }); } refreshPolygonSummary(); }); } function refreshPolygonSummary() { var n = Object.keys(polygonsById).length; $('#wl-polygon-summary').text(n+' polygon(s) saved'); } function refreshPending() { $.post('fn/wayleave_coverage_polygon_uprns_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; $('#wl-pending-add-count').text(resp.pending_add.length); $('#wl-pending-remove-count').text(resp.pending_remove.length); $('#wl-pending-add-list').html(renderPendingList(resp.pending_add)); $('#wl-pending-remove-list').html(renderPendingList(resp.pending_remove)); var total = resp.pending_add.length + resp.pending_remove.length; if (total > 0) { $('#wl-coverage-pending-badge').text(total).show(); } else { $('#wl-coverage-pending-badge').hide(); } }); } function renderPendingList(arr) { if (!arr.length) return 'None'; return arr.map(function (r) { return '
'+WL.escapeHtml(r.uprn)+'' + ''+WL.escapeHtml(r.address_full||'')+'
'; }).join(''); } function wireApprove() { $('#wl-pending-pane').on('click', '[data-approve], [data-reject]', function () { var $btn = $(this); var isApprove = $btn.is('[data-approve]'); var stateName = $btn.attr('data-approve') || $btn.attr('data-reject'); var listSel = stateName === 'pending_add' ? '#wl-pending-add-list' : '#wl-pending-remove-list'; var rows = []; $(listSel).find('.wl-pending-row .u').each(function () { rows.push({ uprn: $(this).text(), state: stateName }); }); if (!rows.length) return; $.post('fn/wayleave_coverage_polygon_uprns_approve.php', { agreement_id: state.agreementId, action: isApprove ? 'approve' : 'reject', uprns: JSON.stringify(rows) }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Approval failed: '+(resp&&resp.error||'unknown'), 'err'); return; } WL.toast('Approved '+resp.approved+', rejected '+resp.rejected); refreshPending(); WL.loadPremises && WL.loadPremises(true); }); }); } // --- Direct UPRNs --- function loadDirect() { $.post('fn/wayleave_premises_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; // Extract only those whose sources include 'direct' var direct = (resp.premises || []).filter(function (p) { return p.sources && p.sources.indexOf('direct') !== -1; }); if (!direct.length) { $('#wl-direct-uprn-list').html('No direct UPRNs.'); return; } var html = direct.map(function (p) { return '
' + ''+WL.escapeHtml(p.uprn)+'' + ''+WL.escapeHtml(p.address_full||'')+'' + '' + '
'; }).join(''); $('#wl-direct-uprn-list').html(html); }); } function wireDirect() { $('#wl-direct-uprn-add').on('click', function () { var raw = $.trim($('#wl-direct-uprn-input').val()); if (!raw) return; var uprns = raw.split(/[\s,]+/).filter(Boolean); $.post('fn/wayleave_coverage_uprn_save.php', { agreement_id: state.agreementId, action: 'add', uprns: JSON.stringify(uprns) }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Add failed: '+(resp&&resp.error||'unknown'), 'err'); return; } WL.toast('Added '+resp.count+' UPRN(s)'); $('#wl-direct-uprn-input').val(''); loadDirect(); WL.loadPremises && WL.loadPremises(true); }); }); $('#wl-direct-uprn-list').on('click', '.wl-direct-remove', function () { var u = $(this).data('uprn'); $.post('fn/wayleave_coverage_uprn_save.php', { agreement_id: state.agreementId, action: 'remove', uprns: JSON.stringify([u]) }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Remove failed', 'err'); return; } loadDirect(); WL.loadPremises && WL.loadPremises(true); }); }); } // --- Stocklists --- function loadStocklists() { // Uses the generic stocklist list endpoint if present; otherwise query wayleave.agreement_stocklists via a dedicated endpoint. // For v1 we just render the currently attached stocklists from a small query. Fetch via wayleave_load's counts won't include names; // if a dedicated "attached stocklists list" endpoint is wanted, add one. For now: hit stocklists list endpoint and filter. $.post('fn/stocklist_list_load.php', {}, null, 'json') .done(function (resp) { var all = (resp && resp.data) ? resp.data : []; // Render those whose id appears in agreement_stocklists via a separate lightweight query. $.post('fn/wayleave_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function () { // Since wayleave_load doesn't return attached stocklist ids directly, do an inline load via agreement_stocklists via map endpoint fallback. // Simpler: call the premises endpoint and collect distinct stocklist source_ids. $.post('fn/wayleave_premises_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (pr) { var ids = {}; (pr.premises || []).forEach(function (p) { (p.source_ids || []).forEach(function (id) { ids[id] = true; }); }); var attached = all.filter(function (s) { return ids[s.stocklist_id]; }); if (!attached.length) { $('#wl-stocklist-list').html('No stocklists attached.'); return; } var html = attached.map(function (s) { return '
' + '#'+s.stocklist_id+'' + ''+WL.escapeHtml(s.stocklist_name||'')+'' + '' + '
'; }).join(''); $('#wl-stocklist-list').html(html); }); }); }); } function wireStocklists() { $('#wl-stocklist-attach').on('click', function () { var raw = $.trim($('#wl-stocklist-search').val()); var id = parseInt(raw, 10); if (!id) { alert('Enter a numeric stocklist id'); return; } $.post('fn/wayleave_coverage_stocklist_save.php', { agreement_id: state.agreementId, action: 'attach', stocklist_id: id }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Attach failed', 'err'); return; } $('#wl-stocklist-search').val(''); loadStocklists(); WL.loadPremises && WL.loadPremises(true); }); }); $('#wl-stocklist-list').on('click', '.wl-stocklist-detach', function () { var id = parseInt($(this).data('id'), 10); $.post('fn/wayleave_coverage_stocklist_save.php', { agreement_id: state.agreementId, action: 'detach', stocklist_id: id }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Detach failed', 'err'); return; } loadStocklists(); WL.loadPremises && WL.loadPremises(true); }); }); } WL.loadCoverage = function () { if (!initialized) { initMap(); wireApprove(); wireDirect(); wireStocklists(); initialized = true; } // When shown, Leaflet needs to know about its container size. setTimeout(function () { if (map) map.invalidateSize(); }, 150); loadPolygons(); refreshPending(); loadDirect(); loadStocklists(); WL.state.loaded.coverage = true; }; })(); ``` - [ ] **Step 2: Verify in browser** 1. Open `?do=wayleaveedit&agreement_id=1` and click the Coverage tab. Expected: Leaflet map appears with Geoman draw controls. "0 polygon(s) saved" below the map. 2. Draw a small polygon over a UPRN-dense area. Expected: toast says "Polygon create: N UPRN(s) in boundary" and the Pending Add list populates. 3. Click **Approve all adds**. Expected: toast "Approved N, rejected 0". Pending add becomes empty. `agreement_polygon_uprns` rows flip to `(true,true)`: ```bash psql -U postgres -d netplanner -c "SELECT is_assigned, is_approved, COUNT(*) FROM wayleave.agreement_polygon_uprns WHERE agreement_id = 1 GROUP BY 1,2;" ``` Expected: only `(true, true)` remains. 4. Drag a vertex to shrink the polygon so some UPRNs fall outside. Expected: approved UPRNs now outside the boundary show up in Pending Removal. 5. Add a direct UPRN via the input. Verify it appears in the "Direct UPRNs" list. --- ## Task 18: Frontend — Remaining Tabs (Premises, Land Registry, Files, Projects, Releases, Map, Journal) **Files:** - Modify (append): `www/js/wayleave_edit.js` - [ ] **Step 1: Append the remaining tab modules** Append this block to the **end** of `www/js/wayleave_edit.js` (as another IIFE). ```javascript // ---- Premises / Landreg / Files / Projects / Releases / Map / Journal ---- (function () { if (!window.WL) return; var state = WL.state; var premisesTable = null, projectsTable = null, releasesTable = null; var viewMap = null, viewPolyLayer = null, viewPointLayer = null; function sourceTags(sources) { return (sources || []).map(function (s) { return ''+WL.escapeHtml(s)+''; }).join(''); } // ---------- Premises ---------- WL.loadPremises = function (force) { return $.post('fn/wayleave_premises_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; $('#wl-premises-count').text(resp.count); $('#wl-premise-count-badge').text(resp.count); if (!premisesTable) { premisesTable = new Tabulator('#wl-premises-table', { data: resp.premises, layout: 'fitColumns', pagination: 'local', paginationSize: 50, placeholder: 'No resolved premises', columns: [ { title: 'UPRN', field: 'uprn', width: 140 }, { title: 'Address', field: 'address_full' }, { title: 'Sources', field: 'sources', formatter: function (c) { return sourceTags(c.getValue()); } }, ] }); } else { premisesTable.replaceData(resp.premises); } state.loaded.premises = true; }); }; // ---------- Land Registry ---------- WL.loadLandreg = function () { $.post('fn/wayleave_landreg_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; if (!resp.titles.length) { $('#wl-landreg-list').html('No titles linked.'); return; } var html = ''; resp.titles.forEach(function (t) { html += '' + '' + '' + ''; }); html += '
TitleTenureAdded
'+WL.escapeHtml(t.title_number)+''+WL.escapeHtml(t.tenure||'')+''+WL.escapeHtml((t.added_datetime||'').substring(0,16))+'
'; $('#wl-landreg-list').html(html); }); }; $(document).on('click', '#wl-landreg-add', function () { var title = $.trim($('#wl-landreg-title').val()); var tenure = $('#wl-landreg-tenure').val(); if (!title) return; $.post('fn/wayleave_landreg_save.php', { agreement_id: state.agreementId, action: 'add', title_number: title, tenure: tenure }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Add failed', 'err'); return; } $('#wl-landreg-title').val(''); $('#wl-landreg-tenure').val(''); WL.loadLandreg(); }); }); $(document).on('click', '.wl-landreg-remove', function () { var t = $(this).data('title'); $.post('fn/wayleave_landreg_save.php', { agreement_id: state.agreementId, action: 'remove', title_number: t }, null, 'json').done(WL.loadLandreg); }); // ---------- Files ---------- // NOTE: consumes the shared public.file_uploads pipeline via wayleave.vw_file_uploads. // Upload → POST /netplanner/www/fn/file_upload.php with entity=wayleave & wayleave= // List → POST /netplanner/www/fn/wayleave_files_load.php (returns data_file_attachments[]) // Download→ POST /netplanner/www/fn/serve_file.php (blob response, same shape project_edit uses) WL.loadFiles = function () { $.post('fn/wayleave_files_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; var rows = resp.data_file_attachments || []; if (!rows.length) { $('#wl-files-list').html('No files uploaded.'); return; } var html = '' + '' + '' + ''; rows.forEach(function (f) { html += '' + '' + '' + '' + '' + '' + '' + ''; }); html += '
FileDescriptionCategorySizeUploadedBy
'+WL.escapeHtml(f.file_name||'')+''+WL.escapeHtml(f.file_description||'')+''+WL.escapeHtml(f.file_category||'')+''+WL.escapeHtml(f.file_size||'')+''+WL.escapeHtml(f.file_upload_datetime||'')+''+WL.escapeHtml(f.username||'')+'
'; $('#wl-files-list').html(html); }); }; // Download via POST blob flow (mirrors www/js/project_edit_v2_fileuploads.js::downloadFile) $(document).on('click', '.wl-file-dl', function () { var fileId = $(this).data('file-id'); var body = 'file_id=' + encodeURIComponent(fileId) + '&entity=wayleave' + '&entity_id=' + encodeURIComponent(state.agreementId); fetch('fn/serve_file.php', { method: 'POST', headers: { 'Content-Type': 'application/x-www-form-urlencoded' }, body: body }).then(function (r) { if (!r.ok) throw new Error('HTTP ' + r.status); var cd = r.headers.get('Content-Disposition') || ''; var m = cd.match(/filename="(.+)"/); var name = m ? m[1] : 'download'; return r.blob().then(function (blob) { return { blob: blob, name: name }; }); }).then(function (x) { var url = window.URL.createObjectURL(x.blob); var a = document.createElement('a'); a.href = url; a.download = x.name; document.body.appendChild(a); a.click(); window.URL.revokeObjectURL(url); document.body.removeChild(a); }).catch(function (err) { WL.toast('Download failed: '+err.message, 'err'); }); }); // Upload via the shared file_upload.php endpoint. // IMPORTANT: the endpoint reads *all* entity IDs from POST (project, stocklist, account, // opportunity, wayleave) but only uses the one matching `entity`. Send blanks for the rest. $(document).on('submit', '#wl-file-form', function (e) { e.preventDefault(); var fileInput = document.getElementById('wl-file-input'); var desc = $.trim($('#wl-file-desc').val()); var cat = $.trim($('#wl-file-cat').val() || ''); if (!fileInput || !fileInput.files.length) { WL.toast('Choose a file first', 'err'); return; } if (!desc) { WL.toast('Description is required', 'err'); return; } var fd = new FormData(); fd.append('file', fileInput.files[0]); fd.append('file_description', desc); fd.append('file_category', cat); fd.append('entity', 'wayleave'); fd.append('wayleave', state.agreementId); fd.append('project', ''); fd.append('stocklist', ''); fd.append('account', ''); fd.append('opportunity',''); $.ajax({ url: 'fn/file_upload.php', method: 'POST', data: fd, processData: false, contentType: false, dataType: 'json' }).done(function (resp) { if (!resp || !resp.success) { WL.toast('Upload failed: '+(resp&&resp.error||''), 'err'); return; } WL.toast('Uploaded'); $('#wl-file-input').val(''); $('#wl-file-desc').val(''); $('#wl-file-cat').val(''); WL.loadFiles(); // The shared endpoint has already written a wayleave.agreement_journal row, so refresh journal too. if (typeof WL.loadJournal === 'function') WL.loadJournal(); }).fail(function (xhr) { WL.toast('Upload failed HTTP '+xhr.status, 'err'); }); }); // ---------- Projects ---------- WL.loadProjects = function () { $.post('fn/wayleave_projects_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; if (!projectsTable) { projectsTable = new Tabulator('#wl-projects-table', { data: resp.projects, layout: 'fitColumns', placeholder: 'No projects overlap with this agreement', columns: [ { title: 'Project', field: 'project_name', widthGrow: 3, formatter: function (c) { var d=c.getRow().getData(); return ''+WL.escapeHtml(d.project_name||'')+''; }}, { title: 'Status', field: 'status', widthGrow: 1 }, { title: 'Overlap', field: 'overlap_count', widthGrow: 1, hozAlign: 'right' }, ] }); } else { projectsTable.replaceData(resp.projects); } }); }; // ---------- Releases ---------- WL.loadReleases = function () { $.post('fn/wayleave_releases_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; if (!releasesTable) { releasesTable = new Tabulator('#wl-releases-table', { data: resp.releases, layout: 'fitColumns', placeholder: 'No releases', columns: [ { title: 'UPRN', field: 'uprn', width: 140 }, { title: 'Address', field: 'address_full' }, { title: 'Release', field: 'release_date', width: 130, editor: 'input' }, { title: 'Sales Ref', field: 'sales_ref', width: 140, editor: 'input' }, { title: 'PIC Ref', field: 'pic_ref', width: 140, editor: 'input' }, { title: 'Notes', field: 'notes', editor: 'input' }, { title: '', width: 90, formatter: function (c) { return ''; }, cellClick: function (e, cell) { if (!$(e.target).hasClass('wl-release-del')) return; var d = cell.getRow().getData(); $.post('fn/wayleave_releases_save.php', { agreement_id: state.agreementId, action: 'delete', uprn: d.uprn }, null, 'json').done(function (resp) { if (resp && resp.success) WL.loadReleases(); }); }}, ], cellEdited: function (cell) { var d = cell.getRow().getData(); $.post('fn/wayleave_releases_save.php', { agreement_id: state.agreementId, action: 'upsert', uprn: d.uprn, release_date: d.release_date || '', sales_ref: d.sales_ref || '', pic_ref: d.pic_ref || '', notes: d.notes || '' }, null, 'json').done(function (resp) { if (!resp || !resp.success) WL.toast('Release save failed', 'err'); else WL.toast('Release saved'); }); } }); } else { releasesTable.replaceData(resp.releases); } }); }; // ---------- Map ---------- WL.loadMap = function () { if (!viewMap) { viewMap = L.map('wl-view-map').setView([54.5, -2.0], 6); L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', { attribution: '© OpenStreetMap', maxZoom: 19 }).addTo(viewMap); viewPolyLayer = L.featureGroup().addTo(viewMap); viewPointLayer = L.featureGroup().addTo(viewMap); } setTimeout(function () { viewMap.invalidateSize(); }, 150); $.post('fn/wayleave_map_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; viewPolyLayer.clearLayers(); viewPointLayer.clearLayers(); L.geoJSON(resp.polygons, { style: { color: '#0d6efd', weight: 2, fillOpacity: 0.1 } }).eachLayer(function (l) { viewPolyLayer.addLayer(l); }); (resp.premises.features || []).forEach(function (f) { var c = f.geometry.coordinates; var p = f.properties; var color = p.released ? '#198754' : (p.sources.indexOf('polygon') !== -1 ? '#0d6efd' : (p.sources.indexOf('direct') !== -1 ? '#20c997' : '#ffc107')); var m = L.circleMarker([c[1], c[0]], { radius: 4, color: color, fillColor: color, fillOpacity: 0.7, weight: 1 }); m.bindPopup('UPRN: '+WL.escapeHtml(p.uprn)+'
'+WL.escapeHtml(p.address||'')+'
Sources: '+(p.sources||[]).join(', ')+(p.released?'
Released':'')); viewPointLayer.addLayer(m); }); var bounds = viewPolyLayer.getBounds(); if (!bounds.isValid()) bounds = viewPointLayer.getBounds(); if (bounds.isValid()) viewMap.fitBounds(bounds, { padding: [20,20] }); }); }; // ---------- Journal ---------- WL.loadJournal = function () { $.post('fn/wayleave_load.php', { agreement_id: state.agreementId }, null, 'json') .done(function (resp) { if (!resp || !resp.success) return; state.journal = resp.journal; renderJournal(); }); }; function renderJournal() { var list = state.journal || []; if (!list.length) { $('#wl-journal-list').html('No journal entries yet.'); return; } var html = '
    '; list.forEach(function (j) { var badge = j.is_system ? 'system' : ''; html += '
  • ' + '
    '+badge+WL.escapeHtml((j.log_datetime||'').substring(0,16))+' · '+WL.escapeHtml(j.user_name||'')+'
    ' + '
    '+WL.escapeHtml(j.log_text)+'
    ' + '
    • '; }); html += '
'; $('#wl-journal-list').html(html); } $(document).on('submit', '#wl-journal-form', function (e) { e.preventDefault(); var t = $.trim($('#wl-journal-text').val()); if (!t) return; $.post('fn/wayleave_journal_save.php', { agreement_id: state.agreementId, journal_text: t }, null, 'json').done(function (resp) { if (!resp || !resp.success) { WL.toast('Journal save failed', 'err'); return; } $('#wl-journal-text').val(''); WL.loadJournal(); }); }); })(); ``` - [ ] **Step 2: End-to-end verification** Logged in, with a polygon drawn and approved in Task 17: 1. **Premises tab** — should show all resolved UPRNs with source tags (polygon/direct/stocklist). 2. **Land Registry tab** — add title "AGL12345" tenure "Freehold". Verify it renders; remove it. 3. **Files tab** — upload a small text file + description. Verify it appears with a download link; click download to confirm it serves. 4. **Projects tab** — if any project's `project_premises` include a UPRN now in the agreement's resolved list, the project should appear with an overlap count. If none, the table says "No projects overlap…". 5. **Releases tab** — type a release_date + sales_ref for a row inline; verify DB: ```bash psql -U postgres -d netplanner -c "SELECT uprn, release_date, sales_ref FROM wayleave.agreement_releases WHERE agreement_id=1;" ``` 6. **Map tab** — Leaflet renders polygon (blue outline) and resolved premises (coloured circle markers). Released premises are green. 7. **Journal tab** — system entries for every action taken are listed. Post a free-text note "Smoke-test journal entry"; verify it appears at the top with no system badge. --- ## Task 19: Global JS/CSS Dependency Check **Files:** - Read / optionally modify: `www/html/head_nav_default.php` The editor and list pages rely on these globally-loaded assets: - jQuery 3.6 - Bootstrap 5 (CSS + JS bundle, including Modal) - Tabulator.js - Leaflet + Leaflet-Geoman (only editor Coverage + Map tabs) If any are missing in `head_nav_default.php`, add them via `` / `