# Product Need Calculation

Product Need view shows product stock balance development and future needs based on sales orders, production orders, purchase orders, and material requirements.

## Views

Two views are available via the Balance/Need tab:

| Tab | Description |
|-----|-------------|
| Balance | Weekly summary per product with purchase order creation |
| Need | Detailed row-level view showing each event and calculation |

## Checkbox Settings

These checkboxes control what is calculated and displayed. Clicking any calculation checkbox triggers an update.

### Calculation Checkboxes

| Checkbox | Default | Description |
|----------|---------|-------------|
| Negative start balance | off | Include negative starting stock balance in running balance |
| Negative end balance | on | Add need when final balance is negative |
| Minimum stock | on | Add need when balance falls below product minimum stock |
| Action limit | on | Warn when balance falls below product action limit |
| Estimate order | on | Include estimate orders in calculation |
| Critical date | off | Calculate critical date when stock runs out |
| Only sales need | off | Calculate only from sales orders, disables minimum stock, action limit, negative end balance, and critical date |

### Display Checkboxes

| Checkbox | Default | Description |
|----------|---------|-------------|
| Only need | on | Show only products that have need (negative balance or need down) |
| Hide empty | on | Hide products with zero balance and no transactions. Disabled when Only need is on |
| Has error | off | Show only products that have errors |
| Select upper | on | Highlight upper level (parent) products when row is selected |
| Select lower | on | Highlight lower level (child) products when row is selected |
| Chart current | off | Show only the chart for the currently selected product |
| Create schedule | off | Mark matching production orders as scheduled |
| Virtual production order | off | Create virtual production order IDs for need rows without existing orders |

### Advanced Checkboxes

| Checkbox | Default | Description |
|----------|---------|-------------|
| Incremental | off | Send only changed data on updates |
| Reload data | off | Force full data reload from database |
| Old work date | off | Use old work date in calculation |
| Max down | 500 | Maximum iterations for cascade loop |

## Product-Level Settings

Each product has settings that control how it participates in the calculation. These are set on the product record:

| Setting | Description |
|---------|-------------|
| Need calculation in use | When off, product is excluded from calculation. Auto-enabled when not set. Also off when product itself is marked not in use |
| Stock balance | Current stock balance, used as starting balance |
| Action limit | Per-product action limit threshold |
| Minimum stock | Per-product minimum stock level |
| Critical end date | Saved critical end date, compared against calculated value |

## Row Types (db column)

Each row in the need calculation has a source type shown in the **db** column:

| db value | Description |
|----------|-------------|
| product | Product header row with starting balance |
| work | Production order (positive = incoming) |
| purchase | Purchase order (positive = incoming) |
| sales | Sales order (negative = outgoing) |
| material | Material requirement (negative = outgoing) |
| estimate | Estimate need row (predicted demand from this product) |
| est. | Upper level estimate need (estimate from parent product) |
| act. limit | Action limit warning row |
| actl. | Upper level action limit |
| neg. balance | Negative end balance row (synthetic) |
| balance | Upper level negative end balance |

### Generated Production Order IDs

When **Virtual production order** is on, sales and material rows that have need down but no existing production order get a generated ID:

- Format: `V-{order id}.{order row}` where prefix `V-` is configurable
- Example: sales order `12345` row `2` becomes `V-12345.2`
- These rows show brown color in the grid
- Requires both order id and order row to exist on the row, otherwise an error is shown

When **Create schedule** is on, rows with an existing production order get a suffix appended:

- Suffix is configurable (default: `c`)
- Example: production order `2024/123` becomes `2024/123c`

## Calculation Rules

### Running Balance

For each product, rows are sorted by date, then by type, then by order. Running balance is calculated:

- If start balance is negative and **Negative start balance** is off: first event row balance = row amount only (start balance ignored)
- Otherwise: balance = previous balance + row amount

### Need Down

Need down is the amount that must be procured or produced to cover a shortage. It flows down the product tree from finished products to raw materials.

#### When need down is set

| Condition | Need down amount | Notes |
|-----------|-----------------|-------|
| Production order row (positive amount) | amount | Only for products that have materials in the work structure |
| Balance goes negative (negative amount row) | -amount | Only for products in the product tree |
| Schedule row | abs(amount) | When previous balance minus amount would be negative |
| Last row, negative end balance on | gap to previous need down | Covers the remaining shortage |
| Last row, minimum stock on (final pass only) | minimum stock - balance | When balance is below minimum stock |
| Product with only start balance, negative, no events | -balance | Synthetic row created with amount 0 |

#### When need down is cleared

