fix: Missing commits from hotfix branch (#17997)
* fix: merge conflict
* fix: restored missing set_gst_state_and_state_number function
* fix: style linting as per codacy
* fix: Fixes related to customer/lead merging
* fix: merge conflict
* fix: Fixes related to customer/lead merging
* fix: Assign isue/opportunity to user
* fix: Assign isue/opportunity to user
* fix: Replaced Invoice type by GST Category
* fix: merge conflict
* fix: merge conflict
* fix: test cases
* fix: test cases
diff --git a/erpnext/accounts/page/bank_reconciliation/__init__.py b/erpnext/accounts/page/bank_reconciliation/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/__init__.py
diff --git a/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.js b/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.js
new file mode 100644
index 0000000..6eafa0d
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.js
@@ -0,0 +1,578 @@
+frappe.provide("erpnext.accounts");
+
+frappe.pages['bank-reconciliation'].on_page_load = function(wrapper) {
+ new erpnext.accounts.bankReconciliation(wrapper);
+}
+
+erpnext.accounts.bankReconciliation = class BankReconciliation {
+ constructor(wrapper) {
+ this.page = frappe.ui.make_app_page({
+ parent: wrapper,
+ title: __("Bank Reconciliation"),
+ single_column: true
+ });
+ this.parent = wrapper;
+ this.page = this.parent.page;
+
+ this.check_plaid_status();
+ this.make();
+ }
+
+ make() {
+ const me = this;
+
+ me.$main_section = $(`<div class="reconciliation page-main-content"></div>`).appendTo(me.page.main);
+ const empty_state = __("Upload a bank statement, link or reconcile a bank account")
+ me.$main_section.append(`<div class="flex justify-center align-center text-muted"
+ style="height: 50vh; display: flex;"><h5 class="text-muted">${empty_state}</h5></div>`)
+
+ me.page.add_field({
+ fieldtype: 'Link',
+ label: __('Company'),
+ fieldname: 'company',
+ options: "Company",
+ onchange: function() {
+ if (this.value) {
+ me.company = this.value;
+ } else {
+ me.company = null;
+ me.bank_account = null;
+ }
+ }
+ })
+ me.page.add_field({
+ fieldtype: 'Link',
+ label: __('Bank Account'),
+ fieldname: 'bank_account',
+ options: "Bank Account",
+ get_query: function() {
+ if(!me.company) {
+ frappe.throw(__("Please select company first"));
+ return
+ }
+
+ return {
+ filters: {
+ "company": me.company
+ }
+ }
+ },
+ onchange: function() {
+ if (this.value) {
+ me.bank_account = this.value;
+ me.add_actions();
+ } else {
+ me.bank_account = null;
+ me.page.hide_actions_menu();
+ }
+ }
+ })
+ }
+
+ check_plaid_status() {
+ const me = this;
+ frappe.db.get_value("Plaid Settings", "Plaid Settings", "enabled", (r) => {
+ if (r && r.enabled == "1") {
+ me.plaid_status = "active"
+ } else {
+ me.plaid_status = "inactive"
+ }
+ })
+ }
+
+ add_actions() {
+ const me = this;
+
+ me.page.show_menu()
+
+ me.page.add_menu_item(__("Upload a statement"), function() {
+ me.clear_page_content();
+ new erpnext.accounts.bankTransactionUpload(me);
+ }, true)
+
+ if (me.plaid_status==="active") {
+ me.page.add_menu_item(__("Synchronize this account"), function() {
+ me.clear_page_content();
+ new erpnext.accounts.bankTransactionSync(me);
+ }, true)
+ }
+
+ me.page.add_menu_item(__("Reconcile this account"), function() {
+ me.clear_page_content();
+ me.make_reconciliation_tool();
+ }, true)
+ }
+
+ clear_page_content() {
+ const me = this;
+ $(me.page.body).find('.frappe-list').remove();
+ me.$main_section.empty();
+ }
+
+ make_reconciliation_tool() {
+ const me = this;
+ frappe.model.with_doctype("Bank Transaction", () => {
+ erpnext.accounts.ReconciliationList = new erpnext.accounts.ReconciliationTool({
+ parent: me.parent,
+ doctype: "Bank Transaction"
+ });
+ })
+ }
+}
+
+
+erpnext.accounts.bankTransactionUpload = class bankTransactionUpload {
+ constructor(parent) {
+ this.parent = parent;
+ this.data = [];
+
+ const assets = [
+ "/assets/frappe/css/frappe-datatable.css",
+ "/assets/frappe/js/lib/clusterize.min.js",
+ "/assets/frappe/js/lib/Sortable.min.js",
+ "/assets/frappe/js/lib/frappe-datatable.js"
+ ];
+
+ frappe.require(assets, () => {
+ this.make();
+ });
+ }
+
+ make() {
+ const me = this;
+ frappe.upload.make({
+ args: {
+ method: 'erpnext.accounts.doctype.bank_transaction.bank_transaction_upload.upload_bank_statement',
+ allow_multiple: 0
+ },
+ no_socketio: true,
+ sample_url: "e.g. http://example.com/somefile.csv",
+ callback: function(attachment, r) {
+ if (!r.exc && r.message) {
+ me.data = r.message;
+ me.setup_transactions_dom();
+ me.create_datatable();
+ me.add_primary_action();
+ }
+ }
+ })
+ }
+
+ setup_transactions_dom() {
+ const me = this;
+ me.parent.$main_section.append(`<div class="transactions-table"></div>`)
+ }
+
+ create_datatable() {
+ try {
+ this.datatable = new DataTable('.transactions-table', {
+ columns: this.data.columns,
+ data: this.data.data
+ })
+ }
+ catch(err) {
+ let msg = __(`Your file could not be processed by ERPNext.
+ <br>It should be a standard CSV or XLSX file.
+ <br>The headers should be in the first row.`)
+ frappe.throw(msg)
+ }
+
+ }
+
+ add_primary_action() {
+ const me = this;
+ me.parent.page.set_primary_action(__("Submit"), function() {
+ me.add_bank_entries()
+ }, null, __("Creating bank entries..."))
+ }
+
+ add_bank_entries() {
+ const me = this;
+ frappe.xcall('erpnext.accounts.doctype.bank_transaction.bank_transaction_upload.create_bank_entries',
+ {columns: this.datatable.datamanager.columns, data: this.datatable.datamanager.data, bank_account: me.parent.bank_account}
+ ).then((result) => {
+ let result_title = result.errors == 0 ? __("{0} bank transaction(s) created", [result.success]) : __("{0} bank transaction(s) created and {1} errors", [result.success, result.errors])
+ let result_msg = `
+ <div class="flex justify-center align-center text-muted" style="height: 50vh; display: flex;">
+ <h5 class="text-muted">${result_title}</h5>
+ </div>`
+ me.parent.page.clear_primary_action();
+ me.parent.$main_section.empty();
+ me.parent.$main_section.append(result_msg);
+ if (result.errors == 0) {
+ frappe.show_alert({message:__("All bank transactions have been created"), indicator:'green'});
+ } else {
+ frappe.show_alert({message:__("Please check the error log for details about the import errors"), indicator:'red'});
+ }
+ })
+ }
+}
+
+erpnext.accounts.bankTransactionSync = class bankTransactionSync {
+ constructor(parent) {
+ this.parent = parent;
+ this.data = [];
+
+ this.init_config()
+ }
+
+ init_config() {
+ const me = this;
+ frappe.xcall('erpnext.erpnext_integrations.doctype.plaid_settings.plaid_settings.plaid_configuration')
+ .then(result => {
+ me.plaid_env = result.plaid_env;
+ me.plaid_public_key = result.plaid_public_key;
+ me.client_name = result.client_name;
+ me.sync_transactions()
+ })
+ }
+
+ sync_transactions() {
+ const me = this;
+ frappe.db.get_value("Bank Account", me.parent.bank_account, "bank", (v) => {
+ frappe.xcall('erpnext.erpnext_integrations.doctype.plaid_settings.plaid_settings.sync_transactions', {
+ bank: v['bank'],
+ bank_account: me.parent.bank_account,
+ freeze: true
+ })
+ .then((result) => {
+ let result_title = (result.length > 0) ? __("{0} bank transaction(s) created", [result.length]) : __("This bank account is already synchronized")
+ let result_msg = `
+ <div class="flex justify-center align-center text-muted" style="height: 50vh; display: flex;">
+ <h5 class="text-muted">${result_title}</h5>
+ </div>`
+ this.parent.$main_section.append(result_msg)
+ frappe.show_alert({message:__("Bank account '{0}' has been synchronized", [me.parent.bank_account]), indicator:'green'});
+ })
+ })
+ }
+}
+
+
+erpnext.accounts.ReconciliationTool = class ReconciliationTool extends frappe.views.BaseList {
+ constructor(opts) {
+ super(opts);
+ this.show();
+ }
+
+ setup_defaults() {
+ super.setup_defaults();
+
+ this.page_title = __("Bank Reconciliation");
+ this.doctype = 'Bank Transaction';
+ this.fields = ['date', 'description', 'debit', 'credit', 'currency']
+
+ }
+
+ setup_view() {
+ this.render_header();
+ }
+
+ setup_side_bar() {
+ //
+ }
+
+ make_standard_filters() {
+ //
+ }
+
+ freeze() {
+ this.$result.find('.list-count').html(`<span>${__('Refreshing')}...</span>`);
+ }
+
+ get_args() {
+ const args = super.get_args();
+
+ return Object.assign({}, args, {
+ ...args.filters.push(["Bank Transaction", "docstatus", "=", 1],
+ ["Bank Transaction", "unallocated_amount", ">", 0])
+ });
+
+ }
+
+ update_data(r) {
+ let data = r.message || [];
+
+ if (this.start === 0) {
+ this.data = data;
+ } else {
+ this.data = this.data.concat(data);
+ }
+ }
+
+ render() {
+ const me = this;
+ this.$result.find('.list-row-container').remove();
+ $('[data-fieldname="name"]').remove();
+ me.data.map((value) => {
+ const row = $('<div class="list-row-container">').data("data", value).appendTo(me.$result).get(0);
+ new erpnext.accounts.ReconciliationRow(row, value);
+ })
+ }
+
+ render_header() {
+ const me = this;
+ if ($(this.wrapper).find('.transaction-header').length === 0) {
+ me.$result.append(frappe.render_template("bank_transaction_header"));
+ }
+ }
+}
+
+erpnext.accounts.ReconciliationRow = class ReconciliationRow {
+ constructor(row, data) {
+ this.data = data;
+ this.row = row;
+ this.make();
+ this.bind_events();
+ }
+
+ make() {
+ $(this.row).append(frappe.render_template("bank_transaction_row", this.data))
+ }
+
+ bind_events() {
+ const me = this;
+ $(me.row).on('click', '.clickable-section', function() {
+ me.bank_entry = $(this).attr("data-name");
+ me.show_dialog($(this).attr("data-name"));
+ })
+
+ $(me.row).on('click', '.new-reconciliation', function() {
+ me.bank_entry = $(this).attr("data-name");
+ me.show_dialog($(this).attr("data-name"));
+ })
+
+ $(me.row).on('click', '.new-payment', function() {
+ me.bank_entry = $(this).attr("data-name");
+ me.new_payment();
+ })
+
+ $(me.row).on('click', '.new-invoice', function() {
+ me.bank_entry = $(this).attr("data-name");
+ me.new_invoice();
+ })
+
+ $(me.row).on('click', '.new-expense', function() {
+ me.bank_entry = $(this).attr("data-name");
+ me.new_expense();
+ })
+ }
+
+ new_payment() {
+ const me = this;
+ const paid_amount = me.data.credit > 0 ? me.data.credit : me.data.debit;
+ const payment_type = me.data.credit > 0 ? "Receive": "Pay";
+ const party_type = me.data.credit > 0 ? "Customer": "Supplier";
+
+ frappe.new_doc("Payment Entry", {"payment_type": payment_type, "paid_amount": paid_amount,
+ "party_type": party_type, "paid_from": me.data.bank_account})
+ }
+
+ new_invoice() {
+ const me = this;
+ const invoice_type = me.data.credit > 0 ? "Sales Invoice" : "Purchase Invoice";
+
+ frappe.new_doc(invoice_type)
+ }
+
+ new_expense() {
+ frappe.new_doc("Expense Claim")
+ }
+
+
+ show_dialog(data) {
+ const me = this;
+
+ frappe.db.get_value("Bank Account", me.data.bank_account, "account", (r) => {
+ me.gl_account = r.account;
+ })
+
+ frappe.xcall('erpnext.accounts.page.bank_reconciliation.bank_reconciliation.get_linked_payments',
+ {bank_transaction: data, freeze:true, freeze_message:__("Finding linked payments")}
+ ).then((result) => {
+ me.make_dialog(result)
+ })
+ }
+
+ make_dialog(data) {
+ const me = this;
+ me.selected_payment = null;
+
+ const fields = [
+ {
+ fieldtype: 'Section Break',
+ fieldname: 'section_break_1',
+ label: __('Automatic Reconciliation')
+ },
+ {
+ fieldtype: 'HTML',
+ fieldname: 'payment_proposals'
+ },
+ {
+ fieldtype: 'Section Break',
+ fieldname: 'section_break_2',
+ label: __('Search for a payment')
+ },
+ {
+ fieldtype: 'Link',
+ fieldname: 'payment_doctype',
+ options: 'DocType',
+ label: 'Payment DocType',
+ get_query: () => {
+ return {
+ filters : {
+ "name": ["in", ["Payment Entry", "Journal Entry", "Sales Invoice", "Purchase Invoice", "Expense Claim"]]
+ }
+ }
+ },
+ },
+ {
+ fieldtype: 'Column Break',
+ fieldname: 'column_break_1',
+ },
+ {
+ fieldtype: 'Dynamic Link',
+ fieldname: 'payment_entry',
+ options: 'payment_doctype',
+ label: 'Payment Document',
+ get_query: () => {
+ let dt = this.dialog.fields_dict.payment_doctype.value;
+ if (dt === "Payment Entry") {
+ return {
+ query: "erpnext.accounts.page.bank_reconciliation.bank_reconciliation.payment_entry_query",
+ filters : {
+ "bank_account": this.data.bank_account,
+ "company": this.data.company
+ }
+ }
+ } else if (dt === "Journal Entry") {
+ return {
+ query: "erpnext.accounts.page.bank_reconciliation.bank_reconciliation.journal_entry_query",
+ filters : {
+ "bank_account": this.data.bank_account,
+ "company": this.data.company
+ }
+ }
+ } else if (dt === "Sales Invoice") {
+ return {
+ query: "erpnext.accounts.page.bank_reconciliation.bank_reconciliation.sales_invoices_query"
+ }
+ } else if (dt === "Purchase Invoice") {
+ return {
+ filters : [
+ ["Purchase Invoice", "ifnull(clearance_date, '')", "=", ""],
+ ["Purchase Invoice", "docstatus", "=", 1],
+ ["Purchase Invoice", "company", "=", this.data.company]
+ ]
+ }
+ } else if (dt === "Expense Claim") {
+ return {
+ filters : [
+ ["Expense Claim", "ifnull(clearance_date, '')", "=", ""],
+ ["Expense Claim", "docstatus", "=", 1],
+ ["Expense Claim", "company", "=", this.data.company]
+ ]
+ }
+ }
+ },
+ onchange: function() {
+ if (me.selected_payment !== this.value) {
+ me.selected_payment = this.value;
+ me.display_payment_details(this);
+ }
+ }
+ },
+ {
+ fieldtype: 'Section Break',
+ fieldname: 'section_break_3'
+ },
+ {
+ fieldtype: 'HTML',
+ fieldname: 'payment_details'
+ },
+ ];
+
+ me.dialog = new frappe.ui.Dialog({
+ title: __("Choose a corresponding payment"),
+ fields: fields,
+ size: "large"
+ });
+
+ const proposals_wrapper = me.dialog.fields_dict.payment_proposals.$wrapper;
+ if (data && data.length > 0) {
+ proposals_wrapper.append(frappe.render_template("linked_payment_header"));
+ data.map(value => {
+ proposals_wrapper.append(frappe.render_template("linked_payment_row", value))
+ })
+ } else {
+ const empty_data_msg = __("ERPNext could not find any matching payment entry")
+ proposals_wrapper.append(`<div class="text-center"><h5 class="text-muted">${empty_data_msg}</h5></div>`)
+ }
+
+ $(me.dialog.body).on('click', '.reconciliation-btn', (e) => {
+ const payment_entry = $(e.target).attr('data-name');
+ const payment_doctype = $(e.target).attr('data-doctype');
+ frappe.xcall('erpnext.accounts.page.bank_reconciliation.bank_reconciliation.reconcile',
+ {bank_transaction: me.bank_entry, payment_doctype: payment_doctype, payment_name: payment_entry})
+ .then((result) => {
+ setTimeout(function(){
+ erpnext.accounts.ReconciliationList.refresh();
+ }, 2000);
+ me.dialog.hide();
+ })
+ })
+
+ me.dialog.show();
+ }
+
+ display_payment_details(event) {
+ const me = this;
+ if (event.value) {
+ let dt = me.dialog.fields_dict.payment_doctype.value;
+ me.dialog.fields_dict['payment_details'].$wrapper.empty();
+ frappe.db.get_doc(dt, event.value)
+ .then(doc => {
+ let displayed_docs = []
+ if (dt === "Payment Entry") {
+ payment.currency = doc.payment_type == "Receive" ? doc.paid_to_account_currency : doc.paid_from_account_currency;
+ payment.doctype = dt
+ displayed_docs.push(payment);
+ } else if (dt === "Journal Entry") {
+ doc.accounts.forEach(payment => {
+ if (payment.account === me.gl_account) {
+ payment.doctype = dt;
+ payment.posting_date = doc.posting_date;
+ payment.party = doc.pay_to_recd_from;
+ payment.reference_no = doc.cheque_no;
+ payment.reference_date = doc.cheque_date;
+ payment.currency = payment.account_currency;
+ payment.paid_amount = payment.credit > 0 ? payment.credit : payment.debit;
+ payment.name = doc.name;
+ displayed_docs.push(payment);
+ }
+ })
+ } else if (dt === "Sales Invoice") {
+ doc.payments.forEach(payment => {
+ if (payment.clearance_date === null || payment.clearance_date === "") {
+ payment.doctype = dt;
+ payment.posting_date = doc.posting_date;
+ payment.party = doc.customer;
+ payment.reference_no = doc.remarks;
+ payment.currency = doc.currency;
+ payment.paid_amount = payment.amount;
+ payment.name = doc.name;
+ displayed_docs.push(payment);
+ }
+ })
+ }
+
+ const details_wrapper = me.dialog.fields_dict.payment_details.$wrapper;
+ details_wrapper.append(frappe.render_template("linked_payment_header"));
+ displayed_docs.forEach(values => {
+ details_wrapper.append(frappe.render_template("linked_payment_row", values));
+ })
+ })
+ }
+
+ }
+}
\ No newline at end of file
diff --git a/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.json b/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.json
new file mode 100644
index 0000000..feea368
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.json
@@ -0,0 +1,29 @@
+{
+ "content": null,
+ "creation": "2018-11-24 12:03:14.646669",
+ "docstatus": 0,
+ "doctype": "Page",
+ "idx": 0,
+ "modified": "2018-11-24 12:03:14.646669",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "bank-reconciliation",
+ "owner": "Administrator",
+ "page_name": "bank-reconciliation",
+ "roles": [
+ {
+ "role": "System Manager"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Accounts User"
+ }
+ ],
+ "script": null,
+ "standard": "Yes",
+ "style": null,
+ "system_page": 0,
+ "title": "Bank Reconciliation"
+}
\ No newline at end of file
diff --git a/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.py b/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.py
new file mode 100644
index 0000000..36c9399
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/bank_reconciliation.py
@@ -0,0 +1,378 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2018, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+import difflib
+from frappe.utils import flt
+from six import iteritems
+from erpnext import get_company_currency
+
+@frappe.whitelist()
+def reconcile(bank_transaction, payment_doctype, payment_name):
+ transaction = frappe.get_doc("Bank Transaction", bank_transaction)
+ payment_entry = frappe.get_doc(payment_doctype, payment_name)
+
+ account = frappe.db.get_value("Bank Account", transaction.bank_account, "account")
+ gl_entry = frappe.get_doc("GL Entry", dict(account=account, voucher_type=payment_doctype, voucher_no=payment_name))
+
+ if transaction.unallocated_amount == 0:
+ frappe.throw(_("This bank transaction is already fully reconciled"))
+
+ if transaction.credit > 0 and gl_entry.credit > 0:
+ frappe.throw(_("The selected payment entry should be linked with a debtor bank transaction"))
+
+ if transaction.debit > 0 and gl_entry.debit > 0:
+ frappe.throw(_("The selected payment entry should be linked with a creditor bank transaction"))
+
+ add_payment_to_transaction(transaction, payment_entry, gl_entry)
+
+ return 'reconciled'
+
+def add_payment_to_transaction(transaction, payment_entry, gl_entry):
+ gl_amount, transaction_amount = (gl_entry.credit, transaction.debit) if gl_entry.credit > 0 else (gl_entry.debit, transaction.credit)
+ allocated_amount = gl_amount if gl_amount <= transaction_amount else transaction_amount
+ transaction.append("payment_entries", {
+ "payment_document": payment_entry.doctype,
+ "payment_entry": payment_entry.name,
+ "allocated_amount": allocated_amount
+ })
+
+ transaction.save()
+ transaction.update_allocations()
+
+@frappe.whitelist()
+def get_linked_payments(bank_transaction):
+ transaction = frappe.get_doc("Bank Transaction", bank_transaction)
+ bank_account = frappe.db.get_values("Bank Account", transaction.bank_account, ["account", "company"], as_dict=True)
+
+ # Get all payment entries with a matching amount
+ amount_matching = check_matching_amount(bank_account[0].account, bank_account[0].company, transaction)
+
+ # Get some data from payment entries linked to a corresponding bank transaction
+ description_matching = get_matching_descriptions_data(bank_account[0].company, transaction)
+
+ if amount_matching:
+ return check_amount_vs_description(amount_matching, description_matching)
+
+ elif description_matching:
+ description_matching = filter(lambda x: not x.get('clearance_date'), description_matching)
+ if not description_matching:
+ return []
+
+ return sorted(list(description_matching), key = lambda x: x["posting_date"], reverse=True)
+
+ else:
+ return []
+
+def check_matching_amount(bank_account, company, transaction):
+ payments = []
+ amount = transaction.credit if transaction.credit > 0 else transaction.debit
+
+ payment_type = "Receive" if transaction.credit > 0 else "Pay"
+ account_from_to = "paid_to" if transaction.credit > 0 else "paid_from"
+ currency_field = "paid_to_account_currency as currency" if transaction.credit > 0 else "paid_from_account_currency as currency"
+
+ payment_entries = frappe.get_all("Payment Entry", fields=["'Payment Entry' as doctype", "name", "paid_amount", "payment_type", "reference_no", "reference_date",
+ "party", "party_type", "posting_date", "{0}".format(currency_field)], filters=[["paid_amount", "like", "{0}%".format(amount)],
+ ["docstatus", "=", "1"], ["payment_type", "=", [payment_type, "Internal Transfer"]], ["ifnull(clearance_date, '')", "=", ""], ["{0}".format(account_from_to), "=", "{0}".format(bank_account)]])
+
+ if transaction.credit > 0:
+ journal_entries = frappe.db.sql("""
+ SELECT
+ 'Journal Entry' as doctype, je.name, je.posting_date, je.cheque_no as reference_no,
+ je.pay_to_recd_from as party, je.cheque_date as reference_date, jea.debit_in_account_currency as paid_amount
+ FROM
+ `tabJournal Entry Account` as jea
+ JOIN
+ `tabJournal Entry` as je
+ ON
+ jea.parent = je.name
+ WHERE
+ (je.clearance_date is null or je.clearance_date='0000-00-00')
+ AND
+ jea.account = %s
+ AND
+ jea.debit_in_account_currency like %s
+ AND
+ je.docstatus = 1
+ """, (bank_account, amount), as_dict=True)
+ else:
+ journal_entries = frappe.db.sql("""
+ SELECT
+ 'Journal Entry' as doctype, je.name, je.posting_date, je.cheque_no as reference_no,
+ jea.account_currency as currency, je.pay_to_recd_from as party, je.cheque_date as reference_date,
+ jea.credit_in_account_currency as paid_amount
+ FROM
+ `tabJournal Entry Account` as jea
+ JOIN
+ `tabJournal Entry` as je
+ ON
+ jea.parent = je.name
+ WHERE
+ (je.clearance_date is null or je.clearance_date='0000-00-00')
+ AND
+ jea.account = %(bank_account)s
+ AND
+ jea.credit_in_account_currency like %(txt)s
+ AND
+ je.docstatus = 1
+ """, {
+ 'bank_account': bank_account,
+ 'txt': '%%%s%%' % amount
+ }, as_dict=True)
+
+ frappe.errprint(journal_entries)
+
+ if transaction.credit > 0:
+ sales_invoices = frappe.db.sql("""
+ SELECT
+ 'Sales Invoice' as doctype, si.name, si.customer as party,
+ si.posting_date, sip.amount as paid_amount
+ FROM
+ `tabSales Invoice Payment` as sip
+ JOIN
+ `tabSales Invoice` as si
+ ON
+ sip.parent = si.name
+ WHERE
+ (sip.clearance_date is null or sip.clearance_date='0000-00-00')
+ AND
+ sip.account = %s
+ AND
+ sip.amount like %s
+ AND
+ si.docstatus = 1
+ """, (bank_account, amount), as_dict=True)
+ else:
+ sales_invoices = []
+
+ if transaction.debit > 0:
+ purchase_invoices = frappe.get_all("Purchase Invoice",
+ fields = ["'Purchase Invoice' as doctype", "name", "paid_amount", "supplier as party", "posting_date", "currency"],
+ filters=[
+ ["paid_amount", "like", "{0}%".format(amount)],
+ ["docstatus", "=", "1"],
+ ["is_paid", "=", "1"],
+ ["ifnull(clearance_date, '')", "=", ""],
+ ["cash_bank_account", "=", "{0}".format(bank_account)]
+ ]
+ )
+
+ mode_of_payments = [x["parent"] for x in frappe.db.get_list("Mode of Payment Account",
+ filters={"default_account": bank_account}, fields=["parent"])]
+
+ company_currency = get_company_currency(company)
+
+ expense_claims = frappe.get_all("Expense Claim",
+ fields=["'Expense Claim' as doctype", "name", "total_sanctioned_amount as paid_amount",
+ "employee as party", "posting_date", "'{0}' as currency".format(company_currency)],
+ filters=[
+ ["total_sanctioned_amount", "like", "{0}%".format(amount)],
+ ["docstatus", "=", "1"],
+ ["is_paid", "=", "1"],
+ ["ifnull(clearance_date, '')", "=", ""],
+ ["mode_of_payment", "in", "{0}".format(tuple(mode_of_payments))]
+ ]
+ )
+ else:
+ purchase_invoices = expense_claims = []
+
+ for data in [payment_entries, journal_entries, sales_invoices, purchase_invoices, expense_claims]:
+ if data:
+ payments.extend(data)
+
+ return payments
+
+def get_matching_descriptions_data(company, transaction):
+ if not transaction.description :
+ return []
+
+ bank_transactions = frappe.db.sql("""
+ SELECT
+ bt.name, bt.description, bt.date, btp.payment_document, btp.payment_entry
+ FROM
+ `tabBank Transaction` as bt
+ LEFT JOIN
+ `tabBank Transaction Payments` as btp
+ ON
+ bt.name = btp.parent
+ WHERE
+ bt.allocated_amount > 0
+ AND
+ bt.docstatus = 1
+ """, as_dict=True)
+
+ selection = []
+ for bank_transaction in bank_transactions:
+ if bank_transaction.description:
+ seq=difflib.SequenceMatcher(lambda x: x == " ", transaction.description, bank_transaction.description)
+
+ if seq.ratio() > 0.6:
+ bank_transaction["ratio"] = seq.ratio()
+ selection.append(bank_transaction)
+
+ document_types = set([x["payment_document"] for x in selection])
+
+ links = {}
+ for document_type in document_types:
+ links[document_type] = [x["payment_entry"] for x in selection if x["payment_document"]==document_type]
+
+
+ data = []
+ company_currency = get_company_currency(company)
+ for key, value in iteritems(links):
+ if key == "Payment Entry":
+ data.extend(frappe.get_all("Payment Entry", filters=[["name", "in", value]],
+ fields=["'Payment Entry' as doctype", "posting_date", "party", "reference_no",
+ "reference_date", "paid_amount", "paid_to_account_currency as currency", "clearance_date"]))
+ if key == "Journal Entry":
+ journal_entries = frappe.get_all("Journal Entry", filters=[["name", "in", value]],
+ fields=["name", "'Journal Entry' as doctype", "posting_date",
+ "pay_to_recd_from as party", "cheque_no as reference_no", "cheque_date as reference_date",
+ "total_credit as paid_amount", "clearance_date"])
+ for journal_entry in journal_entries:
+ journal_entry_accounts = frappe.get_all("Journal Entry Account", filters={"parenttype": journal_entry["doctype"], "parent": journal_entry["name"]}, fields=["account_currency"])
+ journal_entry["currency"] = journal_entry_accounts[0]["account_currency"] if journal_entry_accounts else company_currency
+ data.extend(journal_entries)
+ if key == "Sales Invoice":
+ data.extend(frappe.get_all("Sales Invoice", filters=[["name", "in", value]], fields=["'Sales Invoice' as doctype", "posting_date", "customer_name as party", "paid_amount", "currency"]))
+ if key == "Purchase Invoice":
+ data.extend(frappe.get_all("Purchase Invoice", filters=[["name", "in", value]], fields=["'Purchase Invoice' as doctype", "posting_date", "supplier_name as party", "paid_amount", "currency"]))
+ if key == "Expense Claim":
+ expense_claims = frappe.get_all("Expense Claim", filters=[["name", "in", value]], fields=["'Expense Claim' as doctype", "posting_date", "employee_name as party", "total_amount_reimbursed as paid_amount"])
+ data.extend([dict(x,**{"currency": company_currency}) for x in expense_claims])
+
+ return data
+
+def check_amount_vs_description(amount_matching, description_matching):
+ result = []
+
+ if description_matching:
+ for am_match in amount_matching:
+ for des_match in description_matching:
+ if des_match.get("clearance_date"):
+ continue
+
+ if am_match["party"] == des_match["party"]:
+ if am_match not in result:
+ result.append(am_match)
+ continue
+
+ if "reference_no" in am_match and "reference_no" in des_match:
+ if difflib.SequenceMatcher(lambda x: x == " ", am_match["reference_no"], des_match["reference_no"]).ratio() > 70:
+ if am_match not in result:
+ result.append(am_match)
+ if result:
+ return sorted(result, key = lambda x: x["posting_date"], reverse=True)
+ else:
+ return sorted(amount_matching, key = lambda x: x["posting_date"], reverse=True)
+
+ else:
+ return sorted(amount_matching, key = lambda x: x["posting_date"], reverse=True)
+
+def get_matching_transactions_payments(description_matching):
+ payments = [x["payment_entry"] for x in description_matching]
+
+ payment_by_ratio = {x["payment_entry"]: x["ratio"] for x in description_matching}
+
+ if payments:
+ reference_payment_list = frappe.get_all("Payment Entry", fields=["name", "paid_amount", "payment_type", "reference_no", "reference_date",
+ "party", "party_type", "posting_date", "paid_to_account_currency"], filters=[["name", "in", payments]])
+
+ return sorted(reference_payment_list, key=lambda x: payment_by_ratio[x["name"]])
+
+ else:
+ return []
+
+def payment_entry_query(doctype, txt, searchfield, start, page_len, filters):
+ account = frappe.db.get_value("Bank Account", filters.get("bank_account"), "account")
+ if not account:
+ return
+
+ return frappe.db.sql("""
+ SELECT
+ name, party, paid_amount, received_amount, reference_no
+ FROM
+ `tabPayment Entry`
+ WHERE
+ (clearance_date is null or clearance_date='0000-00-00')
+ AND (paid_from = %(account)s or paid_to = %(account)s)
+ AND (name like %(txt)s or party like %(txt)s)
+ AND docstatus = 1
+ ORDER BY
+ if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), name
+ LIMIT
+ %(start)s, %(page_len)s""",
+ {
+ 'txt': "%%%s%%" % txt,
+ '_txt': txt.replace("%", ""),
+ 'start': start,
+ 'page_len': page_len,
+ 'account': account
+ }
+ )
+
+def journal_entry_query(doctype, txt, searchfield, start, page_len, filters):
+ account = frappe.db.get_value("Bank Account", filters.get("bank_account"), "account")
+
+ return frappe.db.sql("""
+ SELECT
+ jea.parent, je.pay_to_recd_from,
+ if(jea.debit_in_account_currency > 0, jea.debit_in_account_currency, jea.credit_in_account_currency)
+ FROM
+ `tabJournal Entry Account` as jea
+ LEFT JOIN
+ `tabJournal Entry` as je
+ ON
+ jea.parent = je.name
+ WHERE
+ (je.clearance_date is null or je.clearance_date='0000-00-00')
+ AND
+ jea.account = %(account)s
+ AND
+ (jea.parent like %(txt)s or je.pay_to_recd_from like %(txt)s)
+ AND
+ je.docstatus = 1
+ ORDER BY
+ if(locate(%(_txt)s, jea.parent), locate(%(_txt)s, jea.parent), 99999),
+ jea.parent
+ LIMIT
+ %(start)s, %(page_len)s""",
+ {
+ 'txt': "%%%s%%" % txt,
+ '_txt': txt.replace("%", ""),
+ 'start': start,
+ 'page_len': page_len,
+ 'account': account
+ }
+ )
+
+def sales_invoices_query(doctype, txt, searchfield, start, page_len, filters):
+ return frappe.db.sql("""
+ SELECT
+ sip.parent, si.customer, sip.amount, sip.mode_of_payment
+ FROM
+ `tabSales Invoice Payment` as sip
+ LEFT JOIN
+ `tabSales Invoice` as si
+ ON
+ sip.parent = si.name
+ WHERE
+ (sip.clearance_date is null or sip.clearance_date='0000-00-00')
+ AND
+ (sip.parent like %(txt)s or si.customer like %(txt)s)
+ ORDER BY
+ if(locate(%(_txt)s, sip.parent), locate(%(_txt)s, sip.parent), 99999),
+ sip.parent
+ LIMIT
+ %(start)s, %(page_len)s""",
+ {
+ 'txt': "%%%s%%" % txt,
+ '_txt': txt.replace("%", ""),
+ 'start': start,
+ 'page_len': page_len
+ }
+ )
\ No newline at end of file
diff --git a/erpnext/accounts/page/bank_reconciliation/bank_transaction_header.html b/erpnext/accounts/page/bank_reconciliation/bank_transaction_header.html
new file mode 100644
index 0000000..94f183b
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/bank_transaction_header.html
@@ -0,0 +1,21 @@
+<div class="transaction-header">
+ <div class="level list-row list-row-head text-muted small">
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {{ __("Date") }}
+ </div>
+ <div class="col-xs-11 col-sm-4 ellipsis list-subject">
+ {{ __("Description") }}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {{ __("Debit") }}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {{ __("Credit") }}
+ </div>
+ <div class="col-sm-1 ellipsis hidden-xs">
+ {{ __("Currency") }}
+ </div>
+ <div class="col-sm-1 ellipsis">
+ </div>
+ </div>
+</div>
diff --git a/erpnext/accounts/page/bank_reconciliation/bank_transaction_row.html b/erpnext/accounts/page/bank_reconciliation/bank_transaction_row.html
new file mode 100644
index 0000000..742b84c
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/bank_transaction_row.html
@@ -0,0 +1,36 @@
+<div class="list-row transaction-item">
+ <div>
+ <div class="clickable-section" data-name={{ name }}>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {%= frappe.datetime.str_to_user(date) %}
+ </div>
+ <div class="col-xs-8 col-sm-4 ellipsis list-subject">
+ {{ description }}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {%= format_currency(debit, currency) %}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {%= format_currency(credit, currency) %}
+ </div>
+ <div class="col-sm-1 ellipsis hidden-xs">
+ {{ currency }}
+ </div>
+ </div>
+ <div class="col-xs-3 col-sm-1">
+ <div class="btn-group">
+ <a class="dropdown-toggle btn btn-default btn-xs" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
+ <span>Actions </span>
+ <span class="caret"></span>
+ </a>
+ <ul class="dropdown-menu reports-dropdown" style="max-height: 300px; overflow-y: auto; right: 0px; left: auto;">
+ <li><a class="new-reconciliation" data-name={{ name }}>{{ __("Reconcile") }}</a></li>
+ <li class="divider"></li>
+ <li><a class="new-payment" data-name={{ name }}>{{ __("New Payment") }}</a></li>
+ <li><a class="new-invoice" data-name={{ name }}>{{ __("New Invoice") }}</a></li>
+ <li><a class="new-expense" data-name={{ name }}>{{ __("New Expense") }}</a></li>
+ </ul>
+ </div>
+ </div>
+ </div>
+</div>
diff --git a/erpnext/accounts/page/bank_reconciliation/linked_payment_header.html b/erpnext/accounts/page/bank_reconciliation/linked_payment_header.html
new file mode 100644
index 0000000..4542c36
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/linked_payment_header.html
@@ -0,0 +1,21 @@
+<div class="transaction-header">
+ <div class="level list-row list-row-head text-muted small">
+ <div class="col-xs-3 col-sm-2 ellipsis">
+ {{ __("Payment Name") }}
+ </div>
+ <div class="col-xs-3 col-sm-2 ellipsis">
+ {{ __("Reference Date") }}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {{ __("Amount") }}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {{ __("Party") }}
+ </div>
+ <div class="col-xs-3 col-sm-2 ellipsis">
+ {{ __("Reference Number") }}
+ </div>
+ <div class="col-xs-2 col-sm-2">
+ </div>
+ </div>
+</div>
diff --git a/erpnext/accounts/page/bank_reconciliation/linked_payment_row.html b/erpnext/accounts/page/bank_reconciliation/linked_payment_row.html
new file mode 100644
index 0000000..bdbc9fc
--- /dev/null
+++ b/erpnext/accounts/page/bank_reconciliation/linked_payment_row.html
@@ -0,0 +1,36 @@
+<div class="list-row">
+ <div>
+ <div class="col-xs-3 col-sm-2 ellipsis">
+ {{ name }}
+ </div>
+ <div class="col-xs-3 col-sm-2 ellipsis">
+ {% if (typeof reference_date !== "undefined") %}
+ {%= frappe.datetime.str_to_user(reference_date) %}
+ {% else %}
+ {% if (typeof posting_date !== "undefined") %}
+ {%= frappe.datetime.str_to_user(posting_date) %}
+ {% endif %}
+ {% endif %}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {{ format_currency(paid_amount, currency) }}
+ </div>
+ <div class="col-sm-2 ellipsis hidden-xs">
+ {% if (typeof party !== "undefined") %}
+ {{ party }}
+ {% endif %}
+ </div>
+ <div class="col-xs-3 col-sm-2 ellipsis">
+ {% if (typeof reference_no !== "undefined") %}
+ {{ reference_no }}
+ {% else %}
+ {{ "" }}
+ {% endif %}
+ </div>
+ <div class="col-xs-2 col-sm-2">
+ <div class="text-right margin-bottom">
+ <button class="btn btn-primary btn-xs reconciliation-btn" data-doctype="{{ doctype }}" data-name="{{ name }}">{{ __("Reconcile") }}</button>
+ </div>
+ </div>
+ </div>
+</div>
\ No newline at end of file
diff --git a/erpnext/accounts/page/pos/pos.js b/erpnext/accounts/page/pos/pos.js
index 4550ded..3834c96 100755
--- a/erpnext/accounts/page/pos/pos.js
+++ b/erpnext/accounts/page/pos/pos.js
@@ -1957,6 +1957,12 @@
}],
function(values){
me.item_batch_no[me.items[0].item_code] = values.batch;
+ const item = me.frm.doc.items.find(
+ ({ item_code }) => item_code === me.items[0].item_code
+ );
+ if (item) {
+ item.batch_no = values.batch;
+ }
},
__('Select Batch No'))
}