Bank POS transactions in bank reconciliation (#13225)
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
index 0294219..3ab73b7 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
@@ -165,6 +165,36 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "fieldname": "include_pos_transactions",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Include POS Transactions",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"fieldname": "get_payment_entries",
"fieldtype": "Button",
"hidden": 0,
@@ -292,7 +322,7 @@
"istable": 0,
"max_attachments": 0,
"menu_index": 0,
- "modified": "2017-04-21 16:58:26.902732",
+ "modified": "2018-03-07 18:58:48.658687",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Reconciliation",
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
index 0aaed8f..b633662 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
@@ -53,10 +53,26 @@
posting_date ASC, name DESC
""".format(condition),
{"account":self.bank_account, "from":self.from_date, "to":self.to_date}, as_dict=1)
-
- entries = sorted(list(payment_entries)+list(journal_entries),
+
+ pos_entries = []
+ if self.include_pos_transactions:
+ pos_entries = frappe.db.sql("""
+ select
+ "Sales Invoice Payment" as payment_document, sip.name as payment_entry, sip.amount as debit,
+ si.posting_date, si.debit_to as against_account, sip.clearance_date,
+ account.account_currency, 0 as credit
+ from `tabSales Invoice Payment` sip, `tabSales Invoice` si, `tabAccount` account
+ where
+ sip.account=%(account)s and si.docstatus=1 and sip.parent = si.name
+ and account.name = sip.account and si.posting_date >= %(from)s and si.posting_date <= %(to)s {0}
+ order by
+ si.posting_date ASC, si.name DESC
+ """.format(condition),
+ {"account":self.bank_account, "from":self.from_date, "to":self.to_date}, as_dict=1)
+
+ entries = sorted(list(payment_entries)+list(journal_entries+list(pos_entries)),
key=lambda k: k['posting_date'] or getdate(nowdate()))
-
+
self.set('payment_entries', [])
self.total_amount = 0.0
diff --git a/erpnext/accounts/doctype/bank_reconciliation/test_bank_reconciliation.py b/erpnext/accounts/doctype/bank_reconciliation/test_bank_reconciliation.py
new file mode 100644
index 0000000..932fb33
--- /dev/null
+++ b/erpnext/accounts/doctype/bank_reconciliation/test_bank_reconciliation.py
@@ -0,0 +1,8 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2018, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+from __future__ import unicode_literals
+import unittest
+
+class TestBankReconciliation(unittest.TestCase):
+ pass
diff --git a/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json b/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json
index 531622d..b9e5c8d 100644
--- a/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json
+++ b/erpnext/accounts/doctype/sales_invoice_payment/sales_invoice_payment.json
@@ -227,6 +227,36 @@
"search_index": 0,
"set_only_once": 0,
"unique": 0
+ },
+ {
+ "allow_bulk_edit": 0,
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "clearance_date",
+ "fieldtype": "Date",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_global_search": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Clearance Date",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 1,
+ "print_hide_if_no_value": 0,
+ "read_only": 1,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
}
],
"has_web_view": 0,
@@ -239,7 +269,7 @@
"issingle": 0,
"istable": 1,
"max_attachments": 0,
- "modified": "2017-07-24 17:25:03.765856",
+ "modified": "2018-03-07 18:34:39.552769",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Sales Invoice Payment",
diff --git a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.js b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.js
index 0f9fdd7..57fe4b0 100644
--- a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.js
+++ b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.js
@@ -28,5 +28,10 @@
"default": frappe.datetime.get_today(),
"reqd": 1
},
+ {
+ "fieldname":"include_pos_transactions",
+ "label": __("Include POS Transactions"),
+ "fieldtype": "Check"
+ },
]
-}
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
index 95b7ff7..eca5975 100644
--- a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
+++ b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
@@ -138,7 +138,23 @@
and ifnull(clearance_date, '4000-01-01') > %(report_date)s
""", filters, as_dict=1)
- return sorted(list(payment_entries)+list(journal_entries),
+ pos_entries = []
+ if filters.include_pos_transactions:
+ pos_entries = frappe.db.sql("""
+ select
+ "Sales Invoice Payment" as payment_document, sip.name as payment_entry, sip.amount as debit,
+ si.posting_date, si.debit_to as against_account, sip.clearance_date,
+ account.account_currency, 0 as credit
+ from `tabSales Invoice Payment` sip, `tabSales Invoice` si, `tabAccount` account
+ where
+ sip.account=%(account)s and si.docstatus=1 and sip.parent = si.name
+ and account.name = sip.account and si.posting_date <= %(report_date)s and
+ ifnull(sip.clearance_date, '4000-01-01') > %(report_date)s
+ order by
+ si.posting_date ASC, si.name DESC
+ """, filters, as_dict=1)
+
+ return sorted(list(payment_entries)+list(journal_entries+list(pos_entries)),
key=lambda k: k['posting_date'] or getdate(nowdate()))
def get_amounts_not_reflected_in_system(filters):