fix: Finance book filtering in financial statements (#20410)
* fix: Finance book filtering in financial statements
* fix: Use IS NULL instead of ifnull
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.js b/erpnext/accounts/report/balance_sheet/balance_sheet.js
index 8c11514..c4c24c0 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.js
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.js
@@ -14,6 +14,7 @@
frappe.query_reports["Balance Sheet"]["filters"].push({
"fieldname": "include_default_book_entries",
"label": __("Include Default Book Entries"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": 1
});
});
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.js b/erpnext/accounts/report/cash_flow/cash_flow.js
index 03940f4..89244c3 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.js
+++ b/erpnext/accounts/report/cash_flow/cash_flow.js
@@ -20,7 +20,8 @@
{
"fieldname": "include_default_book_entries",
"label": __("Include Default Book Entries"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": 1
}
);
});
\ No newline at end of file
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.py b/erpnext/accounts/report/cash_flow/cash_flow.py
index 98c25b7..0b12477 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/cash_flow.py
@@ -130,11 +130,11 @@
filters = frappe._dict(filters)
if filters.finance_book:
- cond = " and finance_book = %s" %(frappe.db.escape(filters.finance_book))
+ cond = " AND (finance_book in (%s, '') OR finance_book IS NULL)" %(frappe.db.escape(filters.finance_book))
if filters.include_default_book_entries:
company_fb = frappe.db.get_value("Company", company, 'default_finance_book')
- cond = """ and finance_book in (%s, %s)
+ cond = """ AND (finance_book in (%s, %s, '') OR finance_book IS NULL)
""" %(frappe.db.escape(filters.finance_book), frappe.db.escape(company_fb))
gl_sum = frappe.db.sql_list("""
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
index e69a993..48a030a 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
@@ -58,7 +58,8 @@
{
"fieldname": "include_default_book_entries",
"label": __("Include Default Book Entries"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": 1
}
]
}
diff --git a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
index 418a23c..e9eb819 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
@@ -389,9 +389,9 @@
if filters.get("finance_book"):
if filters.get("include_default_book_entries"):
- additional_conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
+ additional_conditions.append("(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)")
else:
- additional_conditions.append("finance_book in (%(finance_book)s)")
+ additional_conditions.append("(finance_book in (%(finance_book)s, '') OR finance_book IS NULL)")
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
diff --git a/erpnext/accounts/report/financial_statements.py b/erpnext/accounts/report/financial_statements.py
index 40d5682..32d9075 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -408,9 +408,9 @@
if filters.get("finance_book"):
if filters.get("include_default_book_entries"):
- additional_conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
+ additional_conditions.append("(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)")
else:
- additional_conditions.append("finance_book in (%(finance_book)s)")
+ additional_conditions.append("(finance_book in (%(finance_book)s, '') OR finance_book IS NULL)")
if accounting_dimensions:
for dimension in accounting_dimensions:
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.js b/erpnext/accounts/report/general_ledger/general_ledger.js
index 4a28706..ac49d37 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.js
+++ b/erpnext/accounts/report/general_ledger/general_ledger.js
@@ -154,7 +154,8 @@
{
"fieldname": "include_default_book_entries",
"label": __("Include Default Book Entries"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": 1
}
]
}
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index b32a54f..0939354 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -184,7 +184,7 @@
if filters.get("finance_book"):
if filters.get("include_default_book_entries"):
- conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
+ conditions.append("(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)")
else:
conditions.append("finance_book in (%(finance_book)s)")
diff --git a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
index a8362bf..baa0bda 100644
--- a/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
+++ b/erpnext/accounts/report/profit_and_loss_statement/profit_and_loss_statement.js
@@ -23,7 +23,8 @@
{
"fieldname": "include_default_book_entries",
"label": __("Include Default Book Entries"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": 1
}
);
});
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.js b/erpnext/accounts/report/trial_balance/trial_balance.js
index f15b5b1..622bab6 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.js
+++ b/erpnext/accounts/report/trial_balance/trial_balance.js
@@ -85,7 +85,8 @@
{
"fieldname": "include_default_book_entries",
"label": __("Include Default Book Entries"),
- "fieldtype": "Check"
+ "fieldtype": "Check",
+ "default": 1
}
],
"formatter": erpnext.financial_statements.formatter,
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index faeee0f..69285cc 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -103,9 +103,9 @@
where lft >= %s and rgt <= %s)""" % (lft, rgt)
if filters.finance_book:
- fb_conditions = " and finance_book = %(finance_book)s"
+ fb_conditions = " AND finance_book = %(finance_book)s"
if filters.include_default_book_entries:
- fb_conditions = " and (finance_book in (%(finance_book)s, %(company_fb)s))"
+ fb_conditions = " AND (finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
additional_conditions += fb_conditions