Business Central Dev Tools

A Python package for Business Central data analysis, CSM fault code management, FAQ issue type matching, and failed CSM submission resolution. The package provides both a CLI and an interactive menu for importing data from BC OData APIs and FAQ APIs, normalizing it into SQLite, fuzzy-matching BC fault codes against FAQ issue types, exporting results to Excel, and running the Failed CSM Submissions Resolver pipeline.

Features

Data import from multiple sources:
- FAQ issue types via the FAQ REST API
- BC fault code relationships from Excel
- Arbitrary Excel workbooks into SQLite for offline analysis
- Manual CSM quality report compilation spreadsheets
Data normalization of both FAQ and BC datasets into relational dimension tables with SQL views
Fuzzy matching of BC fault codes against FAQ issue types using configurable column mappings and similarity thresholds
Excel export of comparison results (all, matched, unmatched sheets)
Quality report submission to the CSM 1.0 API from Excel spreadsheets, with logging and result summaries
Failed CSM Submissions Resolver — fetches failed service orders from BC OData, matches them against the FAQ hierarchy, writes results to a spreadsheet, and optionally submits to CSM and marks records as sent in BC
Resolver analytics — pattern analysis on resolver output and human correction patterns
Dual execution modes — non-interactive CLI subcommands and an interactive numbered menu

Requirements

Python 3.9+
Virtual environment (recommended)
Access to the shared tools/ directory at the repository root (contains bc_odata_client.py, faq_api_client.py, csm_1_api_client.py)

Setup

1. Create and activate a virtual environment

python3 -m venv .venv
source .venv/bin/activate

2. Install dependencies

pip install -r requirements.txt

3. Configure environment variables

Copy the example file and fill in your credentials:

cp .env.example .env

Required variables

Variable	Description
`FAQ_API_BASE_URL`	FAQ REST API base URL
`FAQ_API_USERNAME`	FAQ API username
`FAQ_API_PASSWORD`	FAQ API password
`CSM_API_BASE_URL`	CSM 1.0 API base URL
`CSM_API_SECRET`	CSM API authentication secret
`CSM_API_SERVICE_CENTER`	CSM service center identifier
`BC_TENANT_ID`	Azure AD tenant GUID
`BC_CLIENT_ID`	Azure AD app registration client ID
`BC_CLIENT_SECRET`	Azure AD app registration client secret
`BC_ENVIRONMENT`	BC environment name (e.g., `sandbox`, `production`)
`BC_COMPANY_ID`	BC company GUID
`FAILED_CSM_SPREADSHEET`	Path to the Failed CSM Submissions Resolver spreadsheet

Optional variables

Variable	Default	Description
`BC_TOOLS_DIR`	`../tools/` (repo root)	Path to the shared `tools/` directory
`CSM_DB_NAME`	`csm_data.db`	SQLite database filename
`HTTP_DEBUG`	`0`	Set to `1` to enable verbose HTTP request/response logging

Note: The .env file is local-only and must not be committed. The .gitignore already excludes it.

CLI Usage

Run the package as a module:

python -m bc_dev_tools

Running without a subcommand prints the help message. There is no default command.

Top-level commands

# Full pipeline: import BC + FAQ, normalize, compare, export
python -m bc_dev_tools pipeline [--threshold FLOAT]

# Fuzzy-match BC fault codes against FAQ issue types
python -m bc_dev_tools compare [--threshold FLOAT]

# Export comparison results to Excel
python -m bc_dev_tools export [--output PATH]

# Submit quality reports from an Excel file to the CSM 1.0 API
python -m bc_dev_tools submit --file PATH

# Launch the interactive menu
python -m bc_dev_tools interactive

Import commands

# Import FAQ issue types from the FAQ API and normalize
python -m bc_dev_tools import faq

# Import BC fault codes from Excel and normalize
python -m bc_dev_tools import bc [--file PATH]

# Import other Excel files into SQLite
python -m bc_dev_tools import excel [--file PATH] [--table-name NAME]

# Import a manual compilation report
python -m bc_dev_tools import manual-report

Resolve commands

# Run the Failed CSM Submissions Resolver pipeline
python -m bc_dev_tools resolve run [--dry-run] [--threshold FLOAT] [--full] \
    [--since DATE] [--tab-name NAME] [--output PATH] \
    [--submit] [--mark-sent] [--backfill-sent TIMESTAMP] [--verbose]

# Analyze resolver output patterns
python -m bc_dev_tools resolve analyze

# Analyze human correction patterns
python -m bc_dev_tools resolve corrections

Interactive Mode

python -m bc_dev_tools interactive

The interactive menu presents the following options:

