# Key Databases for Order Processing and Stock Tracking System

## Overview
The order processing and stock tracking system relies on a series of interconnected databases that manage customer orders, inventory, deliveries, invoicing, and related processes. The system uses PHP 5.6 code with MySQL databases. Below are the key databases identified from the core pages, along with their interactions.

## Source Pages Analyzed
The following PHP pages were analyzed to identify the key databases and their interactions:

- `intranet/databases/humanmed/customerorders.php` - Customer orders management
- `intranet/databases/humanmed/orderdetails.php` - Order details and editing
- `intranet/databases/humanmed/displaystarted.php` - Order status display
- `intranet/databases/activelistlive/index.php` - Active orders list
- `intranet/databases/stock_picking/pickorder.php` - Stock picking interface
- `intranet/databases/stock_picking/pickbarcode.php` - Barcode scanning for picking
- `intranet/databases/review_neworders/view_revieworderid.php` - Order review screen
- `intranet/databases/purchaseorders/previewinvoice.php` - Invoice preview and editing

## Core Order Management Databases

### 1. `viamedquote_orders`
**Purpose**: Main orders table storing all customer orders, quotes, and proformas.
**Key Fields**:
- `id`: Order ID
- `company_id`: Customer company ID
- `contact_id`: Contact person ID
- `hostcompany`: Host company ID
- `quote_proforma`: Type (0=Order, 1=Quote, 2=Proforma, 3=Potential)
- `order_stage`: Current stage (0=Entry, 1=Checked, 2=Ready, 3=Complete)
- `customer_ref`: Customer reference
- `due`: Due date
- `prepaid`: Prepaid flag
- `holdpicking`: Hold picking flag
- `holdinvoices`: Hold invoices flag
- `incoterms`: Incoterms ID
- `incotermslocation`: Incoterms location
- `delivery_company_id`: Delivery address ID
- `invoice_company_id`: Invoice address ID
- `vatexempt`: VAT exempt flag
- `notes`: Order notes
- `po_docid`: PO document ID
- `paperworkname`: Staff handling paperwork
- `createdon/createdby`: Creation info
- `modifiedon/modifiedby`: Modification info

**Interactions**:
- Links to `viamedquote_orders_details` via `id`
- Links to `viamedquote_orders_deliverys` via `id`
- Links to `viamedquote_orders_invoices` via `id`
- References `viamedquoteaddress` via `company_id`
- References `viamedquotecontact` via `contact_id`

### 2. `viamedquote_orders_details`
**Purpose**: Detailed line items for each order.
**Key Fields**:
- `orderid`: Links to `viamedquote_orders.id`
- `stockref`: Stock reference
- `qty`: Ordered quantity
- `qty_shipped`: Quantity shipped
- `unitprice`: Unit price
- `duedate`: Due date for this line
- `backorderstatustext`: Backorder status
- `pricelist`: Price list used
- `paperworkorder`: Display order

**Interactions**:
- Child of `viamedquote_orders`
- Links to `stocklist_tarriffs` via `stockref`
- Used in picking process via `serialnumberpicking`

### 3. `viamedquote_orders_deliverys`
**Purpose**: Delivery records for orders.
**Key Fields**:
- `ordernumber`: Links to `viamedquote_orders.id`
- `deliverynumber`: Delivery sequence number
- `trackingnumber`: Tracking number
- `finalweight`: Package weight
- `upspackcode`: UPS package type
- `numberparcels`: Number of parcels
- `deliveredon`: Delivery date
- `invoicedon`: Invoice date
- `shipoption`: Shipping method
- `weightsmemo`: Weight/dimensions notes
- `stopinvoice/stopdeliveryconfirmation`: Hold flags

**Interactions**:
- Child of `viamedquote_orders`
- Links to `viamedquote_orders_invoices` via ordernumber and deliverynumber
- Used in `serialnumberpicked` for tracking picked items

## Stock Tracking Databases

### 4. `Serialnumbers`
**Purpose**: Individual barcode/serial number tracking for all stock items.
**Key Fields**:
- `id`: Barcode ID
- `Stock_ref`: Stock reference
- `companyid`: Host company ID
- `stocklocation2`: Current location
- `Invoicenumber`: Invoice number (when sold)
- `Ordernumber`: Order number (when allocated)
- `Dateout`: Date sold
- `Bookedoutby`: User who booked out
- `expiredate`: Expiry date
- `Datebookedin`: Date received
- `srsnumber`: SRS number
- `srsscanedtoby/srsscanedtoon`: SRS scan info

**Interactions**:
- Links to `stocklist_tarriffs` via `Stock_ref`
- Updated during picking process
- Referenced in `serialnumberpicking` and `serialnumberpicked`
- Location tracked via `serialnumberlocation`

### 5. `serialnumberpicking`
**Purpose**: Temporary table for items being picked for an order.
**Key Fields**:
- `stock_ref`: Stock reference
- `serialnumberid`: Links to `Serialnumbers.id`
- `ordernumber`: Order ID
- `pickedby`: User who picked
- `pickedon`: Pick date
- `status`: Pick status
- `deliverynumber`: Delivery number

**Interactions**:
- Links `Serialnumbers` to orders during picking
- Converted to `serialnumberpicked` when order is sent

### 6. `serialnumberpicked`
**Purpose**: Permanent record of picked items for delivered orders.
**Key Fields**:
- Similar to `serialnumberpicking` but permanent
- `matchedon`: When matched to delivery

**Interactions**:
- Created from `serialnumberpicking` when order is shipped
- Used for delivery notes and invoices

