diff --git a/erpnext/accounts/doctype/ledger_health/__init__.py b/erpnext/accounts/doctype/ledger_health/__init__.py new file mode 100644 index 0000000000000000000000000000000000000000..e69de29bb2d1d6434b8b29ae775ad8c2e48c5391 diff --git a/erpnext/accounts/doctype/ledger_health/ledger_health.js b/erpnext/accounts/doctype/ledger_health/ledger_health.js new file mode 100644 index 0000000000000000000000000000000000000000..e207daef5115f6df4f406f12f9b5dd3a0ab96d6e --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health/ledger_health.js @@ -0,0 +1,8 @@ +// Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors +// For license information, please see license.txt + +// frappe.ui.form.on("Ledger Health", { +// refresh(frm) { + +// }, +// }); diff --git a/erpnext/accounts/doctype/ledger_health/ledger_health.json b/erpnext/accounts/doctype/ledger_health/ledger_health.json new file mode 100644 index 0000000000000000000000000000000000000000..8f60e3abd187d51d26861c331d734626809de817 --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health/ledger_health.json @@ -0,0 +1,70 @@ +{ + "actions": [], + "autoname": "autoincrement", + "creation": "2024-03-26 17:01:47.443986", + "doctype": "DocType", + "engine": "InnoDB", + "field_order": [ + "voucher_type", + "voucher_no", + "checked_on", + "debit_credit_mismatch", + "general_and_payment_ledger_mismatch" + ], + "fields": [ + { + "fieldname": "voucher_type", + "fieldtype": "Data", + "label": "Voucher Type" + }, + { + "fieldname": "voucher_no", + "fieldtype": "Data", + "label": "Voucher No" + }, + { + "default": "0", + "fieldname": "debit_credit_mismatch", + "fieldtype": "Check", + "label": "Debit-Credit mismatch" + }, + { + "fieldname": "checked_on", + "fieldtype": "Datetime", + "label": "Checked On" + }, + { + "default": "0", + "fieldname": "general_and_payment_ledger_mismatch", + "fieldtype": "Check", + "label": "General and Payment Ledger mismatch" + } + ], + "in_create": 1, + "index_web_pages_for_search": 1, + "links": [], + "modified": "2024-04-09 11:16:07.044484", + "modified_by": "Administrator", + "module": "Accounts", + "name": "Ledger Health", + "naming_rule": "Autoincrement", + "owner": "Administrator", + "permissions": [ + { + "create": 1, + "delete": 1, + "email": 1, + "export": 1, + "print": 1, + "read": 1, + "report": 1, + "role": "System Manager", + "share": 1, + "write": 1 + } + ], + "read_only": 1, + "sort_field": "modified", + "sort_order": "DESC", + "states": [] +} diff --git a/erpnext/accounts/doctype/ledger_health/ledger_health.py b/erpnext/accounts/doctype/ledger_health/ledger_health.py new file mode 100644 index 0000000000000000000000000000000000000000..590ff80cc110a45de1a9cd026d393a7b9a35daad --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health/ledger_health.py @@ -0,0 +1,25 @@ +# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors +# For license information, please see license.txt + +# import frappe +from frappe.model.document import Document + + +class LedgerHealth(Document): + # begin: auto-generated types + # This code is auto-generated. Do not modify anything in this block. + + from typing import TYPE_CHECKING + + if TYPE_CHECKING: + from frappe.types import DF + + checked_on: DF.Datetime | None + debit_credit_mismatch: DF.Check + general_and_payment_ledger_mismatch: DF.Check + name: DF.Int | None + voucher_no: DF.Data | None + voucher_type: DF.Data | None + # end: auto-generated types + + pass diff --git a/erpnext/accounts/doctype/ledger_health/test_ledger_health.py b/erpnext/accounts/doctype/ledger_health/test_ledger_health.py new file mode 100644 index 0000000000000000000000000000000000000000..d35b39d9ea128f052fd4713f73b38b32b5bb3dd5 --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health/test_ledger_health.py @@ -0,0 +1,109 @@ +# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and Contributors +# See license.txt + +import frappe +from frappe import qb +from frappe.tests.utils import FrappeTestCase +from frappe.utils import nowdate + +from erpnext.accounts.test.accounts_mixin import AccountsTestMixin +from erpnext.accounts.utils import run_ledger_health_checks + + +class TestLedgerHealth(AccountsTestMixin, FrappeTestCase): + def setUp(self): + self.create_company() + self.create_customer() + self.configure_monitoring_tool() + self.clear_old_entries() + + def tearDown(self): + frappe.db.rollback() + + def configure_monitoring_tool(self): + monitor_settings = frappe.get_doc("Ledger Health Monitor") + monitor_settings.enable_health_monitor = True + monitor_settings.enable_for_last_x_days = 60 + monitor_settings.debit_credit_mismatch = True + monitor_settings.general_and_payment_ledger_mismatch = True + exists = [x for x in monitor_settings.companies if x.company == self.company] + if not exists: + monitor_settings.append("companies", {"company": self.company}) + monitor_settings.save() + + def clear_old_entries(self): + super().clear_old_entries() + lh = qb.DocType("Ledger Health") + qb.from_(lh).delete().run() + + def create_journal(self): + je = frappe.new_doc("Journal Entry") + je.company = self.company + je.voucher_type = "Journal Entry" + je.posting_date = nowdate() + je.append( + "accounts", + { + "account": self.debit_to, + "party_type": "Customer", + "party": self.customer, + "debit_in_account_currency": 10000, + }, + ) + je.append("accounts", {"account": self.income_account, "credit_in_account_currency": 10000}) + je.save().submit() + self.je = je + + def test_debit_credit_mismatch(self): + self.create_journal() + + # manually cause debit-credit mismatch + gle = frappe.db.get_all( + "GL Entry", filters={"voucher_no": self.je.name, "account": self.income_account} + )[0] + frappe.db.set_value("GL Entry", gle.name, "credit", 8000) + + run_ledger_health_checks() + expected = { + "voucher_type": self.je.doctype, + "voucher_no": self.je.name, + "debit_credit_mismatch": True, + "general_and_payment_ledger_mismatch": False, + } + actual = frappe.db.get_all( + "Ledger Health", + fields=[ + "voucher_type", + "voucher_no", + "debit_credit_mismatch", + "general_and_payment_ledger_mismatch", + ], + ) + self.assertEqual(len(actual), 1) + self.assertEqual(expected, actual[0]) + + def test_gl_and_pl_mismatch(self): + self.create_journal() + + # manually cause GL and PL discrepancy + ple = frappe.db.get_all("Payment Ledger Entry", filters={"voucher_no": self.je.name})[0] + frappe.db.set_value("Payment Ledger Entry", ple.name, "amount", 11000) + + run_ledger_health_checks() + expected = { + "voucher_type": self.je.doctype, + "voucher_no": self.je.name, + "debit_credit_mismatch": False, + "general_and_payment_ledger_mismatch": True, + } + actual = frappe.db.get_all( + "Ledger Health", + fields=[ + "voucher_type", + "voucher_no", + "debit_credit_mismatch", + "general_and_payment_ledger_mismatch", + ], + ) + self.assertEqual(len(actual), 1) + self.assertEqual(expected, actual[0]) diff --git a/erpnext/accounts/doctype/ledger_health_monitor/__init__.py b/erpnext/accounts/doctype/ledger_health_monitor/__init__.py new file mode 100644 index 0000000000000000000000000000000000000000..e69de29bb2d1d6434b8b29ae775ad8c2e48c5391 diff --git a/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.js b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.js new file mode 100644 index 0000000000000000000000000000000000000000..cf112760f8ce5a349e76afb2f7ae07a333ec16da --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.js @@ -0,0 +1,8 @@ +// Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors +// For license information, please see license.txt + +// frappe.ui.form.on("Ledger Health Monitor", { +// refresh(frm) { + +// }, +// }); diff --git a/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.json b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.json new file mode 100644 index 0000000000000000000000000000000000000000..43ff4bb112c5e83e1d8bb9b3515bc9c124beff13 --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.json @@ -0,0 +1,104 @@ +{ + "actions": [], + "allow_rename": 1, + "creation": "2024-03-27 09:38:07.427997", + "doctype": "DocType", + "engine": "InnoDB", + "field_order": [ + "enable_health_monitor", + "monitor_section", + "monitor_for_last_x_days", + "debit_credit_mismatch", + "general_and_payment_ledger_mismatch", + "section_break_xdsp", + "companies" + ], + "fields": [ + { + "default": "0", + "fieldname": "enable_health_monitor", + "fieldtype": "Check", + "label": "Enable Health Monitor" + }, + { + "fieldname": "monitor_section", + "fieldtype": "Section Break", + "label": "Configuration" + }, + { + "default": "0", + "fieldname": "debit_credit_mismatch", + "fieldtype": "Check", + "label": "Debit-Credit Mismatch" + }, + { + "default": "0", + "fieldname": "general_and_payment_ledger_mismatch", + "fieldtype": "Check", + "label": "Discrepancy between General and Payment Ledger" + }, + { + "default": "60", + "fieldname": "monitor_for_last_x_days", + "fieldtype": "Int", + "in_list_view": 1, + "label": "Monitor for Last 'X' days", + "reqd": 1 + }, + { + "fieldname": "section_break_xdsp", + "fieldtype": "Section Break", + "label": "Companies" + }, + { + "fieldname": "companies", + "fieldtype": "Table", + "options": "Ledger Health Monitor Company" + } + ], + "hide_toolbar": 1, + "index_web_pages_for_search": 1, + "issingle": 1, + "links": [], + "modified": "2024-03-27 10:14:16.511681", + "modified_by": "Administrator", + "module": "Accounts", + "name": "Ledger Health Monitor", + "owner": "Administrator", + "permissions": [ + { + "create": 1, + "delete": 1, + "email": 1, + "print": 1, + "read": 1, + "role": "System Manager", + "share": 1, + "write": 1 + }, + { + "create": 1, + "delete": 1, + "email": 1, + "print": 1, + "read": 1, + "role": "Accounts Manager", + "share": 1, + "write": 1 + }, + { + "create": 1, + "delete": 1, + "email": 1, + "print": 1, + "read": 1, + "role": "Accounts User", + "share": 1, + "write": 1 + } + ], + "sort_field": "modified", + "sort_order": "DESC", + "states": [], + "track_changes": 1 +} diff --git a/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.py b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.py new file mode 100644 index 0000000000000000000000000000000000000000..9f7c569d6d1d9b10fc1adf8a4f4d6256fb54236a --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health_monitor/ledger_health_monitor.py @@ -0,0 +1,28 @@ +# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors +# For license information, please see license.txt + +# import frappe +from frappe.model.document import Document + + +class LedgerHealthMonitor(Document): + # begin: auto-generated types + # This code is auto-generated. Do not modify anything in this block. + + from typing import TYPE_CHECKING + + if TYPE_CHECKING: + from frappe.types import DF + + from erpnext.accounts.doctype.ledger_health_monitor_company.ledger_health_monitor_company import ( + LedgerHealthMonitorCompany, + ) + + companies: DF.Table[LedgerHealthMonitorCompany] + debit_credit_mismatch: DF.Check + enable_health_monitor: DF.Check + general_and_payment_ledger_mismatch: DF.Check + monitor_for_last_x_days: DF.Int + # end: auto-generated types + + pass diff --git a/erpnext/accounts/doctype/ledger_health_monitor/test_ledger_health_monitor.py b/erpnext/accounts/doctype/ledger_health_monitor/test_ledger_health_monitor.py new file mode 100644 index 0000000000000000000000000000000000000000..e0ba4435b825c42540b2b1fa45e6cf0674c70248 --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health_monitor/test_ledger_health_monitor.py @@ -0,0 +1,9 @@ +# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and Contributors +# See license.txt + +# import frappe +from frappe.tests.utils import FrappeTestCase + + +class TestLedgerHealthMonitor(FrappeTestCase): + pass diff --git a/erpnext/accounts/doctype/ledger_health_monitor_company/__init__.py b/erpnext/accounts/doctype/ledger_health_monitor_company/__init__.py new file mode 100644 index 0000000000000000000000000000000000000000..e69de29bb2d1d6434b8b29ae775ad8c2e48c5391 diff --git a/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.json b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.json new file mode 100644 index 0000000000000000000000000000000000000000..ab0a636bad9915588a7d96f18fce08df6955c8f8 --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.json @@ -0,0 +1,32 @@ +{ + "actions": [], + "allow_rename": 1, + "creation": "2024-03-27 10:04:45.727054", + "doctype": "DocType", + "editable_grid": 1, + "engine": "InnoDB", + "field_order": [ + "company" + ], + "fields": [ + { + "fieldname": "company", + "fieldtype": "Link", + "in_list_view": 1, + "label": "Company", + "options": "Company" + } + ], + "index_web_pages_for_search": 1, + "istable": 1, + "links": [], + "modified": "2024-03-27 10:06:22.806155", + "modified_by": "Administrator", + "module": "Accounts", + "name": "Ledger Health Monitor Company", + "owner": "Administrator", + "permissions": [], + "sort_field": "modified", + "sort_order": "DESC", + "states": [] +} diff --git a/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.py b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.py new file mode 100644 index 0000000000000000000000000000000000000000..5890410090dca020d6299ec2935b8cc6ad73b8bf --- /dev/null +++ b/erpnext/accounts/doctype/ledger_health_monitor_company/ledger_health_monitor_company.py @@ -0,0 +1,23 @@ +# Copyright (c) 2024, Frappe Technologies Pvt. Ltd. and contributors +# For license information, please see license.txt + +# import frappe +from frappe.model.document import Document + + +class LedgerHealthMonitorCompany(Document): + # begin: auto-generated types + # This code is auto-generated. Do not modify anything in this block. + + from typing import TYPE_CHECKING + + if TYPE_CHECKING: + from frappe.types import DF + + company: DF.Link | None + parent: DF.Data + parentfield: DF.Data + parenttype: DF.Data + # end: auto-generated types + + pass diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py index acbe9b32f251c4ebb9215bac00162e76d5f66809..721dd6aec1400af0ed96fe7bd13545defc923659 100644 --- a/erpnext/accounts/utils.py +++ b/erpnext/accounts/utils.py @@ -12,11 +12,13 @@ from frappe.query_builder import AliasedQuery, Criterion, Table from frappe.query_builder.functions import Round, Sum from frappe.query_builder.utils import DocType from frappe.utils import ( + add_days, cint, create_batch, cstr, flt, formatdate, + get_datetime, get_number_format_info, getdate, now, @@ -2103,3 +2105,44 @@ def create_gain_loss_journal( def get_party_types_from_account_type(account_type): return frappe.db.get_all("Party Type", {"account_type": account_type}, pluck="name") + + +def run_ledger_health_checks(): + health_monitor_settings = frappe.get_doc("Ledger Health Monitor") + if health_monitor_settings.enable_health_monitor: + period_end = getdate() + period_start = add_days(period_end, -abs(health_monitor_settings.monitor_for_last_x_days)) + + run_date = get_datetime() + + # Debit-Credit mismatch report + if health_monitor_settings.debit_credit_mismatch: + for x in health_monitor_settings.companies: + filters = {"company": x.company, "from_date": period_start, "to_date": period_end} + voucher_wise = frappe.get_doc("Report", "Voucher-wise Balance") + res = voucher_wise.execute_script_report(filters=filters) + for x in res[1]: + doc = frappe.new_doc("Ledger Health") + doc.voucher_type = x.voucher_type + doc.voucher_no = x.voucher_no + doc.debit_credit_mismatch = True + doc.checked_on = run_date + doc.save() + + # General Ledger and Payment Ledger discrepancy + if health_monitor_settings.general_and_payment_ledger_mismatch: + for x in health_monitor_settings.companies: + filters = { + "company": x.company, + "period_start_date": period_start, + "period_end_date": period_end, + } + gl_pl_comparison = frappe.get_doc("Report", "General and Payment Ledger Comparison") + res = gl_pl_comparison.execute_script_report(filters=filters) + for x in res[1]: + doc = frappe.new_doc("Ledger Health") + doc.voucher_type = x.voucher_type + doc.voucher_no = x.voucher_no + doc.general_and_payment_ledger_mismatch = True + doc.checked_on = run_date + doc.save() diff --git a/erpnext/hooks.py b/erpnext/hooks.py index e642368bb18b9ad5d191d839c1c1bbcdcbea3590..ec17a08d3b0f547af2e1dd5eae60194e9de5e96d 100644 --- a/erpnext/hooks.py +++ b/erpnext/hooks.py @@ -500,6 +500,7 @@ scheduler_events = { "erpnext.accounts.doctype.process_statement_of_accounts.process_statement_of_accounts.send_auto_email", "erpnext.accounts.utils.auto_create_exchange_rate_revaluation_daily", "erpnext.accounts.doctype.adjustment_entry.adjustment_entry.reverse_adjustment_entries", + "erpnext.accounts.utils.run_ledger_health_checks", ], "weekly": [ "erpnext.accounts.utils.auto_create_exchange_rate_revaluation_weekly",