CAM Metal Report and Production Log Sync

The CAM Metal Report Automation is a fully hands-off pipeline that captures sheet metal fabrication data the moment it leaves the CAM cutting machine, tracks each batch through production, and surfaces everything in a **Power BI report** — with no manual data entry required.

There are two automated scripts that work together:

1.**CAM Report Processor** — picks up cut-list files exported by the CAM machine, validates them, and loads them into the database.

2.**Production Log Sync** — reads the Smartsheet production tracking logs and keeps the database in sync with current batch status and fabrication progress.

Once data is in the database, a **Power BI report** connects live to present fabrication metrics, batch status, and project-level production summaries to management and the shop team.

When an operator runs a cutting job on the CAM machine, the system automatically exports a `.CSV` cut-list file to a shared network folder (`P:\CAM_Reports`). No action is required from the operator beyond running the job normally.

Every 10 minutes, the **CAM Report Processor** script wakes up automatically (via Windows Task Scheduler), scans the drop folder, and processes any new files it finds.

-**If the file is valid:** The batch is loaded into the database and the file is moved to a monthly archive folder.

-**If the file is bad:** The file is moved to an `errors` folder and the problem is logged — nothing is silently lost.

On its own schedule, the **Production Log Sync** script connects to Smartsheet and reads the production tracking sheets maintained by the shop team. It updates the database with:

- Current fabrication completion percentage

- Batch status (e.g., In Progress, Complete, Shipped)

- Area, level, and facility assignments

- Shear completion timestamps

New batches are added automatically. Batches that reach **Shipped** status are flagged as inactive and stop being synced, keeping the process efficient.

The **Power BI report** connects directly to the SQL database and reflects all of the above in near real-time. Management and shop leads can view:

- Batch-level fabrication progress by project

- Piece counts, material weights, and cut areas

- Production status across MSM, MIF, and Duct Direct projects

- Historical trends and completion tracking

| Source | What It Provides | How It Gets There |

|---|---|---|

