Metadata-Version: 2.4
Name: sagekit
Version: 0.1.0
Summary: A small, dependency-light toolkit for reading data out of Sage 50 (UK/Ireland) via ODBC, with column-name auto-detection across Sage versions.
Author: Duckboard
License: MIT
Project-URL: Homepage, https://github.com/Duckboard/sagekit
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
Classifier: Topic :: Database
Requires-Python: >=3.8
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyodbc>=5.0
Dynamic: license-file

# sagekit

A small, dependency-light Python toolkit for reading data directly out
of **Sage 50 (UK/Ireland)** via ODBC -- no need to export a report to
CSV first. Built for scripts that need to pull invoices, customer
records, or ledger data straight from a live Sage install: reminder
emails, dashboards, scheduled reports, reconciliations, and so on.

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

## Features

- Connects to Sage 50 via its own ODBC driver -- nothing extra to install on the Sage side
- No secrets in code or on disk -- DSN/username/password are read from environment variables, with a safe interactive prompt (hidden password input) for anything not set
- Column-name auto-detection: Sage 50's schema varies slightly between versions, so sagekit inspects the live table rather than assuming one fixed set of column names
- `DELETED_FLAG` auto-detection: some installs store it as numeric (`0`/`1`), others as string (`'N'`/`'Y'`) -- sagekit works out which
- Two worked examples: outstanding invoices, customer list

## Installation

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

or, to install as an editable package:

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

You'll also need the **Sage Line 50 ODBC driver**, which Sage 50
installs alongside itself -- there's nothing separate to download.
Confirm it's there by searching **"ODBC Data Sources (64-bit)"** in
the Windows Start menu; you should see a Sage-related driver listed
under the "Drivers" tab, and your DSN (see below) under "System DSN"
or "User DSN".

## Finding your DSN

The **DSN (Data Source Name)** identifies which Sage company database
to connect to. To find it:

1. Search **"ODBC Data Sources (64-bit)"** in Windows and open it.
2. Look under the **System DSN** or **User DSN** tab for an entry
   pointing at your Sage 50 data (often named something like
   `SageLine50v33`, with the number matching your Sage version).
3. That name is your `SAGE_DSN`.

If you're not sure, sagekit will prompt you for it at runtime -- you
don't have to set it in advance.

## Configuration

Copy `.env.example` to `.env` if you want to pre-fill values (useful
for scheduled/unattended scripts):

```bash
cp .env.example .env
```

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

`.env` is already in `.gitignore` -- never commit real credentials.
Leaving `SAGE_PWD` blank (or unset) is recommended for anything you
run by hand -- sagekit will prompt for it with hidden input instead,
so your password never sits in a file or your shell history.

To load `.env` automatically in your own scripts, install
`python-dotenv` and add this before importing sagekit:

```python
from dotenv import load_dotenv
load_dotenv()
```

Sagekit itself doesn't require python-dotenv -- it only reads
`os.environ`, plus interactive prompts for anything missing.

## Usage

```python
from sagekit import connect

conn = connect()   # prompts for DSN/username/password if not set
cursor = conn.cursor()
cursor.execute("SELECT * FROM AUDIT_HEADER WHERE TYPE = 'SI'")
rows = cursor.fetchall()
conn.close()
```

Sage 50 must be **running** on the machine (or reachable on the
network) while you query it via ODBC -- the driver talks to the live
application, not a standalone database file.

### Column auto-detection

Column names vary a little between Sage 50 versions. Rather than
hardcoding one version's names, use `find_column()` to ask the live
table what it's actually called:

```python
from sagekit import connect, find_column

conn = connect()
cursor = conn.cursor()

outstanding_col = find_column(cursor, "AUDIT_HEADER", "outstanding_amount")
cursor.execute(f"SELECT {outstanding_col} FROM AUDIT_HEADER WHERE TYPE = 'SI'")
```

Known concepts and the column-name variants sagekit checks for each
(see `sagekit/discovery.py` to extend this list):

