diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.js b/erpnext/accounts/doctype/accounts_settings/accounts_settings.js index b5fea15487f164790e43fe332bcadb91d01972a5..90a9f808286c364e0befd61d21cf67c7e0c37f0b 100644 --- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.js +++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.js @@ -26,9 +26,20 @@ frappe.ui.form.on("Accounts Settings", { add_taxes_from_taxes_and_charges_template(frm) { toggle_tax_settings(frm, "add_taxes_from_taxes_and_charges_template"); }, + add_taxes_from_item_tax_template(frm) { toggle_tax_settings(frm, "add_taxes_from_item_tax_template"); }, + + drop_ar_procedures: function (frm) { + frm.call({ + doc: frm.doc, + method: "drop_ar_sql_procedures", + callback: function (r) { + frappe.show_alert(__("Procedures dropped"), 5); + }, + }); + }, }); function toggle_tax_settings(frm, field_name) { diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json index 0639766708f3d9a19307fe3744b70986a5940b23..cc649a18153221f459c903606e85764158bd1d60 100644 --- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json +++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json @@ -93,6 +93,8 @@ "receivable_payable_remarks_length", "accounts_receivable_payable_tuning_section", "receivable_payable_fetch_method", + "column_break_ntmi", + "drop_ar_procedures", "legacy_section", "ignore_is_opening_check_for_reporting", "payment_request_settings", @@ -577,7 +579,7 @@ "fieldname": "receivable_payable_fetch_method", "fieldtype": "Select", "label": "Data Fetch Method", - "options": "Buffered Cursor\nUnBuffered Cursor" + "options": "Buffered Cursor\nUnBuffered Cursor\nRaw SQL" }, { "fieldname": "accounts_receivable_payable_tuning_section", @@ -652,6 +654,17 @@ "fieldname": "add_taxes_from_taxes_and_charges_template", "fieldtype": "Check", "label": "Automatically Add Taxes from Taxes and Charges Template" + }, + { + "fieldname": "column_break_ntmi", + "fieldtype": "Column Break" + }, + { + "depends_on": "eval:doc.receivable_payable_fetch_method == \"Raw SQL\"", + "description": "Drops existing SQL Procedures and Function setup by Accounts Receivable report", + "fieldname": "drop_ar_procedures", + "fieldtype": "Button", + "label": "Drop Procedures" } ], "grid_page_length": 50, diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.py b/erpnext/accounts/doctype/accounts_settings/accounts_settings.py index 0039eed06f76800afe9bcc575cab7898ec832c93..026346143a0030a3e4cd1012520e7a89cbd6eda6 100644 --- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.py +++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.py @@ -58,7 +58,7 @@ class AccountsSettings(Document): merge_similar_account_heads: DF.Check over_billing_allowance: DF.Currency post_change_gl_entries: DF.Check - receivable_payable_fetch_method: DF.Literal["Buffered Cursor", "UnBuffered Cursor"] + receivable_payable_fetch_method: DF.Literal["Buffered Cursor", "UnBuffered Cursor", "Raw SQL"] receivable_payable_remarks_length: DF.Int reconciliation_queue_size: DF.Int role_allowed_to_over_bill: DF.Link | None @@ -153,3 +153,11 @@ class AccountsSettings(Document): ), title=_("Auto Tax Settings Error"), ) + + @frappe.whitelist() + def drop_ar_sql_procedures(self): + from erpnext.accounts.report.accounts_receivable.accounts_receivable import InitSQLProceduresForAR + + frappe.db.sql(f"drop function if exists {InitSQLProceduresForAR.genkey_function_name}") + frappe.db.sql(f"drop procedure if exists {InitSQLProceduresForAR.init_procedure_name}") + frappe.db.sql(f"drop procedure if exists {InitSQLProceduresForAR.allocate_procedure_name}") diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 932469a9b85c953b51d9fe0bdc0ff20f8145d873..2889c8c4dff8372d8dc32334c194f1e38355779b 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -6,7 +6,7 @@ from collections import OrderedDict import frappe from frappe import _, qb, query_builder, scrub -from frappe.desk.reportview import build_match_conditions +from frappe.database.schema import get_definition from frappe.query_builder import Criterion from frappe.query_builder.functions import Date, Substring, Sum from frappe.utils import cint, cstr, flt, getdate, nowdate @@ -16,6 +16,7 @@ from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import ( get_dimension_with_children, ) from erpnext.accounts.utils import ( + build_qb_match_conditions, get_advance_payment_doctypes, get_currency_precision, get_party_types_from_account_type, @@ -129,6 +130,8 @@ class ReceivablePayableReport: self.fetch_ple_in_buffered_cursor() elif self.ple_fetch_method == "UnBuffered Cursor": self.fetch_ple_in_unbuffered_cursor() + elif self.ple_fetch_method == "Raw SQL": + self.fetch_ple_in_sql_procedures() # Build delivery note map against all sales invoices self.build_delivery_note_map() @@ -136,8 +139,7 @@ class ReceivablePayableReport: self.build_data() def fetch_ple_in_buffered_cursor(self): - query, param = self.ple_query - self.ple_entries = frappe.db.sql(query, param, as_dict=True) + self.ple_entries = self.ple_query.run(as_dict=True) for ple in self.ple_entries: self.init_voucher_balance(ple) # invoiced, paid, credit_note, outstanding @@ -150,9 +152,8 @@ class ReceivablePayableReport: def fetch_ple_in_unbuffered_cursor(self): self.ple_entries = [] - query, param = self.ple_query with frappe.db.unbuffered_cursor(): - for ple in frappe.db.sql(query, param, as_dict=True, as_iterator=True): + for ple in self.ple_query.run(as_dict=True, as_iterator=True): self.init_voucher_balance(ple) # invoiced, paid, credit_note, outstanding self.ple_entries.append(ple) @@ -320,6 +321,79 @@ class ReceivablePayableReport: row.paid -= amount row.paid_in_account_currency -= amount_in_account_currency + def fetch_ple_in_sql_procedures(self): + self.proc = InitSQLProceduresForAR() + + build_balance = f""" + begin not atomic + declare done boolean default false; + declare rec1 row type of `{self.proc._row_def_table_name}`; + declare ple cursor for {self.ple_query.get_sql()}; + declare continue handler for not found set done = true; + + open ple; + fetch ple into rec1; + while not done do + call {self.proc.init_procedure_name}(rec1); + fetch ple into rec1; + end while; + close ple; + + set done = false; + open ple; + fetch ple into rec1; + while not done do + call {self.proc.allocate_procedure_name}(rec1); + fetch ple into rec1; + end while; + close ple; + end; + """ + frappe.db.sql(build_balance) + + balances = frappe.db.sql( + f"""select + name, + voucher_type, + voucher_no, + party, + party_account `account`, + posting_date, + account_currency, + cost_center, + sum(invoiced) `invoiced`, + sum(paid) `paid`, + sum(credit_note) `credit_note`, + sum(invoiced) - sum(paid) - sum(credit_note) `outstanding`, + sum(invoiced_in_account_currency) `invoiced_in_account_currency`, + sum(paid_in_account_currency) `paid_in_account_currency`, + sum(credit_note_in_account_currency) `credit_note_in_account_currency`, + sum(invoiced_in_account_currency) - sum(paid_in_account_currency) - sum(credit_note_in_account_currency) `outstanding_in_account_currency` + from `{self.proc._voucher_balance_name}` group by name order by posting_date;""", + as_dict=True, + ) + for x in balances: + if self.filters.get("ignore_accounts"): + key = (x.voucher_type, x.voucher_no, x.party) + else: + key = (x.account, x.voucher_type, x.voucher_no, x.party) + + _d = self.build_voucher_dict(x) + for field in [ + "invoiced", + "paid", + "credit_note", + "outstanding", + "invoiced_in_account_currency", + "paid_in_account_currency", + "credit_note_in_account_currency", + "outstanding_in_account_currency", + "cost_center", + ]: + _d[field] = x.get(field) + + self.voucher_balance[key] = _d + def update_sub_total_row(self, row, party): total_row = self.total_row_map.get(party) @@ -864,18 +938,15 @@ class ReceivablePayableReport: else: query = query.select(ple.remarks) - query, param = query.walk() - - match_conditions = build_match_conditions("Payment Ledger Entry") - if match_conditions: - query += " AND " + match_conditions + if match_conditions := build_qb_match_conditions("Payment Ledger Entry"): + query = query.where(Criterion.all(match_conditions)) if self.filters.get("group_by_party"): - query += f" ORDER BY `{self.ple.party.name}`, `{self.ple.posting_date.name}`" + query = query.orderby(self.ple.party, self.ple.posting_date) else: - query += f" ORDER BY `{self.ple.posting_date.name}`, `{self.ple.party.name}`" + query = query.orderby(self.ple.posting_date, self.ple.party) - self.ple_query = (query, param) + self.ple_query = query def get_sales_invoices_or_customers_based_on_sales_person(self): if self.filters.get("sales_person"): @@ -1256,3 +1327,134 @@ def get_customer_group_with_children(customer_groups): frappe.throw(_("Customer Group: {0} does not exist").format(d)) return list(set(all_customer_groups)) + + +class InitSQLProceduresForAR: + """ + Initialize SQL Procedures, Functions and Temporary tables to build Receivable / Payable report + """ + + _varchar_type = get_definition("Data") + _currency_type = get_definition("Currency") + # Temporary Tables + _voucher_balance_name = "_ar_voucher_balance" + _voucher_balance_definition = f""" + create temporary table `{_voucher_balance_name}`( + name {_varchar_type}, + voucher_type {_varchar_type}, + voucher_no {_varchar_type}, + party {_varchar_type}, + party_account {_varchar_type}, + posting_date date, + account_currency {_varchar_type}, + cost_center {_varchar_type}, + invoiced {_currency_type}, + paid {_currency_type}, + credit_note {_currency_type}, + invoiced_in_account_currency {_currency_type}, + paid_in_account_currency {_currency_type}, + credit_note_in_account_currency {_currency_type}) engine=memory; + """ + + _row_def_table_name = "_ar_ple_row" + _row_def_table_definition = f""" + create temporary table `{_row_def_table_name}`( + name {_varchar_type}, + account {_varchar_type}, + voucher_type {_varchar_type}, + voucher_no {_varchar_type}, + against_voucher_type {_varchar_type}, + against_voucher_no {_varchar_type}, + party_type {_varchar_type}, + cost_center {_varchar_type}, + party {_varchar_type}, + posting_date date, + due_date date, + account_currency {_varchar_type}, + amount {_currency_type}, + amount_in_account_currency {_currency_type}) engine=memory; + """ + + # Function + genkey_function_name = "ar_genkey" + genkey_function_sql = f""" + create function `{genkey_function_name}`(rec row type of `{_row_def_table_name}`, allocate bool) returns char(40) + begin + if allocate then + return sha1(concat_ws(',', rec.account, rec.against_voucher_type, rec.against_voucher_no, rec.party)); + else + return sha1(concat_ws(',', rec.account, rec.voucher_type, rec.voucher_no, rec.party)); + end if; + end + """ + + # Procedures + init_procedure_name = "ar_init_tmp_table" + init_procedure_sql = f""" + create procedure ar_init_tmp_table(in ple row type of `{_row_def_table_name}`) + begin + if not exists (select name from `{_voucher_balance_name}` where name = `{genkey_function_name}`(ple, false)) + then + insert into `{_voucher_balance_name}` values (`{genkey_function_name}`(ple, false), ple.voucher_type, ple.voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, ple.cost_center, 0, 0, 0, 0, 0, 0); + end if; + end; + """ + + allocate_procedure_name = "ar_allocate_to_tmp_table" + allocate_procedure_sql = f""" + create procedure ar_allocate_to_tmp_table(in ple row type of `{_row_def_table_name}`) + begin + declare invoiced {_currency_type} default 0; + declare invoiced_in_account_currency {_currency_type} default 0; + declare paid {_currency_type} default 0; + declare paid_in_account_currency {_currency_type} default 0; + declare credit_note {_currency_type} default 0; + declare credit_note_in_account_currency {_currency_type} default 0; + + + if ple.amount > 0 then + if (ple.voucher_type in ("Journal Entry", "Payment Entry") and (ple.voucher_no != ple.against_voucher_no)) then + set paid = -1 * ple.amount; + set paid_in_account_currency = -1 * ple.amount_in_account_currency; + else + set invoiced = ple.amount; + set invoiced_in_account_currency = ple.amount_in_account_currency; + end if; + else + + if ple.voucher_type in ("Sales Invoice", "Purchase Invoice") then + if (ple.voucher_no = ple.against_voucher_no) then + set paid = -1 * ple.amount; + set paid_in_account_currency = -1 * ple.amount_in_account_currency; + else + set credit_note = -1 * ple.amount; + set credit_note_in_account_currency = -1 * ple.amount_in_account_currency; + end if; + else + set paid = -1 * ple.amount; + set paid_in_account_currency = -1 * ple.amount_in_account_currency; + end if; + + end if; + + insert into `{_voucher_balance_name}` values (`{genkey_function_name}`(ple, true), ple.against_voucher_type, ple.against_voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency,'', invoiced, paid, 0, invoiced_in_account_currency, paid_in_account_currency, 0); + end; + """ + + def __init__(self): + existing_procedures = frappe.db.get_routines() + + if self.genkey_function_name not in existing_procedures: + frappe.db.sql(self.genkey_function_sql) + + if self.init_procedure_name not in existing_procedures: + frappe.db.sql(self.init_procedure_sql) + + if self.allocate_procedure_name not in existing_procedures: + frappe.db.sql(self.allocate_procedure_sql) + + frappe.db.sql(f"drop table if exists `{self._voucher_balance_name}`") + frappe.db.sql(self._voucher_balance_definition) + + frappe.db.sql(f"drop table if exists `{self._row_def_table_name}`") + frappe.db.sql(self._row_def_table_definition) diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py index c04e6686042b533c01e3c171dc1dc4bf39c697b4..f7fea5648467a526ad9ec9c8865569dd228c83d0 100644 --- a/erpnext/accounts/utils.py +++ b/erpnext/accounts/utils.py @@ -8,6 +8,7 @@ from typing import TYPE_CHECKING, Optional import frappe import frappe.defaults from frappe import _, qb, throw +from frappe.desk.reportview import build_match_conditions from frappe.model.meta import get_field_precision from frappe.query_builder import AliasedQuery, Case, Criterion, Table from frappe.query_builder.functions import Count, Max, Round, Sum @@ -2403,3 +2404,19 @@ def sync_auto_reconcile_config(auto_reconciliation_job_trigger: int = 15): "frequency": "Cron", } ).save() + + +def build_qb_match_conditions(doctype, user=None) -> list: + match_filters = build_match_conditions(doctype, user, False) + criterion = [] + if match_filters: + from frappe import qb + + _dt = qb.DocType(doctype) + + for filter in match_filters: + for d, names in filter.items(): + fieldname = d.lower().replace(" ", "_") + criterion.append(_dt[fieldname].isin(names)) + + return criterion