| CAM Machine | Cut-list CSV (Job Name, Piece #, Fitting Name, Shape, Material, Area, Weight) | Automatically drops to `P:\CAM_Reports` |

| Smartsheet — Project Production Logs | Batch status, fabrication %, area, level, facility, shear status | Shop team maintains in Smartsheet; script pulls via API |

| Smartsheet — MIF Production Log | Same fields for all MIF projects (Houston facility) | Same |

| Smartsheet — Duct Direct Production Log | Same fields for all Duct Direct projects (Houston facility) | Same |

| Output | Description |

|---|---|

| `dbo.CAM_Batch` | One record per processed cut-list file — job name, project number, batch number, file name, timestamp, and piece count |

| `dbo.CAM_Pieces` | Every individual piece from the cut list — fitting name, shape, material, area, and weight |

| `dbo.CAM_ProductionBatches` | Live production log data — batch status, fabrication %, shear completion time, active/inactive flag |

| `dbo.CAM_RunStats` | Automated run history — what ran, when, how many records were touched, any errors |

| Monthly archive folders | Processed CSV files organized by month under `P:\CAM_Reports` |

| `errors` folder | Quarantined files that failed validation, timestamped for review |

| **Power BI Report** | Live dashboard showing fabrication progress, batch status, and production metrics by project |

**Shop operators:** Nothing changes. Run jobs on the CAM machine as normal.

**Shop leads / Smartsheet users:** Keep the Smartsheet production logs updated as batches progress. The sync script does the rest.

**Management:** View the Power BI report. Data is always current.

**IT / Admin:** Scripts are fully automated via Windows Task Scheduler. No daily maintenance required. Check the `errors` folder or log files if something looks off.

```

CAM Machine

    └── exports .CSV → P:\CAM_Reports\

                            └── metal_report_processor.py  (Task Scheduler, every 10 min)

                                    └── validates → upserts dbo.CAM_Batch + dbo.CAM_Pieces

                                    └── archives → P:\CAM_Reports\metal_report_archive_YYYY-MM\

                                    └── quarantines bad files → P:\CAM_Reports\errors\

Smartsheet (Production Logs)

    └── production_log_sync.py  (Task Scheduler, scheduled)

            └── reads dbo.CAM_Batch + dbo.CAM_ProductionBatches for known projects

            └── resolves sheet per project: dedicated sheet → MIF → Duct Direct

            └── upserts dbo.CAM_ProductionBatches (change-guarded UPDATE)

dbo.CAM_Batch + dbo.CAM_Pieces + dbo.CAM_ProductionBatches

    └── Power BI Report (live DirectQuery / scheduled refresh)

```

**Purpose:** Poll a drop folder for CAM-exported CSV files, validate them, upsert into SQL, and manage file lifecycle.

**Schedule:** Windows Task Scheduler — every 10 minutes.

**Source directory:**`P:\CAM_Reports` (top-level `.CSV` files only)

Expected headers (exact order):

```

Job Name, Piece Number, Fitting Name, Shape, Material, Area, Weight

```

-`Piece Number` must be a valid integer

-`Area` and `Weight` must be valid floating-point numbers

- File must contain at least one data row

- Encoding: attempts `utf-8-sig` first, then `latin-1`

Any validation failure quarantines the file with a timestamp prefix to `P:\CAM_Reports\errors\`.

-`Job Name` field format: `<ProjectNumber>-<BatchNumber>` — split on first `-`

-**Overwrite behavior:** if `JobName` already exists in `dbo.CAM_Batch`, the old batch and all its pieces are deleted before re-inserting. This handles CAM re-runs cleanly.

- Batch header → `dbo.CAM_Batch`; individual pieces → `dbo.CAM_Pieces` (bulk `executemany`)

- Wrapped in a transaction; rolls back and quarantines the file on any SQL failure

Every execution writes a row to `dbo.CAM_RunStats`:

- Log files: `P:\CAM_Reports\logs\metal_report_YYYYMMDD.log`

- Rotation: files older than 60 days are deleted on each run

- Console: INFO level; File: DEBUG level

**Purpose:** Sync Smartsheet production log data into `dbo.CAM_ProductionBatches`, tracking fabrication progress per batch.

**Schedule:** Windows Task Scheduler (configured separately from the CSV processor).

1.**Dedicated sheet** — `dbo.SS_sheets_path` WHERE `Name LIKE '<ProjectNumber> - Production Log%'`

2.**MIF Production Log** — Sheet ID `6300903738238852` (covers all MIF projects; `Facility` defaulted to `Houston`)

3.**Duct Direct** — Sheet ID `4928893615400836` (covers all DD projects; `Facility` defaulted to `Houston`)

Projects are sourced by combining `DISTINCT ProjectNumber` from both `dbo.CAM_Batch` and `dbo.CAM_ProductionBatches`.

- REST API: `https://api.smartsheet.com/2.0/sheets/{sheet_id}`

- Auth: Bearer token from environment variable `SMARTSHEET_ACCESS_TOKEN`

- Full sheet fetch on every run (SS `last_update` timestamps are not reliably localized — cannot be trusted for skip logic)

`Batch ID`, `Batch Status`, `Fabrication % Complete`, `Area`, `Level`

Optional: `Facility`, `Shear Status`, `Project Number`

Regex: `(\d[\w]*)-(\d+)` — prefix-agnostic, extracts `<project>-<batch>` from anywhere in the cell value.

If a `Project Number` column is present in the sheet, it takes precedence over the regex-parsed project number.

Smartsheet Percent columns store `0.0–1.0`; plain numeric columns store `0–100`. Values ≤ 1.0 are multiplied by 100. Values > 1 are used as-is.

-**First-time** (no rows for `SheetID` in DB): full sheet fetched, all rows inserted including `Shipped` batches. `IsActive` set correctly from the start.

-**Incremental** (subsequent runs): full sheet always fetched. Known inactive rows (`IsActive = 0`) are skipped. Known active rows are updated. Rows not yet in the DB are inserted.

The `UPDATE` statement uses `CASE WHEN` comparisons on every data column. `LastUpdatedDT` is only bumped when at least one value actually changes. `LastCheckedDT` is always stamped.

When `Batch Status = 'Shipped'`, `IsActive` is set to `0`. Inactive rows are never touched again on subsequent runs.

When `Shear Status = 'Done'` and `[Sheer Completion Time]` is currently `NULL`, the timestamp is set to the current run time. It is never overwritten once set.

Transient SQL errors (deadlock `1205`, connection throttle `40001`, etc.) are retried up to 3 times with exponential back-off (2s, 4s).

Every execution writes a row to `dbo.CAM_RunStats`:

- Log files: `P:\CAM_Reports\logs\prod_log_sync_YYYYMMDD.log`

- Rotation: files older than 60 days are deleted on each run

| Table | Script | Purpose |

|---|---|---|

| `dbo.CAM_Batch` | Processor | Batch header — one row per cut-list file |

| `dbo.CAM_Pieces` | Processor | Individual piece detail rows |

| `dbo.CAM_ProductionBatches` | Sync | Live production log state per batch |

| `dbo.CAM_RunStats` | Both | Execution audit log |

| `dbo.SS_sheets_path` | Sync (read) | Smartsheet sheet registry — used to resolve dedicated production log sheets per project |

| Variable | Used By | Description |

|---|---|---|

| `mcd_usr` | Both | SQL Server login username |

| `sqlro_McData` | Both | SQL Server login password |

| `SMARTSHEET_ACCESS_TOKEN` | Sync only | Smartsheet API bearer token |

**SQL Server:**`MSM-DYNSQLTEST\mcdata` / Database: `MCDATA1`

```

P:\CAM_Reports\

├── *.CSV                          ← CAM drop zone (processed and cleared)

├── logs\

│   ├── metal_report_YYYYMMDD.log

│   └── prod_log_sync_YYYYMMDD.log

├── errors\

│   └── YYYYMMDD_HHMMSS_<filename>.csv   ← quarantined bad files

└── metal_report_archive_YYYY-MM\

    └── <processed files>

```

```

Python 3.13+

pyodbc          — SQL Server connectivity (ODBC Driver 17)

requests        — Smartsheet REST API calls

```

All standard library modules (`csv`, `logging`, `os`, `shutil`, `re`, `json`, `pathlib`, `datetime`) — no additional install required.