| Concept | Variants checked |
|---|---|
| `transaction_date` | `DATE`, `INV_DATE`, `TRANS_DATE`, `DATE_ENTERED` |
| `due_date` | `DUE_DATE`, `DATE_DUE` |
| `outstanding_amount` | `OUTSTANDING`, `OS_AMOUNT`, `OUTSTANDING_AMOUNT` |
| `gross_amount` | `GROSS_AMOUNT`, `AMOUNT`, `FOREIGN_GROSS_AMOUNT` |
| `deleted_flag` | `DELETED_FLAG`, `DELETED`, `RECORD_DELETED` |
| `customer_name` | `NAME`, `COMPANY_NAME`, `ACCOUNT_NAME` |
| `email` | `E_MAIL`, `EMAIL_1`, `EMAIL`, `EMAIL1`, `DIRECT_DEBITS_EMAIL` |
| `payment_due_days` | `PAYMENT_DUE_DAYS`, `PAY_DUE_DAYS` |
| `payment_due_from` | `PAYMENT_DUE_FROM_CODE`, `PAY_DUE_FROM_CODE` |

Or use `get_columns(cursor, table)` to just see everything a table has:

```python
from sagekit import get_columns
print(get_columns(cursor, "AUDIT_HEADER"))
```

### DELETED_FLAG auto-detection

```python
from sagekit import detect_deleted_flag

not_deleted = detect_deleted_flag(cursor, "AUDIT_HEADER", sample_type="SI")
cursor.execute(f"SELECT * FROM AUDIT_HEADER WHERE TYPE = 'SI' AND DELETED_FLAG = {not_deleted}")
```

## Key tables

| Table | Contents |
|---|---|
| `AUDIT_HEADER` | One row per transaction (invoices, credits, receipts). Has `TYPE`, `INV_REF`, date/due-date, outstanding/gross amount, deleted flag |
| `SALES_LEDGER` | One row per customer account. Has name, email, payment terms, address fields |
| `AUDIT_SPLIT` | Line-level detail for each transaction (product codes, quantities, nominal codes) |
| `STOCK` | Product / stock item records |
| `NOMINAL_LEDGER` | Nominal account records |

For most accounts-receivable tasks, `AUDIT_HEADER` joined to
`SALES_LEDGER` on `ACCOUNT_REF` is all you need -- see
`examples/outstanding_invoices.py`.

### Transaction type codes

Common codes on the sales side (verify against your own install --
see below):

| Code | Meaning |
|---|---|
| `SI` | Sales Invoice |
| `SC` | Sales Credit Note |
| `SA` | Sales Payment on Account (unallocated receipt) |

These letters are generally consistent across Sage 50 installs, but
custom transaction types can exist. To check what your install
actually has:

```python
from sagekit import verify_transaction_types
print(verify_transaction_types(cursor))
```

### Examples

- `examples/outstanding_invoices.py` -- list outstanding sales
  invoices, joined to customer names
- `examples/list_customers.py` -- dump customer accounts with email
  addresses

Run either directly once you've set up your `.env` (or just answer
the prompts):

```bash
python examples/outstanding_invoices.py
python examples/list_customers.py
```

## Testing

```bash
pip install pytest
pytest
```

Tests mock `pyodbc`, so they run offline without a real Sage install
or ODBC driver.

## Troubleshooting

| Symptom | Likely cause | Fix |
|---|---|---|
| `Data source name not found` | DSN name wrong or not set up | Open "ODBC Data Sources (64-bit)", check the DSN exists and matches `SAGE_DSN` |
| `Login failed` | Wrong username/password | Try logging into Sage 50 directly first to confirm credentials |
| `0 rows returned` | Wrong `DELETED_FLAG` value or `TYPE` code for this install | Use `detect_deleted_flag()` / `verify_transaction_types()` |
| `Column not found` | Column name varies by version | Use `get_columns()` or `find_column()` to inspect actual column names |
| `pyodbc not installed` | Missing package | `pip install pyodbc` |
| Connection hangs or times out | Sage 50 not running | Sage 50 must be open on the same machine (or accessible on the network) while querying via ODBC |

## Notes

- Use the 64-bit ODBC driver with 64-bit Python -- mixing 32-bit and
  64-bit causes connection failures.
- Credentials are never written to disk by sagekit itself -- they
  live only in memory for the duration of a script (unless you choose
  to put them in your own `.env` file for unattended use, at your own
  risk).

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