- Row amount is zero (with error message for production/sales/material rows)
- Product has need calculation in use = off
- Row source database is marked as not in use
- After calculation: need down less than or equal to 0 is removed
- If balance has recovered to or above previous need down level, need down is cleared

#### Σ info

Σ appears when need down is reduced because part of the shortage was already counted in an earlier row. The Σ value shows the amount already counted.

Example: product starts at balance 0 and has three sales rows:

| Row | Amount | Balance | Need down before Σ | Lowest negative | Action |
|-----|--------|---------|----------------------|-----------------|--------|
| Sales A | -5 | -5 | 5 | -5 | New low, need down = 5 |
| Production | +3 | -2 | - | -5 | Balance recovered, no new need |
| Sales B | -4 | -6 | 4 | -6 | New low, need down reduced to 1 |

At row 3, sales B creates need down of 4. But sales A already caused need down of 5 which covers most of the shortage. Need down is reduced to 1 — only the new part. Σ shows `Σ5` meaning 5 was already counted.

Σ info is shown only when the reduction amount differs from the final need down value.

### Negative Start Balance

When **on**:

- Negative starting stock balance is included in the running balance
- All events add to the balance which may start negative
- The first row shows info `"negative"` when header balance is negative
- Last row: if balance is still negative and need down exists, it is increased to cover the full shortage

When **off**:

- If start balance is negative, the first event row ignores it: balance = row amount only
- The running balance starts from zero (from the first event)
- Negative end balance still works on the last row balance (which may be negative from events)

### Negative End Balance

When **on** and product has no event rows (only start balance):

- Creates a synthetic row with amount 0 and balance = start balance
- Sets need down = -balance if start balance is negative and negative start balance is on

When **on** and product has event rows:

- On the last row: if balance < 0, sets need down to cover the remaining shortage
- The "neg. balance" cause label is shown in the Info column

### Minimum Stock

When **on** (final pass only):

- On the last row: if balance < minimum stock, sets need down = minimum stock - balance
- Combined with negative end balance: if both apply, the larger need down is used
- Info column shows "min. stock" or "neg. balance, min. stock"

### Action Limit

When **on**:

- Warning is shown when product balance falls below action limit
- Action limit value comes from the product record

### Critical Date

When **on**:

- Calculates the date when stock balance first goes to zero or below
- Compares calculated critical date against product's saved critical end date
- If they differ, an error is shown on the product

### Only Sales Need

When **on**:

- Calculation only uses sales orders (no production orders, purchase orders, or material requirements)
- Starting balance is set to 0 instead of stock balance
- Automatically disables: minimum stock, action limit, negative end balance, critical date

## Estimate Need

Estimate need predicts future product demand from historical sales. It calculates a daily consumption rate over a lookback period, then inserts negative rows to represent expected future demand.

Two calculation methods:

| Method | Description |
|--------|-------------|
| Last | Simple average: total delivered amount divided by lookback days (default 78 days). Growth rate is always 1.0 |
| Period | Compares a date range this year to the same range last year. Auto-calculates year-over-year growth rate. Period is defined by before/after days around an anchor date |

Per-product group overrides: each product group can use a different method and growth rate multiplier. Products not in the override table use the global settings.

The daily rate formula: `consumption / days × auto growth rate × manual growth rate`. Estimate rows are inserted between existing product rows, splitting when material rows land inside their date range. This ensures correct intermediate balance calculations during the cascade loop.

## Cascade Loop

Need down flows from finished products to raw materials. Each step may create new need down that triggers the next level.

### Flow

```mermaid
flowchart TD
    L1["Calculate balance for all changed products"] --> L2{"Need down found?"}
    L2 -->|yes| L3["Load lower level materials from database"]
    L3 --> L4["Insert cascade rows into sub-material arrays"]
    L4 --> L5["Recalculate affected sub-materials"]
    L5 --> L1
    L2 -->|no| L6{"Last pass features enabled?"}
    L6 -->|yes| L7["One final calculation pass"]
    L7 --> L8["Apply minimum stock and negative end balance"]
    L6 -->|no| DONE["Done"]
    L8 --> DONE
```

### Example

Product A has BOM requiring Material B. Product A gets need down.

1. Balance calculation for Product A produces need down
2. Database loads Material B requirement with BOM quantity
3. Cascade row inserted into Material B: **db** shows `"work"`, **Origin** shows `"work -"`, **From** links back to Product A's row
4. Balance calculation for Material B runs, may produce its own need down
5. If Material B has sub-material C, the process repeats for C
6. Loop ends when no new need down is produced or Max down limit reached

### Cascade row in the grid

Cascade rows appear like normal rows in the sub-material's product view. Key columns:

- **Origin**: shows `"work -"` for material cascade, indicating this need comes from a parent production order
- **From**: links back to the parent product row (clickable to navigate)
- **Amount**: negative value = material quantity x parent's need down amount

### After cascade loop

One final pass runs when any of these are on: minimum stock, action limit, estimate order, negative end balance. This pass applies features that should only run after all levels are calculated.

## Work Phase Backward Calculation

Each product has work phases (from work estimates) that are scheduled backward from the need date. The calculation determines when each phase must start so the product is ready on time.

### Backward Calculation Principle

Phases are processed from last to first. Each phase starts where the next phase ends, going backward in time. Extra time (from resource or product) is subtracted first, then work time + setup time.

```text
need date ──► phase 3 extra ◄── phase 3 work ◄── phase 2 extra ◄── phase 2 work ◄── phase 1 extra ◄── phase 1 work
```

### Material Phase

When `create_material_phase` is on, a material phase is added as the first phase (phase number 0). It represents raw material delivery lead time, not actual production.

#### Material Phase Properties

| Property | Source | Description |
|----------|--------|-------------|
| Work time | `material_work_time` preference (default 0.25h) | Time to place the purchase order. Not multiplied by amount |
| Setup time | 0 | No setup for material phase |
| Extra time | Product `json_data.raw_material_delivery_time` (weeks) converted to hours (`weeks × 7 × 24`). Falls back to `default_raw_material_delivery_time_weeks` preference | Delivery lead time |

#### Resource Recognition

The material phase resource is identified by `material_resource_id` (default `"MATERIAL"`). Existing database phases with resource IDs listed in `material_phase_resource` (e.g. `"RAAKA-AINE"`) are recognized as material phases — no duplicate is created.

Resources listed in `no_amount_multiply_resource` have their work time used as-is, not multiplied by the production amount. This applies to material phases because ordering raw materials takes the same time regardless of quantity.

#### Material Phase and Lower Level Scheduling

The material phase is the first phase but represents delivery lead time, not production. Lower level materials must be ready when production starts, not when the order is placed. Therefore:

- Lower level backward calculation starts from the first real production phase start, not the material phase start
- The material phase start date (when to place the order) = first real phase start minus delivery lead time

### Upper Level Phase Drives Lower Level Timing

When product A (upper level) needs product B (lower level) as material, product B must be ready before the upper level phase that consumes it starts.

The BOM specifies `work_phase_number` — which upper level phase consumes the material. Product B backward calculation starts from that upper phase's `start_dt`, NOT from the passed need date.

#### Lookup Order

1. Try `findPhasePart(parentPhaseArr, needItem.work_phase_number)` — exact phase match from BOM
2. If not found or result is a material phase — fall back to first non-material phase in parent
3. If no parent exists — use the passed need date as starting point

#### Example

Product MIKROAUTO (upper) has phases:

- Phase 1: Hitsaus (welding) — starts wed 2026-06-24
- Phase 2: Maalaus (painting)
- Phase 3: Pakkaus (packaging)

Product AKSELI (lower) is a material of MIKROAUTO. The BOM says `work_phase_number = 1` (Hitsaus consumes it).

AKSELI backward calculation starts from Hitsaus start (wed 2026-06-24), ensuring AKSELI finishes before MIKROAUTO needs it.

```text
MIKROAUTO:  [Material]──[Hitsaus]──────[Maalaus]──[Pakkaus]
                          ▲
AKSELI:        [Pakkaus──Sahaus──Poraus] must finish here
```

If `work_phase_number = 0` (material phase), the material is needed before the first real phase. Lower level calculation starts from the first non-material phase `start_dt` in the parent.

#### Gantt Connection (linked_work_phase)

The `linked_work_phase` field on the child row tells the Gantt chart which parent phase to connect the TOTAL bar to. Format: `{product_id}|{production_order_id}.{work_phase_number}`. This is set in `product-need.lua` where `fromItem` (parent grid row) has full order information including `production_order_id`.

The JS Gantt uses this key in a fixup pass: it replaces the default sequential connection with a direct connection to the matching parent phase bar.

### Preference Settings for Material Phases

All settings are in `plugin/nc-calc-server/rec.json`:

| Preference | Default | Description |
|------------|---------|-------------|
| `create_material_phase` | true | Create material phase for products |
| `material_resource_id` | `"MATERIAL"` | Resource ID for created material phases |
| `material_phase_resource` | `["RAAKA-AINE"]` | Existing database resource IDs recognized as material phases. No duplicate created when found |
| `material_work_time` | 0.25 | Work time in hours for placing purchase order. Not multiplied by amount |
| `default_raw_material_delivery_time_weeks` | 1 | Default delivery lead time in weeks when product has no `raw_material_delivery_time` |
| `no_amount_multiply_resource` | `["MATERIAL", "RAAKA-AINE"]` | Resources whose work time is not multiplied by production amount |

