370 lines
20 KiB
Markdown
370 lines
20 KiB
Markdown
# Bank Integration in jey_erp — Implementation Plan
|
||
|
||
**Created:** 2026-05-08
|
||
**Status:** Approved, not yet started
|
||
**Owner:** Ali (jeysoft.mmc@gmail.com)
|
||
|
||
---
|
||
|
||
## Context
|
||
|
||
We're building a universal "Bank Integration" tool inside `jey_erp` for importing bank statements from Excel files. It complements the existing `kapital_bank` app (which integrates with the BIRBank B2B API). The new tool targets banks that don't expose an API — clients export Excel statements from their internet-banking and we import them via column mapping presets.
|
||
|
||
The motivation for putting it in `jey_erp` (rather than a new app per bank): the user expects to support many banks over time, and a single shared dropdown `Import From...` on the Bank Transaction list is cleaner than each bank's app injecting its own button group.
|
||
|
||
The kapital_bank app stays mostly untouched — only its buttons move into the new shared `Import From...` group, and its BRT-extension JS is removed (the functionality moves to jey_erp as a universal version that works with both Bank Integration mappings and Kapital Bank Settings mappings).
|
||
|
||
---
|
||
|
||
## Architectural Decisions
|
||
|
||
| Aspect | Decision | Reasoning |
|
||
|---|---|---|
|
||
| Settings shape | Multi-record DocType `Bank Integration` (not Single) | Future-proofs for multiple banks. Initial focus is one bank, but structure is ready. |
|
||
| Coexistence with kapital_bank | kapital_bank kept as-is; only buttons move to new group; BRT-patch removed and replaced by universal version in jey_erp | "Don't refactor kb" was an explicit constraint. Each bank can have its own dedicated app/doctypes if needed. |
|
||
| Outbound payments | NOT covered by Bank Integration | Excel banks have no API; outbound stays kb-only. |
|
||
| Excel format | Hardcoded standard fields + flexible column mapping per bank (presets) | User maps their bank's columns to our standard fields. Popular banks get fixture presets (ABB, Pasha, Custom). |
|
||
| Import scope | BT-only (no PE/JE creation at import time) | Mappings are applied later in BRT via "Create & Reconcile". This matches kb's BT-list import flow (`import_bulk_bank_transactions`). |
|
||
| Two-step registry flow | Yes, like kb — registries fill at import time (option A from discussion) | Lets user run "Match similar customers/suppliers" before reconciliation. Avoids preview-leakage of half-mapped data. |
|
||
| Tracking doctype | NOT in iteration 1 | Deduplication via direct query on `Bank Transaction.reference_number + bank_account`. Cleanup hooks rely on standard ERPNext relationships. Add later if cases emerge. |
|
||
| Bank Account ↔ Bank Integration link | Hidden read-only `Dynamic Link` on Bank Account, set by user in BRT | One source of truth on the BA, points to either a Bank Integration record or `Kapital Bank Settings` (Single). Allows resolving mappings for either system uniformly. |
|
||
| BRT mapping selection UX | Explicit select after Bank Account, with default loaded from BA's hidden field, written back on confirm | Trades one extra click for zero ambiguity. Default makes repeat use frictionless. |
|
||
| Fixtures vs default_data | Fixtures (`fixtures/bank_integration_excel_preset.json`) | Presets are global, not per-company; standard Frappe fixture loader fits. |
|
||
|
||
---
|
||
|
||
## Data Flow
|
||
|
||
```
|
||
[Excel file] ─→ Excel Parser (driven by Bank Integration Excel Preset)
|
||
│
|
||
▼
|
||
[Preview dialog]
|
||
│
|
||
▼ user selects txns + clicks Import
|
||
│
|
||
┌────────────┴────────────┐
|
||
▼ ▼
|
||
[Bank Transaction] [Bank Integration Customer / Supplier / Purpose]
|
||
(status=New, awaiting manual mapping)
|
||
│
|
||
▼
|
||
Bank Integration Settings tabs
|
||
│
|
||
user clicks "Match similar" / "Create unmapped"
|
||
│
|
||
▼
|
||
Customer/Supplier/Transaction Mappings populated
|
||
│
|
||
▼
|
||
BRT (Bank Reconciliation Tool):
|
||
user picks Bank Account
|
||
→ default Bank Integration loaded from BA hidden field
|
||
→ user confirms / changes the select
|
||
→ selects BTs → "Create & Reconcile"
|
||
│
|
||
▼
|
||
mapping_resolver returns mappings
|
||
(from Bank Integration record OR Kapital Bank Settings,
|
||
based on Bank Account.bank_integration_type)
|
||
│
|
||
▼
|
||
Payment Entry / Journal Entry created and reconciled with BT
|
||
```
|
||
|
||
---
|
||
|
||
## File Structure
|
||
|
||
```
|
||
jey_erp/
|
||
├── jey_erp/
|
||
│ ├── doctype/ # standard Frappe location
|
||
│ │ ├── bank_integration/
|
||
│ │ │ ├── bank_integration.json
|
||
│ │ │ ├── bank_integration.py
|
||
│ │ │ └── bank_integration.js
|
||
│ │ ├── bank_integration_account_mapping/
|
||
│ │ ├── bank_integration_customer_mapping/
|
||
│ │ ├── bank_integration_supplier_mapping/
|
||
│ │ ├── bank_integration_transaction_mapping/
|
||
│ │ ├── bank_integration_customer/ # registry
|
||
│ │ ├── bank_integration_supplier/ # registry
|
||
│ │ ├── bank_integration_purpose/ # registry
|
||
│ │ ├── bank_integration_excel_preset/
|
||
│ │ └── bank_integration_excel_column_mapping/
|
||
│ │
|
||
│ ├── bank_integration/ # python logic (separate from doctype/)
|
||
│ │ ├── __init__.py
|
||
│ │ ├── excel_parser.py # parse xlsx by preset
|
||
│ │ ├── import_api.py # whitelisted: bulk_import_bt + helpers
|
||
│ │ ├── matching.py # match_similar_customers/suppliers/accounts
|
||
│ │ ├── creation.py # create_unmapped_customers/suppliers/accounts
|
||
│ │ ├── mapping_resolver.py # abstract: resolve mappings by Bank Account
|
||
│ │ ├── create_reconcile.py # universal "Create & Reconcile" backend
|
||
│ │ └── doc_events.py # on_trash/on_cancel hooks for PE/JE/BT
|
||
│ │
|
||
│ ├── public/js/
|
||
│ │ ├── bank_integration_form.js # buttons on Bank Integration form
|
||
│ │ ├── bank_transaction_list.js # NEW: "Import From..." dropdown with "Load from Excel"
|
||
│ │ └── bank_reconciliation_tool.js # ENHANCED: select + Create&Reconcile toolbar
|
||
│ │
|
||
│ ├── fixtures/
|
||
│ │ └── bank_integration_excel_preset.json # ABB / Pasha / Custom presets
|
||
│ │
|
||
│ ├── custom_fields.py # ADD: bank_integration_type/bank_integration on BA
|
||
│ └── hooks.py # ADD: doctype_js, doctype_list_js, doc_events
|
||
```
|
||
|
||
---
|
||
|
||
## Doctype Schemas
|
||
|
||
### `Bank Integration` (main, multi-record)
|
||
|
||
A close mirror of `Kapital Bank Settings` but as a regular DocType (one record per bank), and without `is_active` / `default_login` / card mappings.
|
||
|
||
**Fields:**
|
||
- `bank_name` (Data, reqd) — display name e.g. "Pasha Bank", "ABB"
|
||
- Tab "Settings":
|
||
- `default_company` (Link → Company)
|
||
- `default_bank` (Link → Bank)
|
||
- `similarity_threshold_customers` (Percent, default 90)
|
||
- `similarity_threshold_suppliers` (Percent, default 80)
|
||
- `similarity_threshold_purpose` (Percent, default 70)
|
||
- `consider_azeri_chars` (Check, default 1)
|
||
- `default_supplier_group` / `default_customer_group` / `default_payment_terms` / `default_territory` (Link)
|
||
- Tab "Data" → subtabs Accounts / Customers / Suppliers / Purposes (HTML render with pagination, like kb)
|
||
- Tab "Mappings" → subtabs Account / Customer / Supplier / Transaction Mappings (child tables)
|
||
|
||
### Child tables (mirror of kb-side)
|
||
|
||
**`Bank Integration Account Mapping`:** `iban`, `account_label`, `currency` (Link → Currency), `bank_account` (Link → Bank Account), `gl_account` (Link → Account)
|
||
|
||
**`Bank Integration Customer Mapping`:** `bi_customer_name` (Link → Bank Integration Customer), `tax_id`, `erp_customer` (Link → Customer), `customer_group`, `territory`, `payment_terms`, `mapping_type` (Select: Manual/Automatic)
|
||
|
||
**`Bank Integration Supplier Mapping`:** analogous to customer
|
||
|
||
**`Bank Integration Transaction Mapping`:** `document_type` (Select: Payment Entry/Journal Entry, default PE), `purpose_keyword` (Link → Bank Integration Purpose), `payment_type` (Select: Pay/Receive), `counterparty_type` (Select: Customer/Supplier), `counterparty` (Dynamic Link → counterparty_type), `paid_from` (Link → Account), `paid_to` (Link → Account), `cost_center`, `multi_currency` (Check), `currency` (Link → Currency), `notes`
|
||
|
||
### Registries (top-level doctypes)
|
||
|
||
**`Bank Integration Customer`:** `customer_name` (Data, reqd), `tax_id`, `iban`, `bank_code`, `status` (Select: New/Mapped, default New), `mapped_customer` (Link → Customer), `parent_bank_integration` (Link → Bank Integration, makes registries per-bank)
|
||
|
||
**`Bank Integration Supplier`:** analogous
|
||
|
||
**`Bank Integration Purpose`:** `purpose_keyword` (Data, reqd), `direction` (Select: Pay/Receive/Both), `status` (Select: New/Mapped), `parent_bank_integration` (Link → Bank Integration)
|
||
|
||
### Excel preset
|
||
|
||
**`Bank Integration Excel Preset`:**
|
||
- `preset_name` (Data, reqd, unique)
|
||
- `description` (Small Text)
|
||
- `header_row` (Int, default 1) — 1-indexed row containing column headers
|
||
- `skip_rows_after_header` (Int, default 0)
|
||
- `date_format` (Data, default "%Y-%m-%d") — Python strftime format
|
||
- `amount_mode` (Select: "Single column with sign" / "Separate debit/credit columns" / "Single column + direction column", default "Separate debit/credit columns")
|
||
- `column_mappings` (Table → Bank Integration Excel Column Mapping)
|
||
|
||
**`Bank Integration Excel Column Mapping`:** `excel_column` (Data — header text in Excel), `standard_field` (Select: date/reference_number/counterparty/counterparty_tax_id/counterparty_iban/amount/debit/credit/direction/currency/purpose/description), `notes`
|
||
|
||
---
|
||
|
||
## Custom Fields on Bank Account
|
||
|
||
Added in `jey_erp/custom_fields.py` (existing `create_custom_fields()`):
|
||
|
||
```python
|
||
{
|
||
"doctype": "Bank Account",
|
||
"fieldname": "bank_integration_type",
|
||
"fieldtype": "Select",
|
||
"label": "Bank Integration Type",
|
||
"options": "\nBank Integration\nKapital Bank Settings",
|
||
"hidden": 1, "read_only": 1,
|
||
"insert_after": "bank_account_no"
|
||
},
|
||
{
|
||
"doctype": "Bank Account",
|
||
"fieldname": "bank_integration",
|
||
"fieldtype": "Dynamic Link",
|
||
"label": "Bank Integration",
|
||
"options": "bank_integration_type",
|
||
"hidden": 1, "read_only": 1,
|
||
"insert_after": "bank_integration_type"
|
||
}
|
||
```
|
||
|
||
The `Dynamic Link` resolves to either `Bank Integration` records or the `Kapital Bank Settings` Single (whose `name` is just `"Kapital Bank Settings"`).
|
||
|
||
---
|
||
|
||
## Backend Modules
|
||
|
||
### `bank_integration/excel_parser.py`
|
||
- `parse_excel(file_url, preset_name) → list[dict]`
|
||
- Uses `openpyxl` (already a Frappe dependency)
|
||
- Returns dicts in standard form: `{ref_no, date, counterparty, contr_voen, contr_iban, amount, currency, drcr, purpose, description}`
|
||
- Handles 3 amount modes: single signed column / separate debit+credit / amount+direction column
|
||
|
||
### `bank_integration/import_api.py`
|
||
- `@whitelist parse_excel_for_preview(file_url, preset_name, bank_integration)` — parses and returns preview, also dedupes against existing BT by `reference_number + bank_account`
|
||
- `@whitelist import_bulk_bt(txn_list, bank_integration, bank_account)` → enqueues background job
|
||
- `_process_bulk_bt_import(txn_list, bank_integration, bank_account, user)`:
|
||
- For each txn:
|
||
1. Upsert Bank Integration Customer/Supplier (status=New if new) — only when counterparty present
|
||
2. Upsert Bank Integration Purpose
|
||
3. Insert + submit Bank Transaction (party left empty — filled later via Create&Reconcile)
|
||
- `frappe.publish_realtime` events: `bi_bt_import_progress` / `bi_bt_import_complete`
|
||
|
||
### `bank_integration/matching.py`
|
||
Mirrors kb: `match_similar_customers`, `match_similar_suppliers`, `match_similar_accounts`. Uses `SequenceMatcher` + a normalize helper (Azərbaycan translit). Consider extracting `_normalize` and `_AZERI_MAP` to a shared place if both apps end up using it.
|
||
|
||
### `bank_integration/creation.py`
|
||
Mirrors kb: `create_unmapped_customers`, `create_unmapped_suppliers`, `create_unmapped_accounts` — creates ERPNext entities and updates the corresponding mapping rows.
|
||
|
||
### `bank_integration/mapping_resolver.py` (key abstraction)
|
||
|
||
```python
|
||
def resolve_mappings_for_bank_account(bank_account_name):
|
||
"""Returns dict: {transaction_mappings, customer_mappings,
|
||
supplier_mappings, settings} or None if not configured.
|
||
Reads from Bank Integration record or Kapital Bank Settings,
|
||
depending on bank_integration_type field on Bank Account."""
|
||
ba = frappe.get_doc("Bank Account", bank_account_name)
|
||
if ba.bank_integration_type == "Bank Integration" and ba.bank_integration:
|
||
bi = frappe.get_doc("Bank Integration", ba.bank_integration)
|
||
return {...build from bi child tables...}
|
||
elif ba.bank_integration_type == "Kapital Bank Settings":
|
||
kb = frappe.get_single("Kapital Bank Settings")
|
||
return {...build from kb child tables...}
|
||
return None
|
||
```
|
||
|
||
### `bank_integration/create_reconcile.py`
|
||
Backend for the universal "Create & Reconcile" button. Pulls logic from kb's existing `_import_one_transaction` (the PE/JE creation arm) but generalised through `mapping_resolver`. Operates on already-existing Bank Transactions selected by user.
|
||
|
||
### `bank_integration/doc_events.py`
|
||
- `on_trash_bank_transaction` / `on_cancel_bank_transaction` — initial implementation: log + sanity checks. No tracking doctype yet, so no cascading deletes.
|
||
|
||
---
|
||
|
||
## Frontend Modules
|
||
|
||
### `public/js/bank_transaction_list.js` (new)
|
||
- Hooks into `frappe.listview_settings['Bank Transaction'].onload`
|
||
- Creates `Import From... ▼` button group with item `Load from Excel`
|
||
- Click flow: dialog → choose Excel Preset → choose Bank Account → upload file → call `parse_excel_for_preview` → preview table (mirror of kb's `showTransactionSelection`) → click Import → enqueue + realtime progress + final summary
|
||
|
||
### `public/js/bank_reconciliation_tool.js` (extend existing)
|
||
- Keep current "Bank Transaction column" patch (already in jey_erp)
|
||
- Add `checkboxColumn: true` to DataTable rebuild (port from kb)
|
||
- Add a `Bank Integration` select to the BRT header that fills from all `Bank Integration` records, and additionally adds a `Kapital Bank` option if kb is installed
|
||
- Default value: read from `Bank Account.bank_integration_type / .bank_integration` via `frappe.db.get_value` when user picks a Bank Account
|
||
- On change: call whitelist `update_bank_account_default_bi(bank_account, type, name)` to persist to BA's hidden fields
|
||
- Floating toolbar `Create & Reconcile` button → calls `create_reconcile.create_documents_for_bts(bt_names, bank_account)`
|
||
|
||
### `public/js/bank_integration_form.js` (new)
|
||
A near-copy of `kapital_bank_settings.js` button setup — Match similar / Create matching / Add unmapped buttons under Accounts / Customers / Suppliers / Purposes groups.
|
||
|
||
---
|
||
|
||
## Fixtures
|
||
|
||
`jey_erp/fixtures/bank_integration_excel_preset.json` — initial set:
|
||
- ABB Standard Statement (column mappings for ABB internet-banking export)
|
||
- Pasha Business Statement
|
||
- Custom Template (empty `column_mappings` — user fills in dialog)
|
||
|
||
Register in `hooks.py`:
|
||
```python
|
||
fixtures = [
|
||
{"doctype": "Print Format"},
|
||
{"doctype": "Bank Integration Excel Preset"},
|
||
{"doctype": "Bank Integration Excel Column Mapping"},
|
||
]
|
||
```
|
||
|
||
---
|
||
|
||
## hooks.py Changes (jey_erp)
|
||
|
||
```python
|
||
doctype_list_js = {
|
||
...
|
||
"Bank Transaction": "public/js/bank_transaction_list.js",
|
||
}
|
||
|
||
# bank_reconciliation_tool.js already registered — file is extended in place
|
||
|
||
doc_events = {
|
||
...
|
||
"Bank Transaction": {
|
||
"on_trash": "jey_erp.bank_integration.doc_events.on_trash_bank_transaction",
|
||
"on_cancel": "jey_erp.bank_integration.doc_events.on_cancel_bank_transaction",
|
||
},
|
||
}
|
||
```
|
||
|
||
In `custom_fields.py` add the Bank Account block above. Ensure `after_migrate_combined()` calls the field creator (already does, via existing `create_custom_fields`).
|
||
|
||
---
|
||
|
||
## Changes in kapital_bank (minimal)
|
||
|
||
1. **`client/bank_transaction.js`** — replace:
|
||
```js
|
||
listview.page.add_inner_button(__('Import'), ..., __('Kapital Bank'));
|
||
```
|
||
with:
|
||
```js
|
||
listview.page.add_inner_button(__('Load from Kapital Bank'), ..., __('Import From...'));
|
||
```
|
||
|
||
2. **`client/payment_entry.js`** — same change.
|
||
|
||
3. **`client/bank_reconciliation_tool.js`** — **delete the file**.
|
||
|
||
4. **`hooks.py`** — remove `"Bank Reconciliation Tool": "client/bank_reconciliation_tool.js"` from `doctype_js`.
|
||
|
||
5. **No setup.py change in kb itself.** Migration of existing kb-mapped Bank Accounts (proper `bank_integration_type`/`bank_integration` values for them) happens in jey_erp's `after_migrate`: walk `Kapital Bank Account Mapping.bank_account` rows and set the BA's hidden fields.
|
||
|
||
---
|
||
|
||
## Implementation Phases
|
||
|
||
| # | Phase | Depends on |
|
||
|---|---|---|
|
||
| 1 | Create all 10 Doctypes — JSON schemas + `__init__.py` + empty `.py` controllers | — |
|
||
| 2 | Custom fields on Bank Account via `custom_fields.py` | — |
|
||
| 3 | `excel_parser.py` + smoke test with one sample.xlsx | 1 |
|
||
| 4 | `import_api.py` + JS preview dialog + bulk import → creates BT | 1, 2, 3 |
|
||
| 5 | `bank_transaction_list.js` — "Load from Excel" item under "Import From..." | 4 |
|
||
| 6 | `bank_integration_form.js` + `matching.py` + `creation.py` (Settings buttons) | 1 |
|
||
| 7 | `mapping_resolver.py` — abstraction over the two mapping sources | 1 |
|
||
| 8 | `create_reconcile.py` — backend for universal Create&Reconcile | 7 |
|
||
| 9 | `bank_reconciliation_tool.js` — extension: checkboxColumn + select + toolbar | 8 |
|
||
| 10 | Migration script in jey_erp `after_migrate`: walk Kapital Bank Account Mappings → set `bank_integration_type/name` on their Bank Accounts | 2 |
|
||
| 11 | Minimal kapital_bank edits: rename buttons + delete BRT file + drop hook | 1–9 ready |
|
||
| 12 | Fixtures: `bank_integration_excel_preset.json` (ABB + Pasha + Custom) | 1 |
|
||
| 13 | End-to-end test: ABB Excel → BT → Settings (mappings) → BRT (Create&Reconcile) → PE/JE | all done |
|
||
|
||
---
|
||
|
||
## Out of Scope (Iteration 1)
|
||
|
||
- Tracking doctype (`Bank Integration Transaction`) — dedupe via direct `Bank Transaction.reference_number + bank_account` query. Add later if cleanup-cascade cases emerge.
|
||
- Outbound payments (Send/Check/Cancel) — explicitly excluded; outbound stays kb-specific.
|
||
- Card statements support — Excel banks usually only provide account statements. Can be added as a preset mode if needed.
|
||
- Other BRT enhancements beyond Create & Reconcile.
|
||
- Sharing `_normalize` / `_AZERI_MAP` between apps — duplicate for now, refactor if both apps continue to evolve in this area.
|
||
|
||
---
|
||
|
||
## Notes for Future Self
|
||
|
||
- `kapital_bank` has TWO import paths (visible in `bank_api.py`): `import_bulk_transactions` (creates PE/JE/BT through full mappings, called from `client/payment_entry.js`) and `import_bulk_bank_transactions` (creates only BT, called from `client/bank_transaction.js`). Both are active and not commented out. Our new tool mirrors the BT-only path.
|
||
- BRT in BRT — the data table is rebuilt by `kapital_bank/client/bank_reconciliation_tool.js` to add a checkbox column. The current `jey_erp/public/js/bank_reconciliation_tool.js` adds the "Bank Transaction" column. After this work, jey_erp owns the entire BRT extension, and kb owns nothing on BRT.
|
||
- The Dynamic Link trick on Bank Account avoids needing kb to be aware of jey_erp at all. The mapping resolver in jey_erp reads from kb Settings directly when the type is `Kapital Bank Settings`.
|
||
- Fixture-driven Excel presets means adding support for a new bank = editing one JSON file in jey_erp + running migrate.
|