From 71858a838045006c63a336db60d2acb3056e77fb Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Fri, 2 May 2025 13:46:46 +0530 Subject: [PATCH 01/12] refactor: using sql procedures for AR report - dynamic filters are passed --- .../accounts_receivable.py | 175 ++++++++++++++++++ 1 file changed, 175 insertions(+) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 932469a9b85..fb4adba2ef2 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -130,6 +130,8 @@ class ReceivablePayableReport: elif self.ple_fetch_method == "UnBuffered Cursor": self.fetch_ple_in_unbuffered_cursor() + self.init_and_run_sql_procedures() + # Build delivery note map against all sales invoices self.build_delivery_note_map() @@ -320,6 +322,179 @@ class ReceivablePayableReport: row.paid -= amount row.paid_in_account_currency -= amount_in_account_currency + def init_and_run_sql_procedures(self): + # create in-memory temporary table for performance + frappe.db.sql("drop table if exists voucher_balance;") + # Memory storage engine doesn't support remarks - BLOB, TEXT types. + # Alternative? + frappe.db.sql( + """ + create temporary table voucher_balance( + name varchar(224), + voucher_type varchar(140), + voucher_no varchar(140), + party varchar(140), + party_account varchar(140), + posting_date date, + account_currency varchar(140), + invoiced decimal(21,9), + paid decimal(21,9), + credit_note decimal(21,9), + outstanding decimal(21,9), + invoiced_in_account_currency decimal(21,9), + paid_in_account_currency decimal(21,9), + credit_note_in_account_currency decimal(21,9), + outstanding_in_account_currency decimal(21,9)) engine=memory; + """ + ) + + # Only used for passing definitions to 'row type of' + frappe.db.sql("drop table if exists ple_row;") + frappe.db.sql( + """ + create temporary table ple_row( + name varchar(224), + account varchar(140), + voucher_type varchar(140), + voucher_no varchar(140), + against_voucher_type varchar(140), + against_voucher_no varchar(140), + party_type varchar(140), + cost_center varchar(140), + party varchar(140), + posting_date date, + due_date date, + account_currency varchar(140), + amount decimal(21,9), + amount_in_account_currency decimal(21,9)) engine=memory; + """ + ) + + # Generate hash from key + frappe.db.sql("drop function if exists genkey;") + frappe.db.sql( + """ + create function genkey(rec row type of ple_row, allocate bool) returns char(224) + begin + if allocate then + return sha2(concat_ws(',', rec.account, rec.against_voucher_type, rec.against_voucher_no, rec.party), 224); + else + return sha2(concat_ws(',', rec.account, rec.voucher_type, rec.voucher_no, rec.party), 224); + end if; + end + """ + ) + + # Init + frappe.db.sql("drop procedure if exists init;") + init_procedure = """ + create procedure init(in ple row type of `ple_row`) + begin + if not exists (select name from `voucher_balance` where name = genkey(ple, false)) + then + insert into `voucher_balance` values (genkey(ple, false), ple.voucher_type, ple.voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, 0, 0, 0, 0, 0, 0, 0, 0); + end if; + end; + """ + frappe.db.sql(init_procedure) + + # Allocate + frappe.db.sql("drop procedure if exists allocate;") + allocate_procedure = """ + create procedure allocate(in ple row type of `ple_row`) + begin + declare invoiced decimal(21,9) default 0; + declare invoiced_in_account_currency decimal(21,9) default 0; + declare paid decimal(21,9) default 0; + declare paid_in_account_currency decimal(21,9) default 0; + declare credit_note decimal(21,9) default 0; + declare credit_note_in_account_currency decimal(21,9) 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` values (genkey(ple, true), ple.against_voucher_type, ple.against_voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, invoiced, paid, 0, 0, invoiced_in_account_currency, paid_in_account_currency, 0, 0); + end; + """ + frappe.db.sql(allocate_procedure) + + frappe.db.sql("drop procedure if exists build;") + build_balance = f""" + begin not atomic + declare done boolean default false; + declare rec1 row type of ple_row; + 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 init(rec1); + fetch ple into rec1; + end while; + close ple; + + set done = false; + open ple; + fetch ple into rec1; + while not done do + call allocate(rec1); + fetch ple into rec1; + end while; + close ple; + end; + """ + frappe.db.sql(build_balance) + + res = frappe.db.sql( + """select + name, + voucher_type, + voucher_no, + party, + party_account, + posting_date, + account_currency, + sum(invoiced), + sum(paid), + sum(credit_note), + sum(invoiced) - sum(paid) - sum(credit_note), + sum(invoiced_in_account_currency), + sum(paid_in_account_currency), + sum(credit_note_in_account_currency), + sum(invoiced_in_account_currency) - sum(paid_in_account_currency) - sum(credit_note_in_account_currency) + from `voucher_balance` group by name;""" + ) + self.printv(res) + + def printv(self, res): + for x in res: + # if x[3] == "ACC-SINV-2025-00035": + print(x) + def update_sub_total_row(self, row, party): total_row = self.total_row_map.get(party) -- GitLab From 95b609320eb56d5dce44d54ccc406e245ffedb84 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Wed, 4 Jun 2025 11:56:09 +0530 Subject: [PATCH 02/12] refactor: better readability --- .../accounts_receivable.py | 264 ++++++++++-------- 1 file changed, 140 insertions(+), 124 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index fb4adba2ef2..85e27475f4c 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -323,136 +323,19 @@ class ReceivablePayableReport: row.paid_in_account_currency -= amount_in_account_currency def init_and_run_sql_procedures(self): - # create in-memory temporary table for performance - frappe.db.sql("drop table if exists voucher_balance;") - # Memory storage engine doesn't support remarks - BLOB, TEXT types. - # Alternative? - frappe.db.sql( - """ - create temporary table voucher_balance( - name varchar(224), - voucher_type varchar(140), - voucher_no varchar(140), - party varchar(140), - party_account varchar(140), - posting_date date, - account_currency varchar(140), - invoiced decimal(21,9), - paid decimal(21,9), - credit_note decimal(21,9), - outstanding decimal(21,9), - invoiced_in_account_currency decimal(21,9), - paid_in_account_currency decimal(21,9), - credit_note_in_account_currency decimal(21,9), - outstanding_in_account_currency decimal(21,9)) engine=memory; - """ - ) - - # Only used for passing definitions to 'row type of' - frappe.db.sql("drop table if exists ple_row;") - frappe.db.sql( - """ - create temporary table ple_row( - name varchar(224), - account varchar(140), - voucher_type varchar(140), - voucher_no varchar(140), - against_voucher_type varchar(140), - against_voucher_no varchar(140), - party_type varchar(140), - cost_center varchar(140), - party varchar(140), - posting_date date, - due_date date, - account_currency varchar(140), - amount decimal(21,9), - amount_in_account_currency decimal(21,9)) engine=memory; - """ - ) - - # Generate hash from key - frappe.db.sql("drop function if exists genkey;") - frappe.db.sql( - """ - create function genkey(rec row type of ple_row, allocate bool) returns char(224) - begin - if allocate then - return sha2(concat_ws(',', rec.account, rec.against_voucher_type, rec.against_voucher_no, rec.party), 224); - else - return sha2(concat_ws(',', rec.account, rec.voucher_type, rec.voucher_no, rec.party), 224); - end if; - end - """ - ) - - # Init - frappe.db.sql("drop procedure if exists init;") - init_procedure = """ - create procedure init(in ple row type of `ple_row`) - begin - if not exists (select name from `voucher_balance` where name = genkey(ple, false)) - then - insert into `voucher_balance` values (genkey(ple, false), ple.voucher_type, ple.voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, 0, 0, 0, 0, 0, 0, 0, 0); - end if; - end; - """ - frappe.db.sql(init_procedure) - - # Allocate - frappe.db.sql("drop procedure if exists allocate;") - allocate_procedure = """ - create procedure allocate(in ple row type of `ple_row`) - begin - declare invoiced decimal(21,9) default 0; - declare invoiced_in_account_currency decimal(21,9) default 0; - declare paid decimal(21,9) default 0; - declare paid_in_account_currency decimal(21,9) default 0; - declare credit_note decimal(21,9) default 0; - declare credit_note_in_account_currency decimal(21,9) 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; + self.proc = InitSQLProceduresForAR() - insert into `voucher_balance` values (genkey(ple, true), ple.against_voucher_type, ple.against_voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, invoiced, paid, 0, 0, invoiced_in_account_currency, paid_in_account_currency, 0, 0); - end; - """ - frappe.db.sql(allocate_procedure) - - frappe.db.sql("drop procedure if exists build;") build_balance = f""" begin not atomic declare done boolean default false; - declare rec1 row type of ple_row; + 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 init(rec1); + call {self.proc.init_procedure_name}(rec1); fetch ple into rec1; end while; close ple; @@ -461,7 +344,7 @@ class ReceivablePayableReport: open ple; fetch ple into rec1; while not done do - call allocate(rec1); + call {self.proc.allocate_procedure_name}(rec1); fetch ple into rec1; end while; close ple; @@ -470,7 +353,7 @@ class ReceivablePayableReport: frappe.db.sql(build_balance) res = frappe.db.sql( - """select + f"""select name, voucher_type, voucher_no, @@ -486,13 +369,12 @@ class ReceivablePayableReport: sum(paid_in_account_currency), sum(credit_note_in_account_currency), sum(invoiced_in_account_currency) - sum(paid_in_account_currency) - sum(credit_note_in_account_currency) - from `voucher_balance` group by name;""" + from `{self.proc._voucher_balance_name}` group by name;""" ) self.printv(res) def printv(self, res): for x in res: - # if x[3] == "ACC-SINV-2025-00035": print(x) def update_sub_total_row(self, row, party): @@ -1431,3 +1313,137 @@ 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 + """ + + # Temporary Tables + _voucher_balance_name = "_voucher_balance" + _voucher_balance_definition = f""" + create temporary table `{_voucher_balance_name}`( + name varchar(224), + voucher_type varchar(140), + voucher_no varchar(140), + party varchar(140), + party_account varchar(140), + posting_date date, + account_currency varchar(140), + invoiced decimal(21,9), + paid decimal(21,9), + credit_note decimal(21,9), + outstanding decimal(21,9), + invoiced_in_account_currency decimal(21,9), + paid_in_account_currency decimal(21,9), + credit_note_in_account_currency decimal(21,9), + outstanding_in_account_currency decimal(21,9)) engine=memory; + """ + _row_def_table_name = "_ple_row" + _row_def_table_definition = f""" + create temporary table `{_row_def_table_name}`( + name varchar(224), + account varchar(140), + voucher_type varchar(140), + voucher_no varchar(140), + against_voucher_type varchar(140), + against_voucher_no varchar(140), + party_type varchar(140), + cost_center varchar(140), + party varchar(140), + posting_date date, + due_date date, + account_currency varchar(140), + amount decimal(21,9), + amount_in_account_currency decimal(21,9)) engine=memory; + """ + + # Function + genkey_function_name = "genkey" + genkey_function_sql = f""" + create function `{genkey_function_name}`(rec row type of `{_row_def_table_name}`, allocate bool) returns char(224) + begin + if allocate then + return sha2(concat_ws(',', rec.account, rec.against_voucher_type, rec.against_voucher_no, rec.party), 224); + else + return sha2(concat_ws(',', rec.account, rec.voucher_type, rec.voucher_no, rec.party), 224); + 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(ple, false)) + then + insert into `{_voucher_balance_name}` values (genkey(ple, false), ple.voucher_type, ple.voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, 0, 0, 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 decimal(21,9) default 0; + declare invoiced_in_account_currency decimal(21,9) default 0; + declare paid decimal(21,9) default 0; + declare paid_in_account_currency decimal(21,9) default 0; + declare credit_note decimal(21,9) default 0; + declare credit_note_in_account_currency decimal(21,9) 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, 0, invoiced_in_account_currency, paid_in_account_currency, 0, 0); + end; + """ + + def __init__(self): + existing_procedures = frappe.db.sql( + f"select routine_name from information_schema.routines where routine_type in ('FUNCTION','PROCEDURE') and routine_schema='{frappe.conf.db_name}';" + ) + if existing_procedures: + # normalize + existing_procedures = [x[0] for x in existing_procedures] + + 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) -- GitLab From 68b233361a74d05e0ce18ac7a9c35ce035c5f581 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Wed, 4 Jun 2025 14:12:18 +0530 Subject: [PATCH 03/12] refactor: introduce sql option for data fetch --- .../accounts/doctype/accounts_settings/accounts_settings.json | 2 +- erpnext/accounts/doctype/accounts_settings/accounts_settings.py | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json index 0639766708f..173cbe08de4 100644 --- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.json +++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.json @@ -577,7 +577,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", diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.py b/erpnext/accounts/doctype/accounts_settings/accounts_settings.py index 0039eed06f7..e9deb31cfbe 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 -- GitLab From d536718a93c762847ce1609089d034a3bbb8aefd Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Wed, 4 Jun 2025 14:26:15 +0530 Subject: [PATCH 04/12] refactor: call procedures based on config --- .../accounts_receivable.py | 48 ++++++++++++------- 1 file changed, 32 insertions(+), 16 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 85e27475f4c..88b6cdfdcc1 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -129,8 +129,8 @@ class ReceivablePayableReport: self.fetch_ple_in_buffered_cursor() elif self.ple_fetch_method == "UnBuffered Cursor": self.fetch_ple_in_unbuffered_cursor() - - self.init_and_run_sql_procedures() + elif self.ple_fetch_method == "Raw SQL": + self.init_and_run_sql_procedures() # Build delivery note map against all sales invoices self.build_delivery_note_map() @@ -358,24 +358,40 @@ class ReceivablePayableReport: voucher_type, voucher_no, party, - party_account, + party_account `account`, posting_date, account_currency, - sum(invoiced), - sum(paid), - sum(credit_note), - sum(invoiced) - sum(paid) - sum(credit_note), - sum(invoiced_in_account_currency), - sum(paid_in_account_currency), - sum(credit_note_in_account_currency), - sum(invoiced_in_account_currency) - sum(paid_in_account_currency) - sum(credit_note_in_account_currency) - from `{self.proc._voucher_balance_name}` group by name;""" + 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;""", + as_dict=True, ) - self.printv(res) - - def printv(self, res): for x in res: - print(x) + 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", + ]: + _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) -- GitLab From 2d066acadd581de7618aec67262f0ae6e2616edf Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Wed, 4 Jun 2025 14:30:31 +0530 Subject: [PATCH 05/12] refactor: order by posting date --- .../accounts/report/accounts_receivable/accounts_receivable.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 88b6cdfdcc1..589da2e6b96 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -369,7 +369,7 @@ class ReceivablePayableReport: 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;""", + from `{self.proc._voucher_balance_name}` group by name order by posting_date;""", as_dict=True, ) for x in res: -- GitLab From fdc0d067da2f7e93eaf02d6fbae9290098c746fc Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Wed, 4 Jun 2025 14:47:01 +0530 Subject: [PATCH 06/12] refactor: utility to drop existing procedures and include cost center --- .../accounts_settings/accounts_settings.js | 11 +++++++++ .../accounts_settings/accounts_settings.json | 13 +++++++++++ .../accounts_settings/accounts_settings.py | 8 +++++++ .../accounts_receivable.py | 23 +++++++++++-------- 4 files changed, 46 insertions(+), 9 deletions(-) diff --git a/erpnext/accounts/doctype/accounts_settings/accounts_settings.js b/erpnext/accounts/doctype/accounts_settings/accounts_settings.js index b5fea15487f..90a9f808286 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 173cbe08de4..cc649a18153 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", @@ -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 e9deb31cfbe..026346143a0 100644 --- a/erpnext/accounts/doctype/accounts_settings/accounts_settings.py +++ b/erpnext/accounts/doctype/accounts_settings/accounts_settings.py @@ -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 589da2e6b96..38126b65efe 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -361,6 +361,7 @@ class ReceivablePayableReport: party_account `account`, posting_date, account_currency, + cost_center, sum(invoiced) `invoiced`, sum(paid) `paid`, sum(credit_note) `credit_note`, @@ -388,6 +389,7 @@ class ReceivablePayableReport: "paid_in_account_currency", "credit_note_in_account_currency", "outstanding_in_account_currency", + "cost_center", ]: _d[field] = x.get(field) @@ -1347,14 +1349,13 @@ class InitSQLProceduresForAR: party_account varchar(140), posting_date date, account_currency varchar(140), + cost_center varchar(140), invoiced decimal(21,9), paid decimal(21,9), credit_note decimal(21,9), - outstanding decimal(21,9), invoiced_in_account_currency decimal(21,9), paid_in_account_currency decimal(21,9), - credit_note_in_account_currency decimal(21,9), - outstanding_in_account_currency decimal(21,9)) engine=memory; + credit_note_in_account_currency decimal(21,9)) engine=memory; """ _row_def_table_name = "_ple_row" _row_def_table_definition = f""" @@ -1395,7 +1396,7 @@ class InitSQLProceduresForAR: begin if not exists (select name from `{_voucher_balance_name}` where name = genkey(ple, false)) then - insert into `{_voucher_balance_name}` values (genkey(ple, false), ple.voucher_type, ple.voucher_no, ple.party, ple.account, ple.posting_date, ple.account_currency, 0, 0, 0, 0, 0, 0, 0, 0); + insert into `{_voucher_balance_name}` values (genkey(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; """ @@ -1437,17 +1438,21 @@ class InitSQLProceduresForAR: 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, 0, invoiced_in_account_currency, paid_in_account_currency, 0, 0); + 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.sql( + def get_existing_procedures(self): + procedures = frappe.db.sql( f"select routine_name from information_schema.routines where routine_type in ('FUNCTION','PROCEDURE') and routine_schema='{frappe.conf.db_name}';" ) - if existing_procedures: + if procedures: # normalize - existing_procedures = [x[0] for x in existing_procedures] + procedures = [x[0] for x in procedures] + return procedures + + def __init__(self): + existing_procedures = self.get_existing_procedures() if self.genkey_function_name not in existing_procedures: frappe.db.sql(self.genkey_function_sql) -- GitLab From fc462eb34d8c1a2d566dc0e87b4433e86e446066 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Thu, 5 Jun 2025 11:24:28 +0530 Subject: [PATCH 07/12] refactor: prefix-ed names for easy distinction --- .../report/accounts_receivable/accounts_receivable.py | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 38126b65efe..7549670c217 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -1339,7 +1339,7 @@ class InitSQLProceduresForAR: """ # Temporary Tables - _voucher_balance_name = "_voucher_balance" + _voucher_balance_name = "_ar_voucher_balance" _voucher_balance_definition = f""" create temporary table `{_voucher_balance_name}`( name varchar(224), @@ -1357,7 +1357,7 @@ class InitSQLProceduresForAR: paid_in_account_currency decimal(21,9), credit_note_in_account_currency decimal(21,9)) engine=memory; """ - _row_def_table_name = "_ple_row" + _row_def_table_name = "_ar_ple_row" _row_def_table_definition = f""" create temporary table `{_row_def_table_name}`( name varchar(224), @@ -1377,7 +1377,7 @@ class InitSQLProceduresForAR: """ # Function - genkey_function_name = "genkey" + 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(224) begin @@ -1394,9 +1394,9 @@ class InitSQLProceduresForAR: 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(ple, false)) + if not exists (select name from `{_voucher_balance_name}` where name = `{genkey_function_name}`(ple, false)) then - insert into `{_voucher_balance_name}` values (genkey(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); + 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; """ -- GitLab From 372167762cd0e400ed335674247b686dab540309 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Thu, 5 Jun 2025 11:29:48 +0530 Subject: [PATCH 08/12] refactor: better variable name --- .../report/accounts_receivable/accounts_receivable.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 7549670c217..3545051b57a 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -352,7 +352,7 @@ class ReceivablePayableReport: """ frappe.db.sql(build_balance) - res = frappe.db.sql( + balances = frappe.db.sql( f"""select name, voucher_type, @@ -373,7 +373,7 @@ class ReceivablePayableReport: from `{self.proc._voucher_balance_name}` group by name order by posting_date;""", as_dict=True, ) - for x in res: + for x in balances: if self.filters.get("ignore_accounts"): key = (x.voucher_type, x.voucher_no, x.party) else: -- GitLab From f0af12ebf4dff4182a87d252cc778ecdd304f123 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Tue, 10 Jun 2025 14:49:03 +0530 Subject: [PATCH 09/12] refactor: dynamic DB field types --- .../accounts_receivable.py | 72 ++++++++++--------- 1 file changed, 38 insertions(+), 34 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 3545051b57a..6ffd0633a32 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -6,6 +6,7 @@ from collections import OrderedDict import frappe from frappe import _, qb, query_builder, scrub +from frappe.database.schema import get_definition from frappe.desk.reportview import build_match_conditions from frappe.query_builder import Criterion from frappe.query_builder.functions import Date, Substring, Sum @@ -1338,53 +1339,56 @@ 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(224), - voucher_type varchar(140), - voucher_no varchar(140), - party varchar(140), - party_account varchar(140), + name {_varchar_type}, + voucher_type {_varchar_type}, + voucher_no {_varchar_type}, + party {_varchar_type}, + party_account {_varchar_type}, posting_date date, - account_currency varchar(140), - cost_center varchar(140), - invoiced decimal(21,9), - paid decimal(21,9), - credit_note decimal(21,9), - invoiced_in_account_currency decimal(21,9), - paid_in_account_currency decimal(21,9), - credit_note_in_account_currency decimal(21,9)) engine=memory; + 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(224), - account varchar(140), - voucher_type varchar(140), - voucher_no varchar(140), - against_voucher_type varchar(140), - against_voucher_no varchar(140), - party_type varchar(140), - cost_center varchar(140), - party varchar(140), + 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(140), - amount decimal(21,9), - amount_in_account_currency decimal(21,9)) engine=memory; + 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(224) + create function `{genkey_function_name}`(rec row type of `{_row_def_table_name}`, allocate bool) returns char(40) begin if allocate then - return sha2(concat_ws(',', rec.account, rec.against_voucher_type, rec.against_voucher_no, rec.party), 224); + return sha1(concat_ws(',', rec.account, rec.against_voucher_type, rec.against_voucher_no, rec.party)); else - return sha2(concat_ws(',', rec.account, rec.voucher_type, rec.voucher_no, rec.party), 224); + return sha1(concat_ws(',', rec.account, rec.voucher_type, rec.voucher_no, rec.party)); end if; end """ @@ -1405,12 +1409,12 @@ class InitSQLProceduresForAR: allocate_procedure_sql = f""" create procedure ar_allocate_to_tmp_table(in ple row type of `{_row_def_table_name}`) begin - declare invoiced decimal(21,9) default 0; - declare invoiced_in_account_currency decimal(21,9) default 0; - declare paid decimal(21,9) default 0; - declare paid_in_account_currency decimal(21,9) default 0; - declare credit_note decimal(21,9) default 0; - declare credit_note_in_account_currency decimal(21,9) default 0; + 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 -- GitLab From 64327396e4dec1e087f5f895672bdd6ee10fbe9c Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Thu, 3 Jul 2025 15:18:53 +0530 Subject: [PATCH 10/12] chore: drop unused utility method --- .../report/accounts_receivable/accounts_receivable.py | 11 +---------- 1 file changed, 1 insertion(+), 10 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index 6ffd0633a32..f4b0f6040c5 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -1446,17 +1446,8 @@ class InitSQLProceduresForAR: end; """ - def get_existing_procedures(self): - procedures = frappe.db.sql( - f"select routine_name from information_schema.routines where routine_type in ('FUNCTION','PROCEDURE') and routine_schema='{frappe.conf.db_name}';" - ) - if procedures: - # normalize - procedures = [x[0] for x in procedures] - return procedures - def __init__(self): - existing_procedures = self.get_existing_procedures() + existing_procedures = frappe.db.get_routines() if self.genkey_function_name not in existing_procedures: frappe.db.sql(self.genkey_function_sql) -- GitLab From 183f7343da037cac858f075b5241c662008dc328 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Thu, 3 Jul 2025 15:41:14 +0530 Subject: [PATCH 11/12] refactor: build and pass match conditions as qb criterion --- .../accounts_receivable.py | 21 +++++++------------ erpnext/accounts/utils.py | 17 +++++++++++++++ 2 files changed, 25 insertions(+), 13 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index f4b0f6040c5..e5feda3f41f 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -7,7 +7,6 @@ from collections import OrderedDict import frappe from frappe import _, qb, query_builder, scrub from frappe.database.schema import get_definition -from frappe.desk.reportview import build_match_conditions from frappe.query_builder import Criterion from frappe.query_builder.functions import Date, Substring, Sum from frappe.utils import cint, cstr, flt, getdate, nowdate @@ -17,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, @@ -139,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 @@ -153,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) @@ -940,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"): diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py index c04e6686042..f7fea564846 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 -- GitLab From 6722fa79ef913bd201427b46f5b87951417ace24 Mon Sep 17 00:00:00 2001 From: ruthra kumar Date: Wed, 9 Jul 2025 10:45:58 +0530 Subject: [PATCH 12/12] chore: rename method --- .../report/accounts_receivable/accounts_receivable.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py index e5feda3f41f..2889c8c4dff 100644 --- a/erpnext/accounts/report/accounts_receivable/accounts_receivable.py +++ b/erpnext/accounts/report/accounts_receivable/accounts_receivable.py @@ -131,7 +131,7 @@ class ReceivablePayableReport: elif self.ple_fetch_method == "UnBuffered Cursor": self.fetch_ple_in_unbuffered_cursor() elif self.ple_fetch_method == "Raw SQL": - self.init_and_run_sql_procedures() + self.fetch_ple_in_sql_procedures() # Build delivery note map against all sales invoices self.build_delivery_note_map() @@ -321,7 +321,7 @@ class ReceivablePayableReport: row.paid -= amount row.paid_in_account_currency -= amount_in_account_currency - def init_and_run_sql_procedures(self): + def fetch_ple_in_sql_procedures(self): self.proc = InitSQLProceduresForAR() build_balance = f""" -- GitLab