feat: ledger comparison report (#36485)
* feat: Accounting Ledger comparison report
* chore: barebones methods
* chore: working state
* chore: refactor internal logic
* chore: working multi select filter on Account
* chore: working voucher no filter
* chore: remove debugging statements
* chore: report with currency symbol
* chore: working start and end date filter
* test: basic report function
* refactor(test): test all filters
diff --git a/erpnext/accounts/report/general_and_payment_ledger_comparison/__init__.py b/erpnext/accounts/report/general_and_payment_ledger_comparison/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/general_and_payment_ledger_comparison/__init__.py
diff --git a/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.js b/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.js
new file mode 100644
index 0000000..7e6b053
--- /dev/null
+++ b/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.js
@@ -0,0 +1,52 @@
+// Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+function get_filters() {
+ let filters = [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"period_start_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.add_months(frappe.datetime.get_today(), -1)
+ },
+ {
+ "fieldname":"period_end_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "reqd": 1,
+ "default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname":"account",
+ "label": __("Account"),
+ "fieldtype": "MultiSelectList",
+ "options": "Account",
+ get_data: function(txt) {
+ return frappe.db.get_link_options('Account', txt, {
+ company: frappe.query_report.get_filter_value("company"),
+ account_type: ['in', ["Receivable", "Payable"]]
+ });
+ }
+ },
+ {
+ "fieldname":"voucher_no",
+ "label": __("Voucher No"),
+ "fieldtype": "Data",
+ "width": 100,
+ },
+ ]
+ return filters;
+}
+
+frappe.query_reports["General and Payment Ledger Comparison"] = {
+ "filters": get_filters()
+};
diff --git a/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.json b/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.json
new file mode 100644
index 0000000..1d0d9d1
--- /dev/null
+++ b/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.json
@@ -0,0 +1,32 @@
+{
+ "add_total_row": 0,
+ "columns": [],
+ "creation": "2023-08-02 17:30:29.494907",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "filters": [],
+ "idx": 0,
+ "is_standard": "Yes",
+ "letterhead": null,
+ "modified": "2023-08-02 17:30:29.494907",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "General and Payment Ledger Comparison",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "GL Entry",
+ "report_name": "General and Payment Ledger Comparison",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Accounts Manager"
+ },
+ {
+ "role": "Auditor"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.py b/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.py
new file mode 100644
index 0000000..553c137
--- /dev/null
+++ b/erpnext/accounts/report/general_and_payment_ledger_comparison/general_and_payment_ledger_comparison.py
@@ -0,0 +1,221 @@
+# Copyright (c) 2023, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe import _, qb
+from frappe.query_builder import Criterion
+from frappe.query_builder.functions import Sum
+
+
+class General_Payment_Ledger_Comparison(object):
+ """
+ A Utility report to compare Voucher-wise balance between General and Payment Ledger
+ """
+
+ def __init__(self, filters=None):
+ self.filters = filters
+ self.gle = []
+ self.ple = []
+
+ def get_accounts(self):
+ receivable_accounts = [
+ x[0]
+ for x in frappe.db.get_all(
+ "Account",
+ filters={"company": self.filters.company, "account_type": "Receivable"},
+ as_list=True,
+ )
+ ]
+ payable_accounts = [
+ x[0]
+ for x in frappe.db.get_all(
+ "Account", filters={"company": self.filters.company, "account_type": "Payable"}, as_list=True
+ )
+ ]
+
+ self.account_types = frappe._dict(
+ {
+ "receivable": frappe._dict({"accounts": receivable_accounts, "gle": [], "ple": []}),
+ "payable": frappe._dict({"accounts": payable_accounts, "gle": [], "ple": []}),
+ }
+ )
+
+ def generate_filters(self):
+ if self.filters.account:
+ self.account_types.receivable.accounts = []
+ self.account_types.payable.accounts = []
+
+ for acc in frappe.db.get_all(
+ "Account", filters={"name": ["in", self.filters.account]}, fields=["name", "account_type"]
+ ):
+ if acc.account_type == "Receivable":
+ self.account_types.receivable.accounts.append(acc.name)
+ else:
+ self.account_types.payable.accounts.append(acc.name)
+
+ def get_gle(self):
+ gle = qb.DocType("GL Entry")
+
+ for acc_type, val in self.account_types.items():
+ if val.accounts:
+
+ filter_criterion = []
+ if self.filters.voucher_no:
+ filter_criterion.append((gle.voucher_no == self.filters.voucher_no))
+
+ if self.filters.period_start_date:
+ filter_criterion.append(gle.posting_date.gte(self.filters.period_start_date))
+
+ if self.filters.period_end_date:
+ filter_criterion.append(gle.posting_date.lte(self.filters.period_end_date))
+
+ if acc_type == "receivable":
+ outstanding = (Sum(gle.debit) - Sum(gle.credit)).as_("outstanding")
+ else:
+ outstanding = (Sum(gle.credit) - Sum(gle.debit)).as_("outstanding")
+
+ self.account_types[acc_type].gle = (
+ qb.from_(gle)
+ .select(
+ gle.company,
+ gle.account,
+ gle.voucher_no,
+ gle.party,
+ outstanding,
+ )
+ .where(
+ (gle.company == self.filters.company)
+ & (gle.is_cancelled == 0)
+ & (gle.account.isin(val.accounts))
+ )
+ .where(Criterion.all(filter_criterion))
+ .groupby(gle.company, gle.account, gle.voucher_no, gle.party)
+ .run()
+ )
+
+ def get_ple(self):
+ ple = qb.DocType("Payment Ledger Entry")
+
+ for acc_type, val in self.account_types.items():
+ if val.accounts:
+
+ filter_criterion = []
+ if self.filters.voucher_no:
+ filter_criterion.append((ple.voucher_no == self.filters.voucher_no))
+
+ if self.filters.period_start_date:
+ filter_criterion.append(ple.posting_date.gte(self.filters.period_start_date))
+
+ if self.filters.period_end_date:
+ filter_criterion.append(ple.posting_date.lte(self.filters.period_end_date))
+
+ self.account_types[acc_type].ple = (
+ qb.from_(ple)
+ .select(
+ ple.company, ple.account, ple.voucher_no, ple.party, Sum(ple.amount).as_("outstanding")
+ )
+ .where(
+ (ple.company == self.filters.company)
+ & (ple.delinked == 0)
+ & (ple.account.isin(val.accounts))
+ )
+ .where(Criterion.all(filter_criterion))
+ .groupby(ple.company, ple.account, ple.voucher_no, ple.party)
+ .run()
+ )
+
+ def compare(self):
+ self.gle_balances = set()
+ self.ple_balances = set()
+
+ # consolidate both receivable and payable balances in one set
+ for acc_type, val in self.account_types.items():
+ self.gle_balances = set(val.gle) | self.gle_balances
+ self.ple_balances = set(val.ple) | self.ple_balances
+
+ self.diff1 = self.gle_balances.difference(self.ple_balances)
+ self.diff2 = self.ple_balances.difference(self.gle_balances)
+ self.diff = frappe._dict({})
+
+ for x in self.diff1:
+ self.diff[(x[0], x[1], x[2], x[3])] = frappe._dict({"gl_balance": x[4]})
+
+ for x in self.diff2:
+ self.diff[(x[0], x[1], x[2], x[3])].update(frappe._dict({"pl_balance": x[4]}))
+
+ def generate_data(self):
+ self.data = []
+ for key, val in self.diff.items():
+ self.data.append(
+ frappe._dict(
+ {
+ "voucher_no": key[2],
+ "party": key[3],
+ "gl_balance": val.gl_balance,
+ "pl_balance": val.pl_balance,
+ }
+ )
+ )
+
+ def get_columns(self):
+ self.columns = []
+ options = None
+ self.columns.append(
+ dict(
+ label=_("Voucher No"),
+ fieldname="voucher_no",
+ fieldtype="Data",
+ options=options,
+ width="100",
+ )
+ )
+
+ self.columns.append(
+ dict(
+ label=_("Party"),
+ fieldname="party",
+ fieldtype="Data",
+ options=options,
+ width="100",
+ )
+ )
+
+ self.columns.append(
+ dict(
+ label=_("GL Balance"),
+ fieldname="gl_balance",
+ fieldtype="Currency",
+ options="Company:company:default_currency",
+ width="100",
+ )
+ )
+
+ self.columns.append(
+ dict(
+ label=_("Payment Ledger Balance"),
+ fieldname="pl_balance",
+ fieldtype="Currency",
+ options="Company:company:default_currency",
+ width="100",
+ )
+ )
+
+ def run(self):
+ self.get_accounts()
+ self.generate_filters()
+ self.get_gle()
+ self.get_ple()
+ self.compare()
+ self.generate_data()
+ self.get_columns()
+
+ return self.columns, self.data
+
+
+def execute(filters=None):
+ columns, data = [], []
+
+ rpt = General_Payment_Ledger_Comparison(filters)
+ columns, data = rpt.run()
+
+ return columns, data
diff --git a/erpnext/accounts/report/general_and_payment_ledger_comparison/test_general_and_payment_ledger_comparison.py b/erpnext/accounts/report/general_and_payment_ledger_comparison/test_general_and_payment_ledger_comparison.py
new file mode 100644
index 0000000..4b0e99d
--- /dev/null
+++ b/erpnext/accounts/report/general_and_payment_ledger_comparison/test_general_and_payment_ledger_comparison.py
@@ -0,0 +1,100 @@
+import unittest
+
+import frappe
+from frappe import qb
+from frappe.tests.utils import FrappeTestCase
+from frappe.utils import add_days
+
+from erpnext.accounts.doctype.sales_invoice.test_sales_invoice import create_sales_invoice
+from erpnext.accounts.report.general_and_payment_ledger_comparison.general_and_payment_ledger_comparison import (
+ execute,
+)
+from erpnext.accounts.test.accounts_mixin import AccountsTestMixin
+
+
+class TestGeneralAndPaymentLedger(FrappeTestCase, AccountsTestMixin):
+ def setUp(self):
+ self.create_company()
+ self.cleanup()
+
+ def tearDown(self):
+ frappe.db.rollback()
+
+ def cleanup(self):
+ doctypes = []
+ doctypes.append(qb.DocType("GL Entry"))
+ doctypes.append(qb.DocType("Payment Ledger Entry"))
+ doctypes.append(qb.DocType("Sales Invoice"))
+
+ for doctype in doctypes:
+ qb.from_(doctype).delete().where(doctype.company == self.company).run()
+
+ def test_01_basic_report_functionality(self):
+ sinv = create_sales_invoice(
+ company=self.company,
+ debit_to=self.debit_to,
+ expense_account=self.expense_account,
+ cost_center=self.cost_center,
+ income_account=self.income_account,
+ warehouse=self.warehouse,
+ )
+
+ # manually edit the payment ledger entry
+ ple = frappe.db.get_all(
+ "Payment Ledger Entry", filters={"voucher_no": sinv.name, "delinked": 0}
+ )[0]
+ frappe.db.set_value("Payment Ledger Entry", ple.name, "amount", sinv.grand_total - 1)
+
+ filters = frappe._dict({"company": self.company})
+ columns, data = execute(filters=filters)
+ self.assertEqual(len(data), 1)
+
+ expected = {
+ "voucher_no": sinv.name,
+ "party": sinv.customer,
+ "gl_balance": sinv.grand_total,
+ "pl_balance": sinv.grand_total - 1,
+ }
+ self.assertEqual(expected, data[0])
+
+ # account filter
+ filters = frappe._dict({"company": self.company, "account": self.debit_to})
+ columns, data = execute(filters=filters)
+ self.assertEqual(len(data), 1)
+ self.assertEqual(expected, data[0])
+
+ filters = frappe._dict({"company": self.company, "account": self.creditors})
+ columns, data = execute(filters=filters)
+ self.assertEqual([], data)
+
+ # voucher_no filter
+ filters = frappe._dict({"company": self.company, "voucher_no": sinv.name})
+ columns, data = execute(filters=filters)
+ self.assertEqual(len(data), 1)
+ self.assertEqual(expected, data[0])
+
+ filters = frappe._dict({"company": self.company, "voucher_no": sinv.name + "-1"})
+ columns, data = execute(filters=filters)
+ self.assertEqual([], data)
+
+ # date range filter
+ filters = frappe._dict(
+ {
+ "company": self.company,
+ "period_start_date": sinv.posting_date,
+ "period_end_date": sinv.posting_date,
+ }
+ )
+ columns, data = execute(filters=filters)
+ self.assertEqual(len(data), 1)
+ self.assertEqual(expected, data[0])
+
+ filters = frappe._dict(
+ {
+ "company": self.company,
+ "period_start_date": add_days(sinv.posting_date, -1),
+ "period_end_date": add_days(sinv.posting_date, -1),
+ }
+ )
+ columns, data = execute(filters=filters)
+ self.assertEqual([], data)