### 7. `stocklist_tarriffs`
**Purpose**: Master stock list with pricing and attributes.
**Key Fields**:
- `stock_ref`: Stock reference
- `hostcompany_id`: Host company
- `description`: Item description
- `shelflife`: Shelf life in months
- `qa_type`: QA requirement (0=none, 1=required)
- `productstop`: Product discontinued flag
- `SUPERSEDED`: Superseded by
- `reqarequiredmonths`: Re-QA interval
- `whilestockslast`: While stocks last flag

**Interactions**:
- Referenced by `Serialnumbers` and order details
- Contains pricing information per host company

## Invoicing Databases

### 8. `viamedquote_orders_invoices`
**Purpose**: Invoice headers.
**Key Fields**:
- `orderid`: Order ID
- `deliveryid`: Delivery ID
- `invoiceref`: Invoice reference (e.g., R123-1)
- `invoicesenttocustomeron`: When sent to customer
- `invoicecommitedon`: When committed
- `goodstotal`: Invoice total

**Interactions**:
- Child of `viamedquote_orders_deliverys`
- Parent to `viamedquote_orders_invoices_details`

### 9. `viamedquote_orders_invoices_details`
**Purpose**: Invoice line items.
**Key Fields**:
- `orderid`: Order ID
- `deliveryid`: Delivery ID
- `invoiceref`: Invoice reference
- `stockref`: Stock reference
- `qty`: Quantity invoiced
- `unitprice`: Unit price
- `vat`: VAT amount

**Interactions**:
- Child of `viamedquote_orders_invoices`
- Links to `Serialnumbers` via barcode tracking

## Supporting Databases

### 10. `viamedquoteaddress`
**Purpose**: Customer/company addresses.
**Key Fields**:
- `ID`: Address ID
- `COMPANY`: Company name
- `ADDRESS1/2/3`: Address lines
- `CITY/STATE/ZIP/COUNTRY`: Location
- `Opera_*`: Opera system integration fields
- `incotermsdefaultconsign`: Default incoterms
- `Vat_Number`: VAT number
- `credittype`: Credit type
- `Opera_Pricelist`: Default price list

**Interactions**:
- Referenced by orders for delivery/invoice addresses
- Contains customer-specific defaults

### 11. `viamedquotecontact`
**Purpose**: Customer contacts.
**Key Fields**:
- `id`: Contact ID
- `COMPANY_ID`: Links to `viamedquoteaddress.ID`
- `CONTACT`: Contact name
- `DEPARTMENT`: Department
- `PHONE1/PHONE2`: Phone numbers
- `UPOSITION`: Position
- `hideme`: Hide flag

**Interactions**:
- Referenced by orders for contact person
- Grouped by company

### 12. `backorders2`
**Purpose**: Backorder tracking.
**Key Fields**:
- `Order_number`: Order ID
- `Stock_ref`: Stock reference
- Other backorder details

**Interactions**:
- Tracks outstanding deliveries
- Linked to orders and stock

### 13. `viamed_memos_history`
**Purpose**: Customer memos and notes.
**Key Fields**:
- `companyid`: Company ID
- `memotype`: Memo type (1=General, 2=Account, 4=Order)
- `memo`: Memo text
- `addedon/addedby`: Creation info

**Interactions**:
- Referenced by company ID
- Used for customer-specific notes

### 14. `viamedquotestatus`
**Purpose**: Order status updates.
**Key Fields**:
- `orderid`: Order ID
- `status`: Status text
- `contact`: Contact info
- `memo`: Status memo
- `timestampraised`: Timestamp

**Interactions**:
- Child of `viamedquote_orders`
- Tracks order progress notes

## System Integration Databases

### 15. `global_companys`
**Purpose**: Host company information.
**Key Fields**:
- `id`: Company ID
- `name`: Company name
- `prefix`: Order prefix
- `logo*`: Logo references

**Interactions**:
- Defines host companies
- Referenced throughout system

### 16. `pw_companys`
**Purpose**: User permissions per company.
**Key Fields**:
- `vui`: User ID
- `company_id`: Company ID

**Interactions**:
- Controls user access to company data

### 17. `ISO_quality_manual_files`
**Purpose**: Document storage.
**Key Fields**:
- `docid`: Document ID
- `filename`: File name
- `description`: Description
- `archived`: Archive flag

**Interactions**:
- Stores PO docs, VAT certificates, etc.
- Linked to orders

## Database Relationships Summary

```
viamedquote_orders (main orders)
├── viamedquote_orders_details (line items)
├── viamedquote_orders_deliverys (deliveries)
│   └── viamedquote_orders_invoices (invoices)
│       └── viamedquote_orders_invoices_details (invoice lines)
├── viamedquoteaddress (customer addresses)
├── viamedquotecontact (customer contacts)
├── Serialnumbers (stock tracking)
│   ├── serialnumberpicking (picking process)
│   └── serialnumberpicked (picked items)
├── stocklist_tarriffs (stock master)
├── viamed_memos_history (customer memos)
└── viamedquotestatus (status updates)
```

## Key System Flows

1. **Order Creation**: `viamedquote_orders` → `viamedquote_orders_details`
2. **Stock Allocation**: `Serialnumbers` → `serialnumberpicking`
3. **Picking Process**: `serialnumberpicking` → `serialnumberpicked`
4. **Delivery**: `viamedquote_orders_deliverys` records delivery
5. **Invoicing**: `viamedquote_orders_invoices` → `viamedquote_orders_invoices_details`
6. **Stock Movement**: `Serialnumbers` location updates via `serialnumber_movements`

This structure supports the complete order-to-cash cycle with integrated stock tracking and quality control processes.