Complete Import and Comparison (Full Pipeline) — runs import, normalize, compare, and export end to end
Import from FAQ API and Normalize Data — pulls FAQ issue types and normalizes into relational tables
Import BC Fault Codes from Excel and Normalize — imports the BC relationship spreadsheet and normalizes
Import Other Excel Files — imports configured or ad-hoc Excel files into SQLite
Compare Data (Match BC codes with FAQ) — runs fuzzy matching with a configurable threshold
Export Results to Excel — writes comparison results to a multi-sheet workbook
Submit Failed Quality Reports — reads an Excel file and submits each row to the CSM 1.0 API
Run Manual Compilation Report Compilation — imports a manual CSM quality report spreadsheet
Exit

Data Sources and Outputs

Inputs

BC relationship file: bcFaultCodeRelationships.xlsx (default, configurable in config.py)
FAQ issue types: fetched live from the FAQ API
Manual reports: Manual CSM Quality Report Compilation - 2024.xlsx
Quality report submissions: user-specified Excel file at runtime
Failed CSM resolver: data fetched from BC OData API

Local database

SQLite database: csm_data.db (default, configurable via CSM_DB_NAME)

Outputs

Comparison results: comparison_results.xlsx (sheets: All Results, Matched, Unmatched)
Submission logs: api_logs_<timestamp>.txt
Submission results: submission_results.xlsx
Resolver spreadsheet: path from FAILED_CSM_SPREADSHEET environment variable

How FAQ/BC Matching Works

The comparison logic uses a configured mapping of BC columns to FAQ columns (COLUMN_MAPPING in config.py) and difflib.SequenceMatcher similarity ratios. For each BC row, the matcher scores it against every FAQ row across the mapped columns and records the best match:

BC Column	FAQ Column
`Fault Area Description`	`csMainCategoryName`
`Symptom Code Description`	`csSubCategoryName`
`Fault Code Description`	`itemPartName`
`Description`	`issueTypeName`

Match results are written back to the BC table as: faq_match_found, faq_matched_id, faq_match_details, match_score.

Project Layout

bc_dev_tools/
  __init__.py                    # Package marker
  __main__.py                    # Entry point: .env loading, sys.path setup, delegates to cli.main()
  cli.py                         # argparse CLI with subcommand groups
  main.py                        # Interactive menu and CLI wrapper functions
  config.py                      # Centralized configuration from environment variables
  database_manager.py            # SQLite + pandas import/query utilities
  exporter.py                    # Excel export of comparison results
  faqbc_data_matcher.py          # Fuzzy matching: BC fault codes vs FAQ issue types
  bc_data_normalizer.py          # BC data normalization into dimension tables
  faq_data_normalizer.py         # FAQ data normalization into dimension tables
  failed_csm_resolver.py         # BC OData -> FAQ matching -> spreadsheet pipeline
  analyze_resolver.py            # Resolver output pattern analysis
  analyze_human_corrections.py   # Human correction pattern analysis
  test_faq_auth.py               # Debug script for FAQ API authentication
  .env.example                   # Template for all environment variables
  .gitignore
  requirements.txt               # Python dependencies
  docs/
    api.md                       # API client reference
    architecture.md              # Package architecture and data flow
    cli.md                       # CLI reference for all subcommands
    contributing.md              # Contribution guidelines
    development.md               # Development environment setup
    usage.md                     # Usage guide for CLI and interactive modes

Shared API clients (in the repository's tools/ directory):

bc_odata_client.py — BC OData API client (OAuth2, CRUD operations)
faq_api_client.py — FAQ API client (authentication, issue type retrieval)
csm_1_api_client.py — CSM 1.0 API client (authentication, quality report submission)

Additional Resources

Troubleshooting

"Shared tools directory not found" — The package needs access to the tools/ directory at the repository root. If your directory structure differs, set BC_TOOLS_DIR to the path containing bc_odata_client.py, faq_api_client.py, and csm_1_api_client.py.
Missing columns during comparison — The matcher validates expected column names. Confirm the Excel headers match the configured BC columns (COLUMN_MAPPING in config.py) and that the API response contains the expected FAQ fields.
Excel read errors — Ensure the file is not open in another program and that headers are in the first row.
API auth failures — Verify environment variables are set correctly and that the target hosts are reachable.
No subcommand provided — Running python -m bc_dev_tools without a subcommand prints help and exits. There is no default command.

Features​

Requirements​

Setup​

1. Create and activate a virtual environment​

2. Install dependencies​

3. Configure environment variables​

Required variables​

Optional variables​

CLI Usage​

Top-level commands​

Import commands​

Resolve commands​

Interactive Mode​

Data Sources and Outputs​

Inputs​

Local database​

Outputs​

How FAQ/BC Matching Works​

Project Layout​

Additional Resources​

Troubleshooting​