Metadata-Version: 2.4
Name: stmtkit
Version: 0.1.0
Summary: Generate branded PDF customer statements from Sage 50 (via sagekit) and email them through Microsoft Graph (via graphkit). Outstanding-balance and full-activity layouts, CSV-driven recipient list, configurable branding.
Author: Duckboard
License: MIT
Project-URL: Homepage, https://github.com/Duckboard/stmtkit
Project-URL: Buy me a coffee, https://ko-fi.com/duckboard
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: Microsoft :: Windows
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Office/Business :: Financial :: Accounting
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sagekit>=0.1.0
Requires-Dist: msgraphkit>=0.1.0
Requires-Dist: reportlab>=4.0
Requires-Dist: PyYAML>=6.0
Dynamic: license-file

# stmtkit

Generate branded PDF customer statements straight from **Sage 50
(UK/Ireland)** and email them out via Microsoft Graph -- no Outlook
desktop client, no manual PDF exports, no copy-pasting into an email
client. Built on top of two other Duckboard toolkits: [sagekit](https://github.com/Duckboard/sagekit)
for the Sage connection and [graphkit](https://github.com/Duckboard/graphkit)
for sending mail.

If this saves you time, consider [buying me a coffee](https://ko-fi.com/duckboard).

## What it does

1. Reads a CSV of customers -- which layout they get, how they pay,
   where to email them.
2. Pulls live data out of Sage 50 via ODBC: invoices, payments,
   company details, bank details.
3. Builds a branded PDF for each customer.
4. Emails the PDF as an attachment via Microsoft Graph.

Everything is driven by the CSV. Add, remove, or change a customer's
preferences by editing that file -- the change takes effect on the
next run.

## Features

- Two statement layouts: **outstanding** (unpaid invoices + aged
  analysis) and **all_activity** (full transaction history from a
  brought-forward balance)
- Payment message (direct debit vs BACS bank details) is set
  independently of layout, per customer
- A built-in grace period for the statement date, so a scheduled run
  on the 1st still works correctly even if it's missed and run a few
  days late (see "Statement date logic" below)
- Configurable branding (accent colour, logo) -- no fixed look
- No Outlook dependency -- sends via the Graph API directly, so it
  works unattended/headless (e.g. on Windows Task Scheduler)
- Column-name auto-detection for the tricky bits (Sage's `DELETED_FLAG`
  varies between numeric and string across installs) via sagekit

## Installation

```bash
pip install -r requirements.txt
```

or, as an editable package:

```bash
pip install -e .
```

You'll also need:

| Requirement | Notes |
|---|---|
| Windows | sagekit's ODBC driver is Windows-only |
| Sage 50 (UK/Ireland) | Running on this PC or reachable on the network, with its ODBC driver installed (comes with Sage) |
| An Azure app registration | With the **Mail.Send** Application permission (admin consent required) -- see graphkit's README for the registration walkthrough |

## Setup

### 1. Sage ODBC connection

stmtkit uses [sagekit](https://github.com/Duckboard/sagekit) for the
Sage connection, so set the same three environment variables sagekit
reads (see its README for how to find your DSN):

```
SAGE_DSN=SageLine50v33
SAGE_UID=MANAGER
SAGE_PWD=
```

Leaving `SAGE_PWD` blank is fine for anything you run by hand --
you'll be prompted with hidden input instead. Only set it for
unattended/scheduled runs.

### 2. Graph API app registration

stmtkit uses [graphkit](https://github.com/Duckboard/graphkit) to
send mail, so set the same three environment variables graphkit reads:

```
GRAPH_TENANT_ID=...
GRAPH_CLIENT_ID=...
GRAPH_CLIENT_SECRET=...
```

The app registration needs the **Mail.Send** Application permission
(not Mail.ReadWrite) with admin consent granted. See graphkit's README
for the full Azure walkthrough.

Copy `.env.example` to `.env` and fill in your values if you want to
pre-fill these (never commit the real `.env` -- it's already
gitignored).

### 3. recipients.csv

Copy `examples/recipients.csv` and edit it. Five columns:

| Column | What to enter | Example |
|---|---|---|
| **CustomerCode** | The account reference from Sage -- must match exactly (case-sensitive) | `SAMP001` |
| **CustomerName** | For your reference only, not used in queries | `C & S Traders Ltd` |
| **Email** | Address(es) to send to, comma-separated for more than one | `accounts@example.com` |
| **LayoutTemplate** | `outstanding` or `all_activity` | `all_activity` |
| **PaymentType** | `DD` or `BACS` -- controls the payment footer | `DD` |
| **EmailNote** | Optional, appended to the email body verbatim | *(blank)* |

`PaymentType` and `LayoutTemplate` are independent: a DD customer can
use either layout and always gets the DD message; a BACS customer
gets your bank details in the footer regardless of layout.

Prefix a `CustomerCode` with `#` to disable that row without deleting it.

### 4. settings.yaml (optional)

Copy `examples/settings.yaml` if you want to change where files are
written, override the "from" email address, or set custom branding.
Every key has a default -- the file itself is optional.

## Usage

```bash
python -m stmtkit --test              # dry run: PDFs only, no emails
python -m stmtkit                     # generate PDFs and send emails
python -m stmtkit --date 2026-04-30   # override the statement date
python -m stmtkit --customer SAMP001  # process one customer only
python -m stmtkit --config examples/settings.yaml
```

Run `--test` first, check the PDFs in the output folder and the log
file in the logs folder, then run for real.

As a library, everything is importable directly -- see
`examples/build_single_pdf.py` for building a PDF from data you
already have (useful if your ledger isn't Sage), and
`examples/run_statements.py` for calling the CLI entry point from
your own script.

```python
from stmtkit import statement_date, load_recipients, Branding, build_pdf, send_statement_email
```

## Statement date logic

Designed for a job scheduled on the **1st of the month**, with a
**seven-day grace period**: if the 1st falls on a weekend, bank
holiday, or the PC was off, running any time up to the 7th still
produces a statement dated for the last day of the *previous* month.

| Run date | Statement date |
|---|---|
| 1st (scheduled) | Last day of previous month |
| 3rd (weekend grace) | Last day of previous month |
| 7th (last grace day) | Last day of previous month |
| 8th or later | Last day of *current* month (treated as a manual run) |

Override with `--date YYYY-MM-DD`, or change the grace window by
calling `stmtkit.statement_date(run_date, grace_days=N)` directly.

## Scheduling

**Windows Task Scheduler:**

1. `Win + R` -> `taskschd.msc`
2. Create Basic Task -> Trigger: Monthly, all months, day 1
3. Action: Start a program -> `python` with arguments `-m stmtkit`,
   "Start in" set to the folder containing your `recipients.csv` /
   `settings.yaml`
4. Tick "Run with highest privileges" and "Run task as soon as
   possible after a scheduled start is missed" (Settings tab) --
   the grace-period logic above covers the rest.

## Log files

Every run creates a dated log file in the logs folder, recording the
run date, statement date, each customer processed (sent / skipped /
failed), the PDF file name, and any errors.

## Troubleshooting

| Symptom | Likely cause | Fix |
|---|---|---|
| `Cannot connect to Sage via DSN ...` | DSN wrong or Sage not running | See sagekit's README -- check "ODBC Data Sources (64-bit)" and that Sage is open |
| `Customer 'X' not found in Sage` | CustomerCode doesn't match the Account Reference | Copy the Account Reference exactly from Sage -- it's case-sensitive |
| `No outstanding invoices for X -- skipping` | Normal -- the customer has no unpaid balance | Nothing to fix |
| PDFs created but no emails arrive | Graph app registration missing Mail.Send, or wrong `from_address` | Check the app registration's API permissions and admin consent; confirm `from_address` is a real mailbox |
| `Missing required environment variable: GRAPH_...` | `.env` not loaded or variables not set | Set the three `GRAPH_*` variables (see Setup step 2) |
| `Missing required environment variable: SAGE_...` / prompted every run | `SAGE_DSN` not set | Set `SAGE_DSN`, or just answer the prompt each time |

## Testing

```bash
pip install pytest
pytest
```

Tests mock the Sage cursor and the Graph client, so they run offline
with zero real credentials or a real Sage install.

## Notes

- Amounts assume GBP and use a `£` prefix -- if you need another
  currency symbol, it's one line to change in `stmtkit/pdf.py`
  (`_money()`).
- The two statement layouts and the SQL in `stmtkit/sage_data.py`
  assume Sage 50's standard `AUDIT_HEADER` / `SALES_LEDGER` schema
  (UK/Ireland). If your install uses different column names, use
  sagekit's `get_columns()` / `find_column()` to check what's
  actually there.

## License

MIT -- see [LICENSE](LICENSE). Do whatever you like with this; a
credit or a [Ko-fi tip](https://ko-fi.com/duckboard) is appreciated
but never required.
