Metadata-Version: 2.4
Name: gckit
Version: 0.1.0
Summary: Consolidate outstanding Sage 50 invoices into one GoCardless direct debit payment per customer per collection date, net off credit notes automatically, and notify customers via Microsoft Graph.
Author: Duckboard
License: MIT
Project-URL: Homepage, https://github.com/Duckboard/gckit
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: gocardless_pro>=2.0
Requires-Dist: sagekit>=0.1.0
Requires-Dist: msgraphkit>=0.1.0
Requires-Dist: PyYAML>=6.0
Dynamic: license-file

# gckit

Consolidate outstanding **Sage 50 (UK/Ireland)** invoices into **one
GoCardless direct debit payment per customer per collection date**,
netting off credit notes automatically, instead of GoCardless's
default of one payment per invoice. 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 customer notification emails.

This is a real, widely-felt gap in GoCardless's own integrations --
see [this Sage Community Hub thread](https://communityhub.sage.com/gb/sage-50-accounts/f/general-discussion-uk/269853/go-cardless---bulk-payments-for-multiple-invoices)
asking exactly this question. If a customer has three unpaid invoices
due the same week, GoCardless's standard integration creates three
separate direct debit collections; a lot of customers phone up
confused about "three payments" when they expected one. gckit
consolidates them into a single payment with a clear description,
before anything gets sent.

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

## What it does

1. Reads outstanding invoices and credit notes straight out of Sage 50
   via ODBC.
2. Groups them by customer and working-day collection date, netting
   off any credit notes against that customer's invoice total.
3. Works out a valid charge date (skipping weekends and English/Welsh
   bank holidays, with a configurable lead time), and defers customers
   on monthly payment terms to their proper monthly date instead of
   collecting weekly.
4. Checks GoCardless itself for anything already submitted or already
   paid, on top of its own run log, so a re-run (scheduled weekly, or
   re-run after a crash) never double-charges anyone.
5. Submits one consolidated payment per customer, with an idempotency
   key GoCardless honours even if the same request is sent twice.
6. Emails each customer what's being collected and when, via
   Microsoft Graph.

Everything is driven by two CSVs (or Sage/GoCardless data directly)
and one optional settings file -- there's no database to maintain.

## Features

- One payment per customer per collection date, not per invoice
- Credit notes netted off automatically
- Bank-holiday-aware charge dates (fetched from gov.uk), with a
  configurable minimum lead time and a choice of England & Wales,
  Scotland, or Northern Ireland's calendar
- Customers on monthly payment terms collected on their monthly date,
  not swept into the weekly run
- Small remaining balances merged into a customer's next scheduled
  payment instead of triggering a tiny separate collection
- Double-charge protection: checks both a local run log and
  GoCardless's own payment list (submitted, pending, and failed) before
  sending anything
- A manual retry list, for pushing a specific invoice through again
  after fixing a one-off issue (bad mandate, etc.) without it being
  silently excluded as "already tried"
- `--customer` flag to test against a single account before trusting
  it with everyone
- A live-environment confirmation you have to type out, separate from
  the normal "submit?" prompt, so a fat-fingered `sandbox` vs `live`
  mistake doesn't move real money
- Column-name auto-detection for the tricky bits (Sage's `DELETED_FLAG`
  varies between numeric and string across installs) via sagekit
- No Outlook dependency for customer notifications -- sends via the
  Graph API directly, so it works unattended/headless (e.g. on Windows
  Task Scheduler)

## 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) |
| A GoCardless account | Sandbox is fine for testing; you'll need a live account with active customer mandates for real collections |
| An Azure app registration | With the **Mail.Send** Application permission (admin consent required) -- see graphkit's README for the registration walkthrough. Skip this if you don't want customer notification emails; leave `email.sender_mailbox` blank in settings.yaml |

## Setup

### 1. GoCardless API token

Generate an access token from your GoCardless dashboard (Developers ->
Create access token). Start with a **sandbox** token and test a full
run before switching to live.

```
GC_ACCESS_TOKEN=sandbox_your-token-here
GC_ENVIRONMENT=sandbox
```

### 2. Sage ODBC connection

gckit 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.

### 3. Graph API app registration (optional, for customer emails)

gckit 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=...
```

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

### 4. mandate_map.csv

gckit needs to know which GoCardless mandate belongs to which Sage
customer. Copy `examples/sample_mandate_map.csv` and edit it by hand,
five columns:

| Column | What to enter | Example |
|---|---|---|
| **Account Ref** | The account reference from Sage -- must match exactly (case-sensitive) | `ABC001` |
| **Mandate ID** | From GoCardless's dashboard: Customers -> mandate -> Mandate ID | `MD0001AAAA1AAA` |
| **Customer Name** | For your reference / notification emails | `ABC Distributors Ltd` |
| **Email** | Where to send the collection notice | `accounts@example.com` |
| **CC Email** | Optional second recipient | *(blank)* |

If your Sage "Direct Debit Mandate Ref" field already matches the
mandate reference you gave GoCardless when each mandate was set up,
`examples/build_mandate_map.py` can build this file automatically from
a Sage export -- see the comments in that script for the export format
and extra dependencies (`pandas`, `openpyxl`).

### 5. settings.yaml (optional)

Copy `examples/settings.yaml` if you want to change where files are
written, the charge-date lead time, the small-payment merge threshold,
or the notification email addresses. Every key has a default -- the
file itself is optional.

## Usage

```bash
python -m gckit                        # preview + interactive confirm
python -m gckit --unattended           # no prompts (scheduled runs only)
python -m gckit --customer ABC001      # restrict to one customer (first live test)
python -m gckit --config examples/settings.yaml
```

Run against the **sandbox** GoCardless environment first, check the
preview output and the log file in the logs folder, then switch
`GC_ENVIRONMENT=live` once you're confident.

As a library, everything is importable directly -- see
`examples/run_collector.py` for calling the CLI entry point from your
own script, and `examples/build_mandate_map.py` for building
`mandate_map.csv` automatically from a Sage export.

```python
from gckit import consolidate_payments, merge_small_payments, calculate_charge_date
```

The consolidation logic itself (`gckit.consolidate`, `gckit.models`,
`gckit.workdays`) has no dependency on Sage, GoCardless, or Graph, so
it's usable on its own if your invoice data comes from somewhere else.

## Double-charge protection

Before submitting anything, gckit excludes invoices/credits that are
already accounted for, from two sources:

1. Its own `collection_log.csv` -- every payment it has successfully
   **sent** in a previous run.
2. GoCardless itself -- any payment currently `submitted`, `pending`,
   `paid_out`, or `failed` for these mandates, read directly from the
   GoCardless API. This catches cases the local log wouldn't, e.g. a
   payment sent from a different machine, or a crash after sending but
   before the log was written.

A `failed` GoCardless payment is excluded by default (something needs
fixing first) -- add the invoice number to `retry_invoices.txt` (one
per line, `#` for comments) to force it back into the next run once
you've resolved the issue.

## Scheduling

**Windows Task Scheduler:**

1. `Win + R` -> `taskschd.msc`
2. Create Basic Task -> Trigger: Weekly (or whatever cadence suits
   your terms), on the day you want the collection run to happen
3. Action: Start a program -> `python` with arguments
   `-m gckit --unattended`, "Start in" set to the folder containing
   your `mandate_map.csv` / `settings.yaml`
4. Tick "Run with highest privileges" and set `SAGE_UID`/`SAGE_PWD` as
   environment variables (System Properties -> Environment Variables)
   since there's no one to answer the Sage login prompt unattended

Test thoroughly with a normal (non-`--unattended`) run against the
sandbox environment before scheduling anything against live.

## Log files

Every run creates a dated log file in the logs folder recording every
step: invoices/credits loaded, payments consolidated, the full
preview, and the result of each submission. `collection_log.csv`
separately keeps a permanent record of every payment ever sent, which
is what double-charge protection reads from.

## Troubleshooting

| Symptom | Likely cause | Fix |
|---|---|---|
| `Missing required environment variable: GC_ACCESS_TOKEN` | Token not set | Set `GC_ACCESS_TOKEN` (see Setup step 1) |
| `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 |
| `No mandate mappings loaded -- cannot continue` | `mandate_map.csv` missing or empty | Check the path in settings.yaml and that the file has data rows |
| A customer's invoices aren't being collected | No mandate mapped for their Account Ref, or all their invoices already appear as `sent` in `collection_log.csv` | Check `mandate_map.csv` has a row for them; check `collection_log.csv` |
| Payment rejected with a mandate/validation error | Usually a lapsed or cancelled mandate in GoCardless | Check the mandate's status in the GoCardless dashboard; add the invoice number to `retry_invoices.txt` once fixed |
| PDFs -- wait, wrong toolkit | You want statements, not collections | See [stmtkit](https://github.com/Duckboard/stmtkit) |

## Testing

```bash
pip install pytest
pytest
```

Tests mock the Sage cursor, the GoCardless client, and the Graph
client, so they run offline with zero real credentials, no real Sage
install, and no real GoCardless account.

## Notes

- Amounts assume GBP; GoCardless amounts are pence internally
  (`gckit.gc_client` handles the conversion).
- The SQL in `gckit/sage_source.py` assumes 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.
- Bank holiday dates are fetched from the UK government's public
  `bank-holidays.json` feed. Defaults to `england-and-wales`; set
  `collection.bank_holiday_region` in settings.yaml to `scotland` or
  `northern-ireland` instead -- see `examples/settings.yaml`.

## 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.
