Payment Entry included in Bank Reconciliation and related reports
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js
index 3de8241..4b33fd3 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.js
@@ -3,11 +3,22 @@
frappe.ui.form.on("Bank Reconciliation", {
setup: function(frm) {
- frm.get_docfield("journal_entries").allow_bulk_edit = 1;
+ frm.get_docfield("payment_entries").allow_bulk_edit = 1;
frm.add_fetch("bank_account", "account_currency", "account_currency");
+
+ frm.get_field('payment_entries').grid.editable_fields = [
+ {fieldname: 'against_account', columns: 3},
+ {fieldname: 'amount', columns: 2},
+ {fieldname: 'cheque_number', columns: 3},
+ {fieldname: 'clearance_date', columns: 3}
+ ];
},
onload: function(frm) {
+ var default_bank_account = locals[":Company"][frappe.defaults.get_user_default("Company")]["default_bank_account"];
+
+ frm.set_value("bank_account", default_bank_account);
+
frm.set_query("bank_account", function() {
return {
"filters": {
@@ -27,16 +38,21 @@
update_clearance_date: function(frm) {
return frappe.call({
- method: "update_details",
- doc: frm.doc
- });
- },
- get_relevant_entries: function(frm) {
- return frappe.call({
- method: "get_details",
+ method: "update_clearance_date",
doc: frm.doc,
callback: function(r, rt) {
- frm.refresh()
+ frm.refresh_field("payment_entries");
+ frm.refresh_fields();
+ }
+ });
+ },
+ get_payment_entries: function(frm) {
+ return frappe.call({
+ method: "get_payment_entries",
+ doc: frm.doc,
+ callback: function(r, rt) {
+ frm.refresh_field("payment_entries");
+ frm.refresh_fields();
}
});
}
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
index 59a3cfc..1a7b763 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.json
@@ -2,6 +2,7 @@
"allow_copy": 1,
"allow_import": 0,
"allow_rename": 0,
+ "beta": 0,
"creation": "2013-01-10 16:34:05",
"custom": 0,
"docstatus": 0,
@@ -16,6 +17,7 @@
"fieldtype": "Link",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Bank Account",
@@ -40,6 +42,7 @@
"fieldtype": "Link",
"hidden": 1,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "Account Currency",
@@ -64,6 +67,7 @@
"fieldtype": "Date",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "From Date",
@@ -87,6 +91,7 @@
"fieldtype": "Date",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "To Date",
@@ -110,6 +115,7 @@
"fieldtype": "Check",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Include Reconciled Entries",
@@ -129,13 +135,14 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "get_relevant_entries",
+ "fieldname": "get_payment_entries",
"fieldtype": "Button",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
- "label": "Get Relevant Entries",
+ "label": "Get Payment Entries",
"length": 0,
"no_copy": 0,
"options": "",
@@ -153,13 +160,14 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "journal_entries",
+ "fieldname": "payment_entries",
"fieldtype": "Table",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
- "label": "Journal Entries",
+ "label": "Payment Entries",
"length": 0,
"no_copy": 0,
"options": "Bank Reconciliation Detail",
@@ -181,6 +189,7 @@
"fieldtype": "Button",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "Update Clearance Date",
@@ -205,6 +214,7 @@
"fieldtype": "Currency",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "Total Amount",
@@ -226,6 +236,7 @@
"hide_toolbar": 1,
"icon": "icon-check",
"idx": 1,
+ "image_view": 0,
"in_create": 0,
"in_dialog": 0,
"is_submittable": 0,
@@ -233,7 +244,7 @@
"istable": 0,
"max_attachments": 0,
"menu_index": 0,
- "modified": "2015-11-30 12:44:45.105451",
+ "modified": "2016-06-28 13:11:09.396353",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Reconciliation",
@@ -260,7 +271,10 @@
"write": 1
}
],
+ "quick_entry": 1,
"read_only": 1,
"read_only_onload": 0,
+ "sort_order": "ASC",
+ "track_seen": 0,
"version": 0
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
index fb95731..b07c82d 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
@@ -12,7 +12,7 @@
}
class BankReconciliation(Document):
- def get_details(self):
+ def get_payment_entries(self):
if not (self.bank_account and self.from_date and self.to_date):
msgprint("Bank Account, From Date and To Date are Mandatory")
return
@@ -22,48 +22,68 @@
condition = "and (clearance_date is null or clearance_date='0000-00-00')"
- dl = frappe.db.sql("""select t1.name, t1.cheque_no, t1.cheque_date, t2.debit_in_account_currency,
- t2.credit_in_account_currency, t1.posting_date, t2.against_account, t1.clearance_date
+ journal_entries = frappe.db.sql("""
+ select
+ "Journal Entry" as payment_document, t1.name as payment_entry,
+ t1.cheque_no as cheque_number, t1.cheque_date,
+ abs(t2.debit_in_account_currency - t2.credit_in_account_currency) as amount,
+ t1.posting_date, t2.against_account, t1.clearance_date
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
- t2.parent = t1.name and t2.account = %s
- and t1.posting_date >= %s and t1.posting_date <= %s and t1.docstatus=1
- and ifnull(t1.is_opening, 'No') = 'No' %s
- order by t1.posting_date DESC, t1.name DESC""" %
- ('%s', '%s', '%s', condition), (self.bank_account, self.from_date, self.to_date), as_dict=1)
-
- self.set('journal_entries', [])
+ t2.parent = t1.name and t2.account = %s and t1.docstatus=1
+ and t1.posting_date >= %s and t1.posting_date <= %s
+ and ifnull(t1.is_opening, 'No') = 'No' {0}
+ order by t1.posting_date DESC, t1.name DESC
+ """.format(condition), (self.bank_account, self.from_date, self.to_date), as_dict=1)
+
+ payment_entries = frappe.db.sql("""
+ select
+ "Payment Entry" as payment_document, name as payment_entry,
+ reference_no as cheque_number, reference_date as cheque_date,
+ if(paid_from=%s, paid_amount, received_amount) as amount,
+ posting_date, party as against_account, clearance_date
+ from `tabPayment Entry`
+ where
+ (paid_from=%s or paid_to=%s) and docstatus=1
+ and posting_date >= %s and posting_date <= %s {0}
+ order by
+ posting_date DESC, name DESC
+ """.format(condition),
+ (self.bank_account, self.bank_account, self.bank_account, self.from_date, self.to_date), as_dict=1)
+
+ entries = sorted(list(payment_entries)+list(journal_entries),
+ key=lambda k: k['posting_date'] or getdate(nowdate()))
+
+ self.set('payment_entries', [])
self.total_amount = 0.0
- for d in dl:
- nl = self.append('journal_entries', {})
- nl.posting_date = d.posting_date
- nl.voucher_id = d.name
- nl.cheque_number = d.cheque_no
- nl.cheque_date = d.cheque_date
- nl.debit = d.debit_in_account_currency
- nl.credit = d.credit_in_account_currency
- nl.against_account = d.against_account
- nl.clearance_date = d.clearance_date
- self.total_amount += flt(d.debit_in_account_currency) - flt(d.credit_in_account_currency)
+ for d in entries:
+ row = self.append('payment_entries', {})
+ row.update(d)
+ self.total_amount += flt(d.amount)
- def update_details(self):
- vouchers = []
- for d in self.get('journal_entries'):
+ def update_clearance_date(self):
+ clearance_date_updated = False
+ for d in self.get('payment_entries'):
if d.clearance_date:
if d.cheque_date and getdate(d.clearance_date) < getdate(d.cheque_date):
- frappe.throw(_("Clearance date cannot be before check date in row {0}").format(d.idx))
+ frappe.throw(_("Row #{0}: Clearance date {1} cannot be before Cheque Date {2}")
+ .format(d.idx, d.clearance_date, d.cheque_date))
if d.clearance_date or self.include_reconciled_entries:
if not d.clearance_date:
d.clearance_date = None
- frappe.db.set_value("Journal Entry", d.voucher_id, "clearance_date", d.clearance_date)
- frappe.db.sql("""update `tabJournal Entry` set clearance_date = %s, modified = %s
- where name=%s""", (d.clearance_date, nowdate(), d.voucher_id))
- vouchers.append(d.voucher_id)
- if vouchers:
- msgprint("Clearance Date updated in: {0}".format(", ".join(vouchers)))
+ frappe.db.set_value(d.payment_document, d.payment_entry, "clearance_date", d.clearance_date)
+ frappe.db.sql("""update `tab{0}` set clearance_date = %s, modified = %s
+ where name=%s""".format(d.payment_document),
+ (d.clearance_date, nowdate(), d.payment_entry))
+
+ clearance_date_updated = True
+
+ if clearance_date_updated:
+ self.get_payment_entries()
+ msgprint(_("Clearance Date updated"))
else:
msgprint(_("Clearance Date not mentioned"))
diff --git a/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json b/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json
index fc3c3b6..b56ad8b 100644
--- a/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json
+++ b/erpnext/accounts/doctype/bank_reconciliation_detail/bank_reconciliation_detail.json
@@ -2,6 +2,7 @@
"allow_copy": 0,
"allow_import": 0,
"allow_rename": 0,
+ "beta": 0,
"creation": "2013-02-22 01:27:37",
"custom": 0,
"docstatus": 0,
@@ -11,18 +12,45 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "voucher_id",
+ "fieldname": "payment_document",
"fieldtype": "Link",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "label": "Payment Document",
+ "length": 0,
+ "no_copy": 0,
+ "options": "DocType",
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "fieldname": "payment_entry",
+ "fieldtype": "Dynamic Link",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
- "label": "Voucher ID",
+ "label": "Payment Entry",
"length": 0,
"no_copy": 0,
"oldfieldname": "voucher_id",
"oldfieldtype": "Link",
- "options": "Journal Entry",
+ "options": "payment_document",
"permlevel": 0,
"print_hide": 0,
"print_hide_if_no_value": 0,
@@ -42,6 +70,7 @@
"fieldtype": "Data",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Against Account",
@@ -64,13 +93,14 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "debit",
+ "fieldname": "amount",
"fieldtype": "Currency",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
- "in_list_view": 0,
- "label": "Debit",
+ "in_list_view": 1,
+ "label": "Amount",
"length": 0,
"no_copy": 0,
"oldfieldname": "debit",
@@ -90,36 +120,11 @@
"allow_on_submit": 0,
"bold": 0,
"collapsible": 0,
- "fieldname": "credit",
- "fieldtype": "Currency",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "in_filter": 0,
- "in_list_view": 0,
- "label": "Credit",
- "length": 0,
- "no_copy": 0,
- "oldfieldname": "credit",
- "oldfieldtype": "Currency",
- "options": "account_currency",
- "permlevel": 0,
- "print_hide": 0,
- "print_hide_if_no_value": 0,
- "read_only": 1,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
"fieldname": "column_break_5",
"fieldtype": "Column Break",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"length": 0,
@@ -144,8 +149,9 @@
"fieldtype": "Date",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
- "in_list_view": 1,
+ "in_list_view": 0,
"label": "Posting Date",
"length": 0,
"no_copy": 0,
@@ -169,6 +175,7 @@
"fieldtype": "Data",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Cheque Number",
@@ -194,6 +201,7 @@
"fieldtype": "Date",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
"label": "Cheque Date",
@@ -219,6 +227,7 @@
"fieldtype": "Date",
"hidden": 0,
"ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 1,
"label": "Clearance Date",
@@ -240,6 +249,7 @@
"hide_heading": 0,
"hide_toolbar": 0,
"idx": 1,
+ "image_view": 0,
"in_create": 0,
"in_dialog": 0,
"is_submittable": 0,
@@ -247,12 +257,15 @@
"istable": 1,
"max_attachments": 0,
"menu_index": 0,
- "modified": "2016-02-17 06:50:40.074578",
+ "modified": "2016-06-28 13:23:15.412477",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Bank Reconciliation Detail",
"owner": "Administrator",
"permissions": [],
+ "quick_entry": 1,
"read_only": 0,
- "read_only_onload": 0
+ "read_only_onload": 0,
+ "sort_order": "ASC",
+ "track_seen": 0
}
\ No newline at end of file
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index ad01eb3..da4f10d 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -90,14 +90,16 @@
frm.toggle_display("source_exchange_rate",
(frm.doc.paid_amount && frm.doc.paid_from_account_currency != company_currency));
- frm.toggle_display("target_exchange_rate",
- (frm.doc.received_amount && frm.doc.paid_to_account_currency != company_currency));
+ frm.toggle_display("target_exchange_rate", (frm.doc.received_amount &&
+ frm.doc.paid_to_account_currency != company_currency &&
+ frm.doc.paid_from_account_currency != frm.doc.paid_to_account_currency));
frm.toggle_display("base_paid_amount",
(!frm.doc.party && frm.doc.paid_from_account_currency != company_currency));
- frm.toggle_display("base_received_amount",
- (!frm.doc.party && frm.doc.paid_to_account_currency != company_currency));
+ frm.toggle_display("base_received_amount", (!frm.doc.party &&
+ frm.doc.paid_to_account_currency != company_currency &&
+ frm.doc.paid_from_account_currency != frm.doc.paid_to_account_currency));
frm.toggle_display("received_amount",
frm.doc.paid_from_account_currency != frm.doc.paid_to_account_currency)
@@ -116,7 +118,8 @@
frm.toggle_display("set_exchange_gain_loss",
(frm.doc.paid_amount && frm.doc.received_amount && frm.doc.difference_amount &&
- (frm.doc.paid_from_account_currency != frm.doc.paid_to_account_currency)));
+ (frm.doc.paid_from_account_currency != company_currency ||
+ frm.doc.paid_to_account_currency != company_currency)));
frm.refresh_fields();
},
@@ -193,7 +196,7 @@
}
},
- "party": function(frm) {
+ party: function(frm) {
if(frm.doc.payment_type && frm.doc.party_type && frm.doc.party) {
frm.set_party_account_based_on_party = true;
@@ -259,7 +262,7 @@
if(frm.set_party_account_based_on_party) return;
frm.events.set_account_currency_and_balance(frm, frm.doc.paid_from,
- "paid_from_account_currency", "paid_from_account_balance", function() {
+ "paid_from_account_currency", "paid_from_account_balance", function(frm) {
if(frm.doc.payment_type == "Receive") frm.events.get_outstanding_documents(frm);
}
);
@@ -269,7 +272,7 @@
if(frm.set_party_account_based_on_party) return;
frm.events.set_account_currency_and_balance(frm, frm.doc.paid_to,
- "paid_to_account_currency", "paid_to_account_balance", function() {
+ "paid_to_account_currency", "paid_to_account_balance", function(frm) {
if(frm.doc.payment_type == "Pay") frm.events.get_outstanding_documents(frm);
}
);
@@ -288,7 +291,15 @@
frm.set_value(currency_field, r.message['account_currency']);
frm.set_value(balance_field, r.message['account_balance']);
- if(callback_function) callback_function()
+ if(frm.doc.payment_type == "Receive" && currency_field=="paid_to_account_currency"
+ && !frm.doc.received_amount && frm.doc.paid_amount) {
+ frm.events.paid_amount(frm);
+ } else if(frm.doc.payment_type == "Pay" && currency_field=="paid_from_account_currency"
+ && !frm.doc.paid_amount && frm.doc.received_amount) {
+ frm.events.received_amount(frm);
+ }
+
+ if(callback_function) callback_function(frm);
frm.events.hide_unhide_fields(frm);
frm.events.set_dynamic_labels(frm);
@@ -391,10 +402,12 @@
},
get_outstanding_documents: function(frm) {
+ frm.clear_table("references");
+
+ if(!frm.doc.party) return;
+
frm.events.check_mandatory_to_fetch(frm);
var company_currency = frappe.get_doc(":Company", frm.doc.company).default_currency;
-
- frm.clear_table("references");
return frappe.call({
method: 'erpnext.accounts.doctype.payment_entry.payment_entry.get_outstanding_reference_documents',
@@ -417,7 +430,7 @@
c.total_amount = d.invoice_amount;
c.outstanding_amount = d.outstanding_amount;
if(!in_list(["Sales Order", "Purchase Order"], d.voucher_type))
- total_outstanding_amount += flt(d.outstanding_amount);
+ total_invoice_outstanding_amount += flt(d.outstanding_amount);
var party_account_currency = frm.doc.payment_type=="Receive" ?
frm.doc.paid_from_account_currency : frm.doc.paid_to_account_currency;
@@ -432,8 +445,12 @@
}
});
- var party_amt_field = frm.doc.payment_type=="Receive" ? "paid_amount" : "received_amount";
- frm.set_value(party_amt_field, total_outstanding_amount);
+ if(total_invoice_outstanding_amount > 0) {
+ var party_amt_field = (frm.doc.payment_type=="Receive") ?
+ "paid_amount" : "received_amount";
+ frm.set_value(party_amt_field, total_invoice_outstanding_amount);
+ }
+
}
frm.events.allocate_party_amount_against_ref_docs(frm,
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.json b/erpnext/accounts/doctype/payment_entry/payment_entry.json
index b827b7c..91aeeb0 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.json
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.json
@@ -831,7 +831,7 @@
"in_list_view": 0,
"label": "Clearance Date",
"length": 0,
- "no_copy": 0,
+ "no_copy": 1,
"permlevel": 0,
"precision": "",
"print_hide": 1,
@@ -1372,7 +1372,7 @@
"issingle": 0,
"istable": 0,
"max_attachments": 0,
- "modified": "2016-06-27 11:17:47.484139",
+ "modified": "2016-06-28 12:45:59.162749",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Entry",
diff --git a/erpnext/accounts/party.py b/erpnext/accounts/party.py
index 8d11374..f430b99 100644
--- a/erpnext/accounts/party.py
+++ b/erpnext/accounts/party.py
@@ -181,8 +181,16 @@
{"parenttype": party_group_doctype, "parent": group, "company": company}, "account")
if not account:
- default_account_name = "default_receivable_account" if party_type=="Customer" else "default_payable_account"
+ default_account_name = "default_receivable_account" \
+ if party_type=="Customer" else "default_payable_account"
account = frappe.db.get_value("Company", company, default_account_name)
+
+ existing_gle_currency = get_party_gle_currency(party_type, party, company)
+ if existing_gle_currency:
+ if account:
+ account_currency = frappe.db.get_value("Account", account, "account_currency")
+ if (account and account_currency != existing_gle_currency) or not account:
+ account = get_party_gle_account(party_type, party, company)
return account
@@ -203,6 +211,17 @@
return frappe.local_cache("party_gle_currency", (party_type, party, company), generator,
regenerate_if_none=True)
+
+def get_party_gle_account(party_type, party, company):
+ def generator():
+ existing_gle_account = frappe.db.sql("""select account from `tabGL Entry`
+ where docstatus=1 and company=%(company)s and party_type=%(party_type)s and party=%(party)s
+ limit 1""", { "company": company, "party_type": party_type, "party": party })
+
+ return existing_gle_account[0][0] if existing_gle_account else None
+
+ return frappe.local_cache("party_gle_account", (party_type, party, company), generator,
+ regenerate_if_none=True)
def validate_party_gle_currency(party_type, party, company, party_account_currency=None):
"""Validate party account currency with existing GL Entry's currency"""
diff --git a/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.js b/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.js
index 37d8130..ac3366c 100644
--- a/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.js
+++ b/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.js
@@ -22,6 +22,7 @@
"fieldtype": "Link",
"options": "Account",
"reqd": 1,
+ "default": locals[":Company"][frappe.defaults.get_user_default("Company")]["default_bank_account"],
"get_query": function() {
return {
"query": "erpnext.controllers.queries.get_account_list",
diff --git a/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py b/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py
index cfb677f..07ab969 100644
--- a/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py
+++ b/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py
@@ -3,7 +3,8 @@
from __future__ import unicode_literals
import frappe
-from frappe import _, msgprint
+from frappe import _
+from frappe.utils import nowdate, getdate
def execute(filters=None):
if not filters: filters = {}
@@ -14,28 +15,36 @@
return columns, data
def get_columns():
- return [_("Journal Entry") + ":Link/Journal Entry:140", _("Account") + ":Link/Account:140",
- _("Posting Date") + ":Date:100", _("Clearance Date") + ":Date:110", _("Against Account") + ":Link/Account:200",
- _("Debit") + ":Currency:120", _("Credit") + ":Currency:120"
+ return [
+ _("Payment Document") + ":Link/DocType:130",
+ _("Payment Entry") + ":Dynamic Link/"+_("Payment Document")+":110",
+ _("Posting Date") + ":Date:100",
+ _("Cheque/Reference No") + "::120",
+ _("Clearance Date") + ":Date:100",
+ _("Against Account") + ":Link/Account:170",
+ _("Amount") + ":Currency:120"
]
def get_conditions(filters):
conditions = ""
- if not filters.get("account"):
- msgprint(_("Please select Bank Account"), raise_exception=1)
- else:
- conditions += " and jvd.account = %(account)s"
- if filters.get("from_date"): conditions += " and jv.posting_date>=%(from_date)s"
- if filters.get("to_date"): conditions += " and jv.posting_date<=%(to_date)s"
+ if filters.get("from_date"): conditions += " and posting_date>=%(from_date)s"
+ if filters.get("to_date"): conditions += " and posting_date<=%(to_date)s"
return conditions
def get_entries(filters):
conditions = get_conditions(filters)
- entries = frappe.db.sql("""select jv.name, jvd.account, jv.posting_date,
- jv.clearance_date, jvd.against_account, jvd.debit, jvd.credit
+ journal_entries = frappe.db.sql("""select "Journal Entry", jv.name, jv.posting_date,
+ jv.cheque_no, jv.clearance_date, jvd.against_account, (jvd.debit - jvd.credit)
from `tabJournal Entry Account` jvd, `tabJournal Entry` jv
- where jvd.parent = jv.name and jv.docstatus=1 %s
- order by jv.name DESC""" % conditions, filters, as_list=1)
- return entries
\ No newline at end of file
+ where jvd.parent = jv.name and jv.docstatus=1 and jvd.account = %(account)s {0}
+ order by posting_date DESC, jv.name DESC""".format(conditions), filters, as_list=1)
+
+ payment_entries = frappe.db.sql("""select "Payment Entry", name, posting_date,
+ reference_no, clearance_date, party, if(paid_from=%(account)s, paid_amount, received_amount)
+ from `tabPayment Entry`
+ where docstatus=1 and (paid_from = %(account)s or paid_to = %(account)s) {0}
+ order by posting_date DESC, name DESC""".format(conditions), filters, as_list=1, debug=1)
+
+ return sorted(journal_entries + payment_entries, key=lambda k: k[2] or getdate(nowdate()))
\ 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 581a7c8..76e0de2 100644
--- a/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
+++ b/erpnext/accounts/report/bank_reconciliation_statement/bank_reconciliation_statement.py
@@ -3,7 +3,7 @@
from __future__ import unicode_literals
import frappe
-from frappe.utils import flt
+from frappe.utils import flt, getdate, nowdate
from frappe import _
def execute(filters=None):
@@ -24,16 +24,8 @@
for d in data:
total_debit += flt(d.debit)
total_credit += flt(d.credit)
-
- amounts_not_reflected_in_system = frappe.db.sql("""
- select sum(jvd.debit_in_account_currency - jvd.credit_in_account_currency)
- from `tabJournal Entry Account` jvd, `tabJournal Entry` jv
- where jvd.parent = jv.name and jv.docstatus=1 and jvd.account=%s
- and jv.posting_date > %s and jv.clearance_date <= %s and ifnull(jv.is_opening, 'No') = 'No'
- """, (filters["account"], filters["report_date"], filters["report_date"]))
-
- amounts_not_reflected_in_system = flt(amounts_not_reflected_in_system[0][0]) \
- if amounts_not_reflected_in_system else 0.0
+
+ amounts_not_reflected_in_system = get_amounts_not_reflected_in_system(filters)
bank_bal = flt(balance_as_per_system) - flt(total_debit) + flt(total_credit) \
+ amounts_not_reflected_in_system
@@ -42,7 +34,7 @@
get_balance_row(_("Bank Statement balance as per General Ledger"), balance_as_per_system, account_currency),
{},
{
- "journal_entry": _("Outstanding Cheques and Deposits to clear"),
+ "payment_entry": _("Outstanding Cheques and Deposits to clear"),
"debit": total_debit,
"credit": total_credit,
"account_currency": account_currency
@@ -61,13 +53,21 @@
"fieldname": "posting_date",
"label": _("Posting Date"),
"fieldtype": "Date",
- "width": 100
+ "width": 90
},
{
- "fieldname": "journal_entry",
- "label": _("Journal Entry"),
+ "fieldname": "payment_document",
+ "label": _("Payment Document"),
"fieldtype": "Link",
- "options": "Journal Entry",
+ "options": "DocType",
+ "width": 120,
+ "hidden": 1
+ },
+ {
+ "fieldname": "payment_entry",
+ "label": _("Payment Entry"),
+ "fieldtype": "Dynamic Link",
+ "options": "payment_document",
"width": 220
},
{
@@ -92,7 +92,7 @@
"width": 200
},
{
- "fieldname": "reference",
+ "fieldname": "reference_no",
"label": _("Reference"),
"fieldtype": "Data",
"width": 100
@@ -119,31 +119,67 @@
]
def get_entries(filters):
- entries = frappe.db.sql("""select
- jv.posting_date, jv.name as journal_entry, jvd.debit_in_account_currency as debit,
+ journal_entries = frappe.db.sql("""
+ select "Journal Entry" as payment_document, jv.posting_date,
+ jv.name as payment_entry, jvd.debit_in_account_currency as debit,
jvd.credit_in_account_currency as credit, jvd.against_account,
- jv.cheque_no as reference, jv.cheque_date as ref_date, jv.clearance_date, jvd.account_currency
+ jv.cheque_no as reference_no, jv.cheque_date as ref_date, jv.clearance_date, jvd.account_currency
from
`tabJournal Entry Account` jvd, `tabJournal Entry` jv
where jvd.parent = jv.name and jv.docstatus=1
and jvd.account = %(account)s and jv.posting_date <= %(report_date)s
and ifnull(jv.clearance_date, '4000-01-01') > %(report_date)s
- and ifnull(jv.is_opening, 'No') = 'No'
- order by jv.posting_date DESC,jv.name DESC""", filters, as_dict=1)
+ and ifnull(jv.is_opening, 'No') = 'No'""", filters, as_dict=1)
+
+ payment_entries = frappe.db.sql("""
+ select
+ "Payment Entry" as payment_document, name as payment_entry,
+ reference_no, reference_date as ref_date,
+ if(paid_to=%(account)s, received_amount, 0) as debit,
+ if(paid_from=%(account)s, paid_amount, 0) as credit,
+ posting_date, party as against_account, clearance_date,
+ if(paid_to=%(account)s, paid_to_account_currency, paid_from_account_currency) as account_currency
+ from `tabPayment Entry`
+ where
+ (paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
+ and posting_date <= %(report_date)s
+ and ifnull(clearance_date, '4000-01-01') > %(report_date)s
+ """, filters, as_dict=1)
- return entries
+ return sorted(list(payment_entries)+list(journal_entries),
+ key=lambda k: k['posting_date'] or getdate(nowdate()))
+
+def get_amounts_not_reflected_in_system(filters):
+ je_amount = frappe.db.sql("""
+ select sum(jvd.debit_in_account_currency - jvd.credit_in_account_currency)
+ from `tabJournal Entry Account` jvd, `tabJournal Entry` jv
+ where jvd.parent = jv.name and jv.docstatus=1 and jvd.account=%(account)s
+ and jv.posting_date > %(report_date)s and jv.clearance_date <= %(report_date)s
+ and ifnull(jv.is_opening, 'No') = 'No' """, filters)
+
+ je_amount = flt(je_amount[0][0]) if je_amount else 0.0
+
+ pe_amount = frappe.db.sql("""
+ select sum(if(paid_from=%(account)s, paid_amount, received_amount))
+ from `tabPayment Entry`
+ where (paid_from=%(account)s or paid_to=%(account)s) and docstatus=1
+ and posting_date > %(report_date)s and clearance_date <= %(report_date)s""", filters)
+
+ pe_amount = flt(pe_amount[0][0]) if pe_amount else 0.0
+
+ return je_amount + pe_amount
def get_balance_row(label, amount, account_currency):
if amount > 0:
return {
- "journal_entry": label,
+ "payment_entry": label,
"debit": amount,
"credit": 0,
"account_currency": account_currency
}
else:
return {
- "journal_entry": label,
+ "payment_entry": label,
"debit": 0,
"credit": abs(amount),
"account_currency": account_currency
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 56a485e..048a534 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -473,7 +473,7 @@
}, as_dict=True)
for d in invoice_list:
- outstanding_invoices.append({
+ outstanding_invoices.append(frappe._dict({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
@@ -481,7 +481,7 @@
'payment_amount': flt(d.payment_amount),
'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision),
'due_date': frappe.db.get_value(d.voucher_type, d.voucher_no, "due_date"),
- })
+ }))
outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate()))