Bank statement import corrections (#15405)
* Bank statement import corrections
* Bank statement corrections
* Missing semi-columns
diff --git a/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.js b/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.js
index d852935..736ed35 100644
--- a/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.js
+++ b/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.js
@@ -44,21 +44,21 @@
invoice_filter: function(frm) {
frm.set_query("invoice", "payment_invoice_items", function(doc, cdt, cdn) {
- row = locals[cdt][cdn]
+ let row = locals[cdt][cdn]
if (row.party_type == "Customer") {
return {
- filters:[[row.invoice_type, "customer", "in", [row.party]],
- [row.invoice_type, "status", "!=", "Cancelled" ],
- [row.invoice_type, "posting_date", "<", row.transaction_date ],
- [row.invoice_type, "outstanding_amount", ">", 0 ]]
- }
+ filters:[[row.invoice_type, "customer", "in", [row.party]],
+ [row.invoice_type, "status", "!=", "Cancelled" ],
+ [row.invoice_type, "posting_date", "<", row.transaction_date ],
+ [row.invoice_type, "outstanding_amount", ">", 0 ]]
+ }
} else if (row.party_type == "Supplier") {
return {
- filters:[[row.invoice_type, "supplier", "in", [row.party]],
- [row.invoice_type, "status", "!=", "Cancelled" ],
- [row.invoice_type, "posting_date", "<", row.transaction_date ],
- [row.invoice_type, "outstanding_amount", ">", 0 ]]
- }
+ filters:[[row.invoice_type, "supplier", "in", [row.party]],
+ [row.invoice_type, "status", "!=", "Cancelled" ],
+ [row.invoice_type, "posting_date", "<", row.transaction_date ],
+ [row.invoice_type, "outstanding_amount", ">", 0 ]]
+ }
}
});
},
@@ -73,3 +73,28 @@
frm.events.invoke_doc_function(frm, "submit_payment_entries");
},
});
+
+
+frappe.ui.form.on('Bank Statement Transaction Invoice Item', {
+ party_type: function(frm, cdt, cdn) {
+ let row = locals[cdt][cdn];
+ if (row.party_type == "Customer") {
+ row.invoice_type = "Sales Invoice";
+ } else if (row.party_type == "Supplier") {
+ row.invoice_type = "Purchase Invoice";
+ } else if (row.party_type == "Account") {
+ row.invoice_type = "Journal Entry";
+ }
+ refresh_field("invoice_type", row.name, "payment_invoice_items");
+
+ },
+ invoice_type: function(frm, cdt, cdn) {
+ let row = locals[cdt][cdn];
+ if (row.invoice_type == "Purchase Invoice") {
+ row.party_type = "Supplier";
+ } else if (row.invoice_type == "Sales Invoice") {
+ row.party_type = "Customer";
+ }
+ refresh_field("party_type", row.name, "payment_invoice_items");
+ }
+});
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.json b/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.json
index a7c56a1..fb80169 100644
--- a/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.json
+++ b/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.json
@@ -14,6 +14,7 @@
"fields": [
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -46,6 +47,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -77,6 +79,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -108,6 +111,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -140,6 +144,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -170,6 +175,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -202,6 +208,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -234,6 +241,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -266,6 +274,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -297,10 +306,12 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "depends_on": "",
"fieldname": "section_break_6",
"fieldtype": "Section Break",
"hidden": 0,
@@ -328,6 +339,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -360,10 +372,12 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "depends_on": "eval:doc.new_transaction_items && doc.new_transaction_items.length",
"fieldname": "section_break_9",
"fieldtype": "Section Break",
"hidden": 0,
@@ -390,10 +404,12 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "depends_on": "",
"fieldname": "match_invoices",
"fieldtype": "Button",
"hidden": 0,
@@ -421,6 +437,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -451,6 +468,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -482,6 +500,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -512,6 +531,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -543,10 +563,12 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "depends_on": "eval:doc.new_transaction_items && doc.new_transaction_items.length",
"fieldname": "section_break_18",
"fieldtype": "Section Break",
"hidden": 0,
@@ -574,6 +596,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -606,6 +629,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -637,6 +661,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -669,6 +694,7 @@
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -709,7 +735,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2018-04-07 17:01:47.480572",
+ "modified": "2018-09-14 18:04:44.170455",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Statement Transaction Entry",
@@ -718,7 +744,6 @@
"permissions": [
{
"amend": 1,
- "apply_user_permissions": 0,
"cancel": 1,
"create": 1,
"delete": 1,
@@ -738,7 +763,6 @@
},
{
"amend": 1,
- "apply_user_permissions": 0,
"cancel": 1,
"create": 1,
"delete": 1,
diff --git a/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.py b/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.py
index ff2de0c..7a7d7d2 100644
--- a/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.py
+++ b/erpnext/accounts/doctype/bank_statement_transaction_entry/bank_statement_transaction_entry.py
@@ -49,7 +49,7 @@
if not self.bank_settings:
frappe.throw("Bank Data mapper doesn't exist")
mapper_doc = frappe.get_doc("Bank Statement Settings", self.bank_settings)
- headers = [entry.stmt_header for entry in mapper_doc.header_items]
+ headers = {entry.mapped_header:entry.stmt_header for entry in mapper_doc.header_items}
return headers
def populate_payment_entries(self):
@@ -63,9 +63,10 @@
date_format = '%Y-%m-%d'
if self.bank_settings:
mapped_items = frappe.get_doc("Bank Statement Settings", self.bank_settings).mapped_items
- transactions = get_transaction_entries(filename, self.get_statement_headers())
+ statement_headers = self.get_statement_headers()
+ transactions = get_transaction_entries(filename, statement_headers)
for entry in transactions:
- date = entry["Date"].strip()
+ date = entry[statement_headers["Date"]].strip()
#print("Processing entry DESC:{0}-W:{1}-D:{2}-DT:{3}".format(entry["Particulars"], entry["Withdrawals"], entry["Deposits"], entry["Date"]))
if (not date): continue
transaction_date = datetime.strptime(date, date_format).date()
@@ -73,19 +74,19 @@
if (self.to_date and transaction_date > datetime.strptime(self.to_date, '%Y-%m-%d').date()): continue
bank_entry = self.append('new_transaction_items', {})
bank_entry.transaction_date = transaction_date
- bank_entry.description = entry["Particulars"]
+ bank_entry.description = entry[statement_headers["Particulars"]]
- mapped_item = next((entry for entry in mapped_items if entry.mapping_type == "Transaction" and entry.bank_data.lower() in bank_entry.description.lower()), None)
+ mapped_item = next((entry for entry in mapped_items if entry.mapping_type == "Transaction" and frappe.safe_decode(entry.bank_data.lower()) in frappe.safe_decode(bank_entry.description.lower())), None)
if (mapped_item is not None):
bank_entry.party_type = mapped_item.mapped_data_type
bank_entry.party = mapped_item.mapped_data
else:
- bank_entry.party_type = "Supplier" if not entry["Deposits"].strip() else "Customer"
+ bank_entry.party_type = "Supplier" if not entry[statement_headers["Deposits"]].strip() else "Customer"
party_list = frappe.get_all(bank_entry.party_type, fields=["name"])
parties = [party.name for party in party_list]
- matches = difflib.get_close_matches(bank_entry.description.lower(), parties, 1, 0.4)
+ matches = difflib.get_close_matches(frappe.safe_decode(bank_entry.description.lower()), parties, 1, 0.4)
if len(matches) > 0: bank_entry.party = matches[0]
- bank_entry.amount = -float(entry["Withdrawals"]) if not entry["Deposits"].strip() else float(entry["Deposits"])
+ bank_entry.amount = -float(entry[statement_headers["Withdrawals"]]) if not entry[statement_headers["Deposits"]].strip() else float(entry[statement_headers["Deposits"]])
self.map_unknown_transactions()
self.map_transactions_on_journal_entry()
@@ -93,7 +94,7 @@
for entry in self.new_transaction_items:
vouchers = frappe.db.sql("""select name, posting_date from `tabJournal Entry`
where posting_date='{0}' and total_credit={1} and cheque_no='{2}' and docstatus != 2
- """.format(entry.transaction_date, abs(entry.amount), entry.description), as_dict=True)
+ """.format(entry.transaction_date, abs(entry.amount), frappe.safe_decode(entry.description)), as_dict=True)
if (len(vouchers) == 1):
entry.reference_name = vouchers[0].name
@@ -115,7 +116,7 @@
if (added is not None): continue
ent = self.append('payment_invoice_items', {})
ent.transaction_date = entry.transaction_date
- ent.payment_description = entry.description
+ ent.payment_description = frappe.safe_decode(entry.description)
ent.party_type = entry.party_type
ent.party = entry.party
ent.invoice = e.get('voucher_no')
@@ -138,7 +139,7 @@
amount = abs(entry.amount)
payment, matching_invoices = None, []
for inv_entry in self.payment_invoice_items:
- if (inv_entry.payment_description != entry.description or inv_entry.transaction_date != entry.transaction_date): continue
+ if (inv_entry.payment_description != frappe.safe_decode(entry.description) or inv_entry.transaction_date != entry.transaction_date): continue
if (inv_entry.party != entry.party): continue
matching_invoices += [inv_entry.invoice_type + "|" + inv_entry.invoice]
payment = get_payments_matching_invoice(inv_entry.invoice, entry.amount, entry.transaction_date)
@@ -165,7 +166,7 @@
entry.mode_of_payment = "Wire Transfer"
entry.outstanding_amount = min(amount, 0)
if (entry.payment_reference is None):
- entry.payment_reference = entry.description
+ entry.payment_reference = frappe.safe_decode(entry.description)
entry.invoices = ",".join(matching_invoices)
#print("Matching payment is {0}:{1}".format(entry.reference_type, entry.reference_name))
@@ -192,7 +193,7 @@
def populate_matching_vouchers(self):
for entry in self.new_transaction_items:
if (not entry.party or entry.reference_name): continue
- print("Finding matching voucher for {0}".format(entry.description))
+ print("Finding matching voucher for {0}".format(frappe.safe_decode(entry.description)))
amount = abs(entry.amount)
invoices = []
vouchers = get_matching_journal_entries(self.from_date, self.to_date, entry.party, self.bank_account, amount)
@@ -207,7 +208,7 @@
ent.invoice_date = voucher.posting_date
ent.invoice_type = "Journal Entry"
ent.invoice = voucher.voucher_no
- ent.payment_description = entry.description
+ ent.payment_description = frappe.safe_decode(entry.description)
ent.allocated_amount = max(voucher.debit, voucher.credit)
invoices += [ent.invoice_type + "|" + ent.invoice]
@@ -223,7 +224,7 @@
for payment_entry in self.new_transaction_items:
if (not payment_entry.party): continue
if (payment_entry.reference_name): continue
- print("Creating payment entry for {0}".format(payment_entry.description))
+ print("Creating payment entry for {0}".format(frappe.safe_decode(payment_entry.description)))
if (payment_entry.party_type == "Account"):
payment = self.create_journal_entry(payment_entry)
invoices = [payment.doctype + "|" + payment.name]
@@ -338,7 +339,10 @@
idx = 0
while idx < len(self.new_transaction_items):
entry = self.new_transaction_items[idx]
- print("Checking transaction {0}: {2} in {1} entries".format(idx, len(self.new_transaction_items), entry.description))
+ try:
+ print("Checking transaction {0}: {2} in {1} entries".format(idx, len(self.new_transaction_items), frappe.safe_decode(entry.description)))
+ except UnicodeEncodeError:
+ pass
idx += 1
if entry.reference_name is None: continue
doc = frappe.get_doc(entry.reference_type, entry.reference_name)
@@ -411,14 +415,15 @@
else:
frappe.throw("Only .csv and .xlsx files are supported currently")
+ stmt_headers = headers.values()
for row in rows:
if len(row) == 0 or row[0] == None or not row[0]: continue
#print("Processing row {0}".format(row))
if header_index:
- transaction = get_transaction_info(headers, header_index, row)
+ transaction = get_transaction_info(stmt_headers, header_index, row)
transactions.append(transaction)
- elif is_headers_present(headers, row):
- header_index = get_header_index(headers, row)
+ elif is_headers_present(stmt_headers, row):
+ header_index = get_header_index(stmt_headers, row)
return transactions
def get_rows_from_xls_file(filename):
diff --git a/erpnext/accounts/doctype/bank_statement_transaction_invoice_item/bank_statement_transaction_invoice_item.json b/erpnext/accounts/doctype/bank_statement_transaction_invoice_item/bank_statement_transaction_invoice_item.json
index bcffff0..d96c94d 100644
--- a/erpnext/accounts/doctype/bank_statement_transaction_invoice_item/bank_statement_transaction_invoice_item.json
+++ b/erpnext/accounts/doctype/bank_statement_transaction_invoice_item/bank_statement_transaction_invoice_item.json
@@ -14,6 +14,7 @@
"fields": [
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -40,10 +41,12 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -70,10 +73,12 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -95,16 +100,18 @@
"precision": "",
"print_hide": 0,
"print_hide_if_no_value": 0,
- "read_only": 1,
+ "read_only": 0,
"remember_last_selected_value": 0,
"report_hide": 0,
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -132,10 +139,12 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -161,10 +170,12 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -191,10 +202,12 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -211,7 +224,7 @@
"label": "Invoice Type",
"length": 0,
"no_copy": 0,
- "options": "Purchase Invoice\nSales Invoice\nJournal Entry",
+ "options": "Sales Invoice\nPurchase Invoice\nJournal Entry",
"permlevel": 0,
"precision": "",
"print_hide": 0,
@@ -222,10 +235,12 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -253,10 +268,12 @@
"reqd": 1,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -283,10 +300,12 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
},
{
"allow_bulk_edit": 0,
+ "allow_in_quick_entry": 0,
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
@@ -313,6 +332,7 @@
"reqd": 0,
"search_index": 0,
"set_only_once": 0,
+ "translatable": 0,
"unique": 0
}
],
@@ -326,7 +346,7 @@
"issingle": 0,
"istable": 1,
"max_attachments": 0,
- "modified": "2017-11-15 09:41:45.840947",
+ "modified": "2018-09-14 19:03:30.949831",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Statement Transaction Invoice Item",
@@ -340,5 +360,6 @@
"sort_field": "modified",
"sort_order": "DESC",
"track_changes": 1,
- "track_seen": 0
+ "track_seen": 0,
+ "track_views": 0
}
\ No newline at end of file