Metadata-Version: 2.5
Name: bomkit
Version: 0.2.0
Summary: Bill of materials processing utilities
Project-URL: Repository, https://github.com/robsiegwart/bomkit
Author-email: Rob Siegwart <rob@robsiegwart.com>
License: MIT
License-File: LICENSE
Keywords: bom,engineering,manufacturing
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Manufacturing
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Scientific/Engineering
Requires-Python: >=3.8
Requires-Dist: anytree>=2.12
Requires-Dist: openpyxl>=3.1
Requires-Dist: pandas>=2.0
Requires-Dist: textual>=0.50
Provides-Extra: dev
Requires-Dist: pytest; extra == 'dev'
Requires-Dist: pytest-asyncio; extra == 'dev'
Description-Content-Type: text/markdown

# bomkit

![Tests](https://github.com/robsiegwart/bomkit/actions/workflows/test.yml/badge.svg)

A Python tool for flattening multi-level bills of materials (BOMs) from Excel
files. It combines part quantities across the hierarchy, computing total
quantities, minimum purchase quantities, and extended costs. BOM structure can
also be exported as a tree for visualization.

The functionality can be accessed in three ways:

| Method | Description |
|--------|-------------|
| Interactive TUI (terminal-based interface) | Launch with `bomkit [PATH]` |
| API | In Python, use `from bomkit import BOM`, then `BOM.from_folder()` or `BOM.single_file()` |
| Command line | Run with `bomkit [-h] [PATH] [action]` |

## Motivation

When the same part appears in multiple sub-assemblies across your BOM, the total
quantity you need isn't obvious from any single assembly. Flattening resolves
this by aggregating quantities across every level of your product structure.

This is useful for purchasing because many parts are sold in packs larger than
one. Knowing the true total quantity lets you calculate the minimum number of
packs to buy.

Excel is used as the input format since it's widely available and requires no
additional software or server.

## Structure

There are two methods for storing data for parts and assemblies: multi-file or
single file.

### Multi-File

In a separate directory, put an Excel file named *Parts List.xlsx* to serve as
the master parts list \"database\". Then, each additional assembly is described
by a separate .xlsx file. Thus you might have:

    my_project/
       Parts list.xlsx     <-- master parts list
       SKA-100.xlsx        <-- top level/root assembly
       TR-01.xlsx          <-- subassembly
       WH-01.xlsx          <-- subassembly

Root and sub-assemblies are inferred from item number relationships and are not
required to be explicitly identified.

*Parts list.xlsx* serves as the single point of reference for part information.
For example, it may have the following:

| PN        | Name        | Description                    | Supplier               | Supplier PN   | Pkg QTY   | Pkg Price  |
| --------- | ----------- | ------------------------------ | ---------------------- | ------------- | --------- | ---------- |
| SK1001-01 | Deck        | Pavement Pro 9" Maple Deck     | Grindstone Supply Co.  | BRX-02        | 1         | 67.95      |
| SK1002-01 | Truck       | HollowKing Standard Trucks     | Grindstone Supply Co.  | TR1-A         | 1         | 28.95      |
| SK1003-01 | Bearing     | ABEC-7 Steel Bearings          | BoltRun Hardware       | 74295-942     | 1         | 9.95       |
| SK1004-01 | Wheel       | SlickCore 54mm Cruiser Wheels  | Grindstone Supply Co.  | WHL-PRX       | 4         | 44.95      |
| SK1005-01 | Screw       | 10-32, 1", Phillips            | BoltRun Hardware       | 92220A        | 25        | 12.49      |
| SK1006-01 | Nut         | 10-32                          | BoltRun Hardware       | 95479A        | 25        | 9.89       |
| SK1007-01 | Grip Tape   | SuperStick 9"                  | BoltRun Hardware       | GTSS99        | 1         | 8.95       |

For each assembly, all that is required is the part identification number and
quantity which correspond to the following fields:

- PN
- QTY

Example wheel assembly (1 wheel + 2 bearings):

| PN          | QTY   |
| ----------- | ----- |
| SK1004-01   | 1     |
| SK1003-01   | 2     |

Certain fields are used in calculating totals, such as in `BOM.BOM.summary`,
which are:

`Pkg QTY`
  : The quantity of items in a specific supplier SKU (i.e. a bag of 100 screws)

`Pkg Price`
  : The cost of a specific supplier SKU                                        


### Single File

A single Excel file is used to store all part and assembly data through the use
of Excel tabs. There are two supported layouts, described below; which one is
used is detected automatically.

#### Default layout

The conventions for the default single-file layout are the same as the
multi-file approach, with the following exceptions:

- The first (left-most) Excel tab is treated as the Parts List "database",
  regardless of its name
- All tabs/sheets to the right are interpreted as assemblies, with the sheet
  name as the assembly part number (PN)

#### Long layout

For BOMs with many assemblies, a "long" layout keeps everything in exactly two
tabs instead of one tab per assembly:

- **Parts list** — the first tab, containing *both* parts and assemblies in
  one table. A `Type` column identifies each row as one of `Part`, `Assembly`,
  or `COTS` (commercial off-the-shelf); rows for an assembly can leave the
  part-specific fields (`Description`, `Supplier`, etc.) blank.
- **BOMs** — the second tab, containing every assembly's line items together
  in one table, with columns `Assy PN`, `PN`, and `QTY`. `Assy PN` identifies
  which assembly each row belongs to.

This layout is used whenever the workbook has exactly two tabs and the second
is named `BOMs` (case-insensitive); otherwise the default layout above
applies. For example:

**Parts list**

| PN        | Type     | Name           | Description                    | ... |
| --------- | -------- | -------------- | ------------------------------ | --- |
| SK1002-01 | Part     | Truck          | HollowKing Standard Trucks     | ... |
| SK1003-01 | Part     | Bearing        | ABEC-7 Steel Bearings          | ... |
| SK1004-01 | Part     | Wheel          | SlickCore 54mm Cruiser Wheels  | ... |
| TR-01     | Assembly | Truck assembly |                                |     |
| WH-01     | Assembly | Wheel assembly |                                |     |

**BOMs**

| Assy PN | PN        | QTY |
| ------- | --------- | --- |
| TR-01   | SK1002-01 | 1   |
| TR-01   | WH-01     | 2   |
| WH-01   | SK1004-01 | 1   |
| WH-01   | SK1003-01 | 2   |

See `Example/Single-file-long/` for a complete worked example.


## Installation

Install with pip:

```
pip install bomkit
```


## Usage

Set up your data with either the multi-file or single file approach.

### TUI Browser

In a terminal, browse to the folder containing your BOM files and issue the
command `bomkit` with no arguments (or issue a path, e.g.
`bomkit /path/to/your/project` for multi-file, or `bomkit /path/to/bom.xlsx`
for single-file). This will cause it to enter the browser mode where you can
interact with your BOM hierarchy and view derived properties such as the
aggregated parts list and tree structure.

The default screen shows the top-level assembly and its direct-child parts and
assemblies. You can navigate down the hierarchy with the ⬆️ and ⬇️ arrow keys
and by selecting an assembly and pressing `Enter` to view its child parts and
assemblies. Pressing `Enter` on a part will show its details. Use the left arrow
key ⬅️ or `Esc` to return to the parent assembly. You can also access different
views and derived properties using the command keys listed at the bottom of the
screen, such as `t` for a tree view. Assemblies are shown in cyan and bold text.
The top row shows a breadcrumb of the current location in the BOM hierarchy.

The commands at the bottom of the screen are:

- `t` for a tree view of the full BOM hierarchy
- `p` for a part list view (all the parts in the Parts List file
- `a` for a list of all the assemblies in the BOM
- `s` for a summary view (aggregated parts list with total QTY and purchase
  QTY)

![TUI Browser's main screen view](doc/images/TUI-browser-main-screen.png)


### API Usage

```python
from bomkit import BOM

# Multi-file
bom = BOM.from_folder(FOLDER)

# Single file
bom = BOM.single_file(FILENAME)
```

This returns a `BOM` object with properties on it you can retrieve:


`BOM.parts`
  : Get a list of all direct-child parts

  ```
  >>> print(bom.parts)
  [Part SK1001-01, Part SK1005-01, Part SK1006-01, Part SK1007-01] 
  ```

`BOM.assemblies`
  : Get a list of all direct-child assemblies

  ```
  >>> print(bom.assemblies)
  [TR-01]
  ```

`BOM.aggregate`
  : Get the aggregated quantity of each part/assembly from the current
  BOM level down

  ```
  >>> print(bom.aggregate)
  {'SK1001-01': 1, 'SK1005-01': 8, 'SK1006-01': 8, 'SK1007-01': 1, 'SK1002-01': 2, 'SK1004-01': 4, 'SK1003-01': 8}
  ```

`BOM.summary`
  : Get a summary in the form of a DataFrame containing the master parts
  list with each item's aggregated quantity and the required packages
  to buy (`Purchase QTY`) if the `Pkg QTY` field is not 1.

  ```
  >>> print(bom.summary)
          PN       Name                    Description  ... Total QTY Purchase QTY  Subtotal
0  SK1001-01       Deck     Pavement Pro 9" Maple Deck  ...         1            1     67.95
1  SK1002-01      Truck     HollowKing Standard Trucks  ...         2            2     57.90
2  SK1003-01    Bearing          ABEC-7 Steel Bearings  ...         8            8     27.92
3  SK1004-01      Wheel  SlickCore 54mm Cruiser Wheels  ...         4            1     44.95
4  SK1005-01      Screw            10-32, 1”, Phillips  ...         8            1     12.49
5  SK1006-01        Nut                          10-32  ...         8            1      9.89
6  SK1007-01  Grip tape                  SuperStick 9”  ...         1            1      8.95
  ```

`BOM.tree`
  : Return a string representation of the BOM tree hierarchy

  ```
  >>> print(bom.tree)
  SKA-100
  ├── Part SK1001-01        
  ├── TR-01
  │   ├── Part SK1002-01    
  │   └── WH-01
  │       ├── Part SK1004-01
  │       └── Part SK1003-01
  ├── Part SK1005-01        
  ├── Part SK1006-01        
  └── Part SK1007-01  
  ```

  Calling this on child assemblies shows the tree from that reference point:
  ```
  >>> bom.assemblies
  [TR-01]
  >>> print(bom.assemblies[0].tree)
  TR-01
  ├── Part SK1002-01
  └── WH-01
    ├── Part SK1004-01
    └── Part SK1003-01
  ```

### Command Line Usage

Functionality is extended to the command line, where `PATH` is either a
single Excel file (single-file mode) or a folder (multi-file mode) — which
one is inferred automatically from whether `PATH` is a file or a directory.

`action` is what to do with the imported data, which just maps to a property
on the top-level `BOM` object. If omitted, the interactive TUI browser opens
instead (see above).

This method is not persistent and is meant for quick one-off retrieval of information.

```
> bomkit [-h] [PATH] [action]
```

```
> bomkit "Example/Multi-file" tree
SKA-100
├── Part SK1001-01        
├── TR-01
│   ├── Part SK1002-01    
│   └── WH-01
│       ├── Part SK1004-01
│       └── Part SK1003-01
├── Part SK1005-01        
├── Part SK1006-01        
└── Part SK1007-01 
```

## Development

The demo data under `Example/` is generated by
`Example/generate_example_data.py` rather than hand-edited, so the multi-file
and single-file layouts stay consistent. Edit that script and re-run it to
change the example data, rather than editing the `.xlsx` files directly.

Dependencies
------------

- *pandas*
- *anytree*
- *openpyxl*
- *textual*