Merge pull request #31182 from ruthra-kumar/migrate_gl_to_payment_ledger_using_sql
refactor: migrating data from GL to payment ledger using raw SQL
diff --git a/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.json b/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.json
index d961076..39e9042 100644
--- a/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.json
+++ b/erpnext/accounts/doctype/payment_ledger_entry/payment_ledger_entry.json
@@ -1,7 +1,6 @@
{
"actions": [],
"allow_rename": 1,
- "autoname": "format:PLE-{YY}-{MM}-{######}",
"creation": "2022-05-09 19:35:03.334361",
"doctype": "DocType",
"editable_grid": 1,
@@ -138,11 +137,10 @@
"in_create": 1,
"index_web_pages_for_search": 1,
"links": [],
- "modified": "2022-05-19 18:04:44.609115",
+ "modified": "2022-05-30 19:04:55.532171",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Ledger Entry",
- "naming_rule": "Expression",
"owner": "Administrator",
"permissions": [
{
diff --git a/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py b/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py
index c2267aa..1e0d20d 100644
--- a/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py
+++ b/erpnext/patches/v14_0/migrate_gl_to_payment_ledger.py
@@ -1,11 +1,13 @@
import frappe
from frappe import qb
+from frappe.query_builder import Case
+from frappe.query_builder.custom import ConstantColumn
+from frappe.query_builder.functions import IfNull
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
get_dimensions,
make_dimension_in_accounting_doctypes,
)
-from erpnext.accounts.utils import create_payment_ledger_entry
def create_accounting_dimension_fields():
@@ -15,24 +17,119 @@
make_dimension_in_accounting_doctypes(dimension, ["Payment Ledger Entry"])
-def execute():
- # create accounting dimension fields in Payment Ledger
- create_accounting_dimension_fields()
+def generate_name_for_payment_ledger_entries(gl_entries):
+ for index, entry in enumerate(gl_entries, 1):
+ entry.name = index
- gl = qb.DocType("GL Entry")
- accounts = frappe.db.get_list(
- "Account", "name", filters={"account_type": ["in", ["Receivable", "Payable"]]}, as_list=True
- )
- gl_entries = []
- if accounts:
- # get all gl entries on receivable/payable accounts
+
+def get_columns():
+ columns = [
+ "name",
+ "creation",
+ "modified",
+ "modified_by",
+ "owner",
+ "docstatus",
+ "posting_date",
+ "account_type",
+ "account",
+ "party_type",
+ "party",
+ "voucher_type",
+ "voucher_no",
+ "against_voucher_type",
+ "against_voucher_no",
+ "amount",
+ "amount_in_account_currency",
+ "account_currency",
+ "company",
+ "cost_center",
+ "due_date",
+ "finance_book",
+ ]
+
+ dimensions_and_defaults = get_dimensions()
+ if dimensions_and_defaults:
+ for dimension in dimensions_and_defaults[0]:
+ columns.append(dimension.fieldname)
+
+ return columns
+
+
+def build_insert_query():
+ ple = qb.DocType("Payment Ledger Entry")
+ columns = get_columns()
+ insert_query = qb.into(ple)
+
+ # build 'insert' columns in query
+ insert_query = insert_query.columns(tuple(columns))
+
+ return insert_query
+
+
+def insert_chunk_into_payment_ledger(insert_query, gl_entries):
+ if gl_entries:
+ columns = get_columns()
+
+ # build tuple of data with same column order
+ for entry in gl_entries:
+ data = ()
+ for column in columns:
+ data += (entry[column],)
+ insert_query = insert_query.insert(data)
+ insert_query.run()
+
+
+def execute():
+ if frappe.reload_doc("accounts", "doctype", "payment_ledger_entry"):
+ # create accounting dimension fields in Payment Ledger
+ create_accounting_dimension_fields()
+
+ gl = qb.DocType("GL Entry")
+ account = qb.DocType("Account")
+
gl_entries = (
qb.from_(gl)
- .select("*")
- .where(gl.account.isin(accounts))
+ .inner_join(account)
+ .on((gl.account == account.name) & (account.account_type.isin(["Receivable", "Payable"])))
+ .select(
+ gl.star,
+ ConstantColumn(1).as_("docstatus"),
+ account.account_type.as_("account_type"),
+ IfNull(gl.against_voucher_type, gl.voucher_type).as_("against_voucher_type"),
+ IfNull(gl.against_voucher, gl.voucher_no).as_("against_voucher_no"),
+ # convert debit/credit to amount
+ Case()
+ .when(account.account_type == "Receivable", gl.debit - gl.credit)
+ .else_(gl.credit - gl.debit)
+ .as_("amount"),
+ # convert debit/credit in account currency to amount in account currency
+ Case()
+ .when(
+ account.account_type == "Receivable",
+ gl.debit_in_account_currency - gl.credit_in_account_currency,
+ )
+ .else_(gl.credit_in_account_currency - gl.debit_in_account_currency)
+ .as_("amount_in_account_currency"),
+ )
.where(gl.is_cancelled == 0)
+ .orderby(gl.creation)
.run(as_dict=True)
)
- if gl_entries:
- # create payment ledger entries for the accounts receivable/payable
- create_payment_ledger_entry(gl_entries, 0)
+
+ # primary key(name) for payment ledger records
+ generate_name_for_payment_ledger_entries(gl_entries)
+
+ # split data into chunks
+ chunk_size = 1000
+ try:
+ for i in range(0, len(gl_entries), chunk_size):
+ insert_query = build_insert_query()
+ insert_chunk_into_payment_ledger(insert_query, gl_entries[i : i + chunk_size])
+ frappe.db.commit()
+ except Exception as err:
+ frappe.db.rollback()
+ ple = qb.DocType("Payment Ledger Entry")
+ qb.from_(ple).delete().where(ple.docstatus >= 0).run()
+ frappe.db.commit()
+ raise err