feat: Party auto-matcher from Bank Transaction data
- Created Bank Party Mapper
- Created class to auto match by account/iban or party name/description(fuzzy)
- Automatch and set in transaction or create mapper
- `rapidfuzz` introduced
diff --git a/erpnext/accounts/doctype/bank_party_mapper/__init__.py b/erpnext/accounts/doctype/bank_party_mapper/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_party_mapper/__init__.py
diff --git a/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.js b/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.js
new file mode 100644
index 0000000..d11d804
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.js
@@ -0,0 +1,8 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+// frappe.ui.form.on("Bank Party Mapper", {
+// refresh(frm) {
+
+// },
+// });
diff --git a/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.json b/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.json
new file mode 100644
index 0000000..4f5f6bf
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.json
@@ -0,0 +1,80 @@
+{
+ "actions": [],
+ "creation": "2023-03-31 10:48:20.249481",
+ "default_view": "List",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "party_type",
+ "party",
+ "column_break_wbna",
+ "bank_party_name_desc",
+ "bank_party_account_number",
+ "bank_party_iban"
+ ],
+ "fields": [
+ {
+ "fieldname": "bank_party_account_number",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Party Account No. (Bank Statement)"
+ },
+ {
+ "fieldname": "bank_party_iban",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Party IBAN (Bank Statement)"
+ },
+ {
+ "fieldname": "column_break_wbna",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "party_type",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "in_standard_filter": 1,
+ "label": "Party Type",
+ "options": "DocType"
+ },
+ {
+ "fieldname": "party",
+ "fieldtype": "Dynamic Link",
+ "in_list_view": 1,
+ "in_standard_filter": 1,
+ "label": "Party",
+ "options": "party_type"
+ },
+ {
+ "fieldname": "bank_party_name_desc",
+ "fieldtype": "Small Text",
+ "label": "Party Name/Desc (Bank Statement)"
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "links": [],
+ "modified": "2023-04-03 10:11:31.384383",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Bank Party Mapper",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "System Manager",
+ "share": 1,
+ "write": 1
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": [],
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.py b/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.py
new file mode 100644
index 0000000..d3a9a5e
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_party_mapper/bank_party_mapper.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+# import frappe
+from frappe.model.document import Document
+
+
+class BankPartyMapper(Document):
+ pass
diff --git a/erpnext/accounts/doctype/bank_party_mapper/test_bank_party_mapper.py b/erpnext/accounts/doctype/bank_party_mapper/test_bank_party_mapper.py
new file mode 100644
index 0000000..c05b23f
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_party_mapper/test_bank_party_mapper.py
@@ -0,0 +1,9 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+# import frappe
+from frappe.tests.utils import FrappeTestCase
+
+
+class TestBankPartyMapper(FrappeTestCase):
+ pass
diff --git a/erpnext/accounts/doctype/bank_transaction/auto_match_party.py b/erpnext/accounts/doctype/bank_transaction/auto_match_party.py
new file mode 100644
index 0000000..7354fa0
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_transaction/auto_match_party.py
@@ -0,0 +1,157 @@
+import frappe
+from rapidfuzz import fuzz, process
+
+
+class AutoMatchParty:
+ def __init__(self, **kwargs) -> None:
+ self.__dict__.update(kwargs)
+
+ def get(self, key):
+ return self.__dict__.get(key, None)
+
+ def match(self):
+ result = AutoMatchbyAccountIBAN(
+ bank_party_account_number=self.bank_party_account_number,
+ bank_party_iban=self.bank_party_iban,
+ deposit=self.deposit,
+ ).match()
+
+ if not result:
+ result = AutoMatchbyPartyDescription(
+ bank_party_name=self.bank_party_name, description=self.description, deposit=self.deposit
+ ).match()
+
+ return result
+
+
+class AutoMatchbyAccountIBAN:
+ def __init__(self, **kwargs) -> None:
+ self.__dict__.update(kwargs)
+
+ def get(self, key):
+ return self.__dict__.get(key, None)
+
+ def match(self):
+ if not (self.bank_party_account_number or self.bank_party_iban):
+ return None
+
+ result = self.match_account_in_bank_party_mapper()
+ if not result:
+ result = self.match_account_in_party()
+
+ return result
+
+ def match_account_in_bank_party_mapper(self):
+ filter_field = (
+ "bank_party_account_number" if self.bank_party_account_number else "bank_party_iban"
+ )
+ result = frappe.db.get_value(
+ "Bank Party Mapper",
+ filters={filter_field: self.get(filter_field)},
+ fieldname=["party_type", "party"],
+ )
+ if result:
+ party_type, party = result
+ return (party_type, party, None)
+
+ return result
+
+ def match_account_in_party(self):
+ # If not check if there is a match in Customer/Supplier/Employee
+ filter_field = "bank_account_no" if self.bank_party_account_number else "iban"
+ transaction_field = (
+ "bank_party_account_number" if self.bank_party_account_number else "bank_party_iban"
+ )
+ result = None
+
+ parties = ["Supplier", "Employee", "Customer"] # most -> least likely to receive
+ if self.deposit > 0:
+ parties = ["Customer", "Supplier", "Employee"] # most -> least likely to pay
+
+ for party in parties:
+ party_name = frappe.db.get_value(
+ party, filters={filter_field: self.get(transaction_field)}, fieldname=["name"]
+ )
+ if party_name:
+ result = (party, party_name, {transaction_field: self.get(transaction_field)})
+ break
+
+ return result
+
+
+class AutoMatchbyPartyDescription:
+ def __init__(self, **kwargs) -> None:
+ self.__dict__.update(kwargs)
+
+ def get(self, key):
+ return self.__dict__.get(key, None)
+
+ def match(self):
+ # Match by Customer, Supplier or Employee Name
+ # search bank party mapper by party and then description
+ # fuzzy search by customer/supplier & employee
+ if not (self.bank_party_name or self.description):
+ return None
+
+ result = self.match_party_name_desc_in_bank_party_mapper()
+
+ if not result:
+ result = self.match_party_name_desc_in_party()
+
+ return result
+
+ def match_party_name_desc_in_bank_party_mapper(self):
+ """Check if match exists for party name or description in Bank Party Mapper"""
+ result = None
+ # TODO: or filters
+ if self.bank_party_name:
+ result = frappe.db.get_value(
+ "Bank Party Mapper",
+ filters={"bank_party_name_desc": self.bank_party_name},
+ fieldname=["party_type", "party"],
+ )
+
+ if not result and self.description:
+ result = frappe.db.get_value(
+ "Bank Party Mapper",
+ filters={"bank_party_name_desc": self.description},
+ fieldname=["party_type", "party"],
+ )
+
+ result = result + (None,) if result else result
+
+ return result
+
+ def match_party_name_desc_in_party(self):
+ """Fuzzy search party name and/or description against parties in the system"""
+ result = None
+
+ parties = ["Supplier", "Employee", "Customer"] # most-least likely to receive
+ if frappe.utils.flt(self.deposit) > 0.0:
+ parties = ["Customer", "Supplier", "Employee"] # most-least likely to pay
+
+ for party in parties:
+ name_field = party.lower() + "_name"
+ filters = {"status": "Active"} if party == "Employee" else {"disabled": 0}
+
+ names = frappe.get_all(party, filters=filters, pluck=name_field)
+
+ for field in ["bank_party_name", "description"]:
+ if not result and self.get(field):
+ result = self.fuzzy_search_and_return_result(party, names, field)
+ if result:
+ break
+
+ return result
+
+ def fuzzy_search_and_return_result(self, party, names, field):
+ result = process.extractOne(query=self.get(field), choices=names, scorer=fuzz.token_set_ratio)
+
+ if result:
+ party_name, score, index = result
+ if score > 75:
+ return (party, party_name, {"bank_party_name_desc": self.get(field)})
+ else:
+ return None
+
+ return result
diff --git a/erpnext/accounts/doctype/bank_transaction/bank_transaction.json b/erpnext/accounts/doctype/bank_transaction/bank_transaction.json
index 1543fdb..4139a9f 100644
--- a/erpnext/accounts/doctype/bank_transaction/bank_transaction.json
+++ b/erpnext/accounts/doctype/bank_transaction/bank_transaction.json
@@ -36,7 +36,7 @@
"party",
"column_break_3czf",
"bank_party_name",
- "bank_party_no",
+ "bank_party_account_number",
"bank_party_iban"
],
"fields": [
@@ -217,19 +217,19 @@
"label": "Party Name (Bank Statement)"
},
{
- "fieldname": "bank_party_no",
- "fieldtype": "Data",
- "label": "Party Account No. (Bank Statement)"
- },
- {
"fieldname": "bank_party_iban",
"fieldtype": "Data",
"label": "Party IBAN (Bank Statement)"
+ },
+ {
+ "fieldname": "bank_party_account_number",
+ "fieldtype": "Data",
+ "label": "Party Account No. (Bank Statement)"
}
],
"is_submittable": 1,
"links": [],
- "modified": "2023-03-30 15:30:46.485683",
+ "modified": "2023-03-31 10:45:30.671309",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Transaction",
diff --git a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
index 676c719..7454504 100644
--- a/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
+++ b/erpnext/accounts/doctype/bank_transaction/bank_transaction.py
@@ -9,11 +9,6 @@
class BankTransaction(StatusUpdater):
# TODO
- # On BT save:
- # - Match by account no/iban in Customer/Supplier/Employee
- # - Match by Party Name
- # - If match found, set party type and party name.
-
# On submit/update after submit
# - Create/Update a Bank Party Map record
# - User can edit after submit.
@@ -22,6 +17,12 @@
def after_insert(self):
self.unallocated_amount = abs(flt(self.withdrawal) - flt(self.deposit))
+ def on_update(self):
+ if self.party_type and self.party:
+ return
+
+ self.auto_set_party()
+
def on_submit(self):
self.clear_linked_payment_entries()
self.set_status()
@@ -157,6 +158,30 @@
payment_entry.payment_document, payment_entry.payment_entry, clearance_date, self
)
+ def auto_set_party(self):
+ # TODO: check if enabled
+ from erpnext.accounts.doctype.bank_transaction.auto_match_party import AutoMatchParty
+
+ result = AutoMatchParty(
+ bank_party_account_number=self.bank_party_account_number,
+ bank_party_iban=self.bank_party_iban,
+ bank_party_name=self.bank_party_name,
+ description=self.description,
+ deposit=self.deposit,
+ ).match()
+
+ if result:
+ self.party_type, self.party, mapper = result
+
+ if not mapper:
+ return
+
+ mapper_doc = frappe.get_doc(
+ {"doctype": "Bank Party Mapper", "party_type": self.party_type, "party": self.party}
+ )
+ mapper_doc.update(mapper)
+ mapper_doc.insert()
+
@frappe.whitelist()
def get_doctypes_for_bank_reconciliation():
diff --git a/pyproject.toml b/pyproject.toml
index 0718e5b..e5bc884 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -12,6 +12,7 @@
"pycountry~=20.7.3",
"Unidecode~=1.2.0",
"barcodenumber~=0.5.0",
+ "rapidfuzz~=2.15.0",
# integration dependencies
"gocardless-pro~=1.22.0",