================================================================================
MPUMALANGA PVC - DELIVERY NOTES MODULE
================================================================================
Last Updated: 2026-03-26
Path: /app/delivery_note/
================================================================================

WHAT THIS SECTION DOES
-----------------------
Creates delivery notes to accompany goods when they are delivered or collected
by a client. Each delivery note is linked to an invoice and contains a list of
stock items being delivered (code, description, size, panels) plus an optional
note. A PDF is generated for printing and signing.

DATABASE TABLES
---------------
delivery_note:
  record_id   int   - Primary key (NO AUTO_INCREMENT)
  quote_id    int   - STORES INVOICE ID (misleading column name - always invoice)
  note        text  - Optional delivery note text

delivery_note_list:
  record_id          int
  delivery_note_id   int   - FK to delivery_note.record_id
  stock_code         text  - Stock code string (not a FK - stored as text)
  description        text  - Stock description text
  size_m             text
  pannels            text

IMPORTANT DATABASE NOTE:
  delivery_note.quote_id actually stores an invoice_id. The column was named
  "quote_id" historically but has always stored the invoice record_id.
  All code treats it as invoice_id consistently.

REQUIRED ALTER TABLE:
  If delivery_note_list is missing the description column, saves will fail.
  Run this on the DB if you see SQL errors on save:
  ALTER TABLE delivery_note_list ADD description varchar(2550) NOT NULL AFTER stock_code;

================================================================================
FILES
================================================================================

home.php
--------
Lists all delivery notes with an Edit button.
Uses: new table("delivery_note") + add_action_button("edit_delivery_note.php")

--------------------------------------------------------------------------------
add_delivery_note.php
---------------------
What it does:
  Form to create a new delivery note. User selects an invoice, the stock
  datalist is filtered to items on that invoice, and rows can be added/edited.

How it works:
  On load:
    - Calculates next delivery note number (last record_id + 1, or 1).
    - Calls get_stock_datalist("stock_list") for the full stock datalist.
    - Also creates an empty stock_desc_list datalist for description-first lookup.

  Invoice selection flow:
    - User selects invoice from datalist.
    - Change listener extracts data-id to get invoice record_id.
    - Calls loadInvoiceStock(invoice_id).

  loadInvoiceStock(invoice_id)
    - XHR POST to get_invoice_stock.ajax.php with invoice_id.
    - Response: one line per stock item on the invoice, tilde-separated:
      CODE~Description~UOM~Price
    - Builds two datalists from the response:
      stock_list: CODE~Desc~UOM~Price (search by code)
      stock_desc_list: Desc~CODE~UOM~Price (search by description)
    - Replaces the existing datalist options (scopes to invoice items only).

  Line items table:
    - Code input uses stock_list datalist.
    - Description input uses stock_desc_list datalist.
    - fill_description(input, i): when code selected, fills description from ~ split.
    - fill_from_desc(input, i): when description selected, fills code from ~ split.
    - No price or UOM shown - delivery notes only track code, description, size, panels.

JS Functions:
  loadInvoiceStock(invoice_id)   - Filters datalists to invoice items.
  fill_description(input, i)     - Fills description when code is chosen.
  fill_from_desc(input, i)       - Fills code when description is chosen.
  add_row()                      - Adds a new empty line item row.
  delete_row(el)                 - Removes the row.
  save(url)
    - Validates invoice_id is set.
    - Collects all inputs as payload.
    - Submits POST to save_delivery_note.php.

--------------------------------------------------------------------------------
save_delivery_note.php
----------------------
What it does:
  Inserts a new delivery note header and all list rows, redirects to home.php.

How it works:
  - Sanitises the note field.
  - Inserts into delivery_note (quote_id=invoice_id, note).
  - Gets back delivery_note_id.
  - Loops over $_POST['stock_code']:
    - Each stock_code may contain "CODE~Description~..." from the datalist.
      Extracts just the code using explode('~', $raw_code)[0].
    - Each description may contain "Desc~CODE~..." from the desc datalist.
      Extracts just the description using explode('~', $raw_desc)[0].
    - Inserts into delivery_note_list.
  - Redirects to home.php.

--------------------------------------------------------------------------------
edit_delivery_note.php
----------------------
What it does:
  Pre-filled edit form for an existing delivery note. Shows existing list rows
  and allows changes. Has a VIEW PDF button.

How it works:
  - Loads delivery note by $_GET['record_id'].
  - Loads delivery_note_list rows and renders each as an editable row.
  - On page load, calls loadInvoiceStock() for the existing invoice_id to
    restore the filtered datalists.
  - Shows VIEW PDF button (opens delivery_note.pdf.php in new tab).
  - Saves to update_delivery_note.php.

--------------------------------------------------------------------------------
update_delivery_note.php
------------------------
What it does:
  Updates the delivery note header, deletes all list rows, re-inserts from POST.

How it works:
  - Sanitises note.
  - Updates delivery_note (quote_id=invoice_id, note).
  - DELETEs all delivery_note_list rows for this delivery_note_id.
  - Re-inserts all rows (same tilde-stripping logic as save).
  - Redirects to home.php.

--------------------------------------------------------------------------------
get_invoice_stock.ajax.php
--------------------------
What it does:
  AJAX endpoint. Receives invoice_id via POST. Returns one line per stock item
  on that invoice in tilde-separated format.

Query used:
  SELECT il.*, s.code, s.name, s.unit_of_measure, s.retail
  FROM invoice_list il JOIN stock s ON s.record_id = il.stock_id
  WHERE il.invoice_id = {invoice_id}

Response format (one line per item, newline separated):
  CODE~Description~UOM~retail_price

Called by: loadInvoiceStock() in add and edit delivery note pages.

--------------------------------------------------------------------------------
delivery_note.pdf.php
---------------------
What it does:
  Generates a PDF delivery note for a specific delivery note (?record_id=X).
  Uses ob_start()/ob_end_clean() to prevent any accidental output before FPDF.

PDF contents:
  - "DELIVERY NOTE" title.
  - Client details (from delivery_note -> invoice -> client).
  - Company details.
  - Today's date and the linked invoice number.
  - Delivery details block (from invoice if filled).
  - Line items table: Code, Description, Size, Panels.
    Data comes from delivery_note_list (stock_code, description columns).
  - Note text block (if delivery_note.note is not empty).
  - "RECEIVED IN GOOD CONDITION" statement.
  - Signature lines: Client Name/Signature and Delivery/Pick-up Checked By.

================================================================================
KNOWN ISSUES SUMMARY
================================================================================
1. delivery_note.quote_id stores an invoice_id - confusing column name.
2. delivery_note_list may be missing the description column - requires ALTER.
3. No price information on delivery notes (intentional - but limits usefulness).
4. SQL injection throughout.

================================================================================