#### Product-Level Delivery Time

Each product can override the default delivery time via `json_data.raw_material_delivery_time` (value in weeks). This field is synced from 4D and shown in the product input form as "RA toimitusaika (vko)".

### Phase Graph Connecting Lines

The phase graph (Gantt chart) shows connecting lines between upper and lower level phases:

- The upper level phase that consumes the material gets a highlight marker
- The highlight matches `work_phase_number` from the BOM — exact phase X, not first real phase
- The lower level total bar represents the complete production time for that product
- Total bar connects to the parent consuming phase via `linked_work_phase`, not to the next sequential item
- Connection line goes from total bar `end2_dt` (last phase tail) to parent phase `start_dt`

## Chart Types

The chart type selector offers these views:

| Chart | Description |
|-------|-------------|
| Stock balance | Line chart showing balance development over time per product |
| Product tree | Tree chart showing product hierarchy and lower level connections |
| Work phases | Gantt chart showing production order work phases and timing |

## Scope Column

The Scope column shows hierarchical relationships for the selected row:

| Symbol | Meaning | Example |
|--------|---------|---------|
| ↓3 | 3 lower level (child) products, all visible | Click Product A, see 3 materials below |
| ↓3/1 | 3 child products total, 1 visible in current filter | 2 materials hidden by filter |
| ↑2 | 2 upper level (parent) products, all visible | Material used in 2 finished products |
| ↑2/1 | 2 parent products total, 1 visible | 1 parent hidden by filter |
| ↓3 ↑2 | 3 children and 2 parents | Both relationships visible |

## Info Column (Info ⇣)

The Info column shows why and how much need down was calculated:

| Info | Meaning | Example |
|------|---------|---------|
| `negative` | Header row has negative start balance | Product stock balance = -10 |
| `neg. balance` | Need down from negative end balance | Last row balance = -8, need down = 8 |
| `min. stock` | Need down from minimum stock requirement | Balance = 2, minimum stock = 10, need down = 8 |
| `neg. balance, min. stock` | Both negative end balance and minimum stock apply | Balance = -8, minimum stock = 5, need down = 8 |
| `Σ5` | Need down reduced because 5 units were already counted | Sales A already caused need down for 5 units |
| `⇣2/3` | 2 of 3 materials in work structure contribute to need down | Product has 3 materials, only 2 have need down |
| `⇣` | Need down set but no material count info available | Product has no work structure data |

## Grid Colors

### Cell Colors

| Color | Column | Condition |
|-------|--------|-----------|
| Semi-bold text | All columns | Product header row |
| Semi-bold + underlined | Balance | Last row of product |
| Grey | Date | Date is before today (not header) |
| Brown | Date | Date is before today (header row) |
| Blue | Date | Header row with valid date |
| Dark grey | Name, Amount (estimate) | Estimate type rows |
| Dark red | Balance (non-last row) | Balance is negative |
| Red | Balance (last row) | End balance is negative |
| Red | Row number | Error that is not a note |
| Green + bold | Work / prod. lot | Existing production order (not virtual) |
| Dark red + bold | Scope | Row highlighted but scope target not visible |
| Dimmed | Scope | Row not highlighted, scope not visible |
| White | Order row, Amount, Need down | Zero value |
| Black | Amount | Positive amount (incoming) |
| Blue | Need down | Need down > 0 |

### Row Colors

| Color | Condition |
|-------|-----------|
| Brown | Virtual production order (not estimate type) |
| Light brown | Estimate order type |
| Teal | Estimate row |
| Golden yellow background | Upper level highlight (parent product selected) |
| Light green background | Lower level highlight (child product selected) |
| Red border + red text | Missing order data |
| Darksalmon | Product excluded from calculation |

### Error Colors

| Color | Condition |
|-------|-----------|
| Green text | Informational note |
| Red text | Error |
| Red background + border | Cell validation failed |

## Incremental Update

When the Incremental checkbox is on, the server tracks data changes using modify timestamps:

- First load returns all data
- Subsequent updates return only changed rows
- Falls back to full reload if incremental update fails
- Each table must have a modify tracking field

## Add Purchase Order (Balance View)

In Balance view, you can create purchase orders:

1. Select one or more rows
2. Set order amount (must be > 0) for each row
3. Set supplier for each row
4. Click "Add order"

Validation requires both fields on all selected rows before saving.
