fix: finance book filters includes the gl data which don't have finance book
diff --git a/erpnext/accounts/report/balance_sheet/balance_sheet.js b/erpnext/accounts/report/balance_sheet/balance_sheet.js
index f22f3a1..4bc29da 100644
--- a/erpnext/accounts/report/balance_sheet/balance_sheet.js
+++ b/erpnext/accounts/report/balance_sheet/balance_sheet.js
@@ -10,4 +10,10 @@
"fieldtype": "Check",
"default": 1
});
+
+ frappe.query_reports["Balance Sheet"]["filters"].push({
+ "fieldname": "include_default_book_entries",
+ "label": __("Include Default Book Entries"),
+ "fieldtype": "Check"
+ });
});
diff --git a/erpnext/accounts/report/cash_flow/cash_flow.js b/erpnext/accounts/report/cash_flow/cash_flow.js
index 391f57b..0422111 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.js
+++ b/erpnext/accounts/report/cash_flow/cash_flow.js
@@ -15,4 +15,10 @@
"label": __("Accumulated Values"),
"fieldtype": "Check"
});
+
+ frappe.query_reports["Cash Flow"]["filters"].push({
+ "fieldname": "include_default_book_entries",
+ "label": __("Include Default Book Entries"),
+ "fieldtype": "Check"
+ });
});
\ 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 f048c1a..75d99e7 100644
--- a/erpnext/accounts/report/cash_flow/cash_flow.py
+++ b/erpnext/accounts/report/cash_flow/cash_flow.py
@@ -14,8 +14,8 @@
if cint(frappe.db.get_single_value('Accounts Settings', 'use_custom_cash_flow')):
from erpnext.accounts.report.cash_flow.custom_cash_flow import execute as execute_custom
return execute_custom(filters=filters)
-
- period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
+
+ period_list = get_period_list(filters.from_fiscal_year, filters.to_fiscal_year,
filters.periodicity, filters.accumulated_values, filters.company)
cash_flow_accounts = get_cash_flow_accounts()
@@ -25,18 +25,18 @@
accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
expense = get_data(filters.company, "Expense", "Debit", period_list, filters=filters,
accumulated_values=filters.accumulated_values, ignore_closing_entries=True, ignore_accumulated_values_for_fy= True)
-
+
net_profit_loss = get_net_profit_loss(income, expense, period_list, filters.company)
data = []
company_currency = frappe.get_cached_value('Company', filters.company, "default_currency")
-
+
for cash_flow_account in cash_flow_accounts:
section_data = []
data.append({
- "account_name": cash_flow_account['section_header'],
+ "account_name": cash_flow_account['section_header'],
"parent_account": None,
- "indent": 0.0,
+ "indent": 0.0,
"account": cash_flow_account['section_header']
})
@@ -44,18 +44,18 @@
# add first net income in operations section
if net_profit_loss:
net_profit_loss.update({
- "indent": 1,
+ "indent": 1,
"parent_account": cash_flow_accounts[0]['section_header']
})
data.append(net_profit_loss)
section_data.append(net_profit_loss)
for account in cash_flow_account['account_types']:
- account_data = get_account_type_based_data(filters.company,
- account['account_type'], period_list, filters.accumulated_values)
+ account_data = get_account_type_based_data(filters.company,
+ account['account_type'], period_list, filters.accumulated_values, filters)
account_data.update({
"account_name": account['label'],
- "account": account['label'],
+ "account": account['label'],
"indent": 1,
"parent_account": cash_flow_account['section_header'],
"currency": company_currency
@@ -63,7 +63,7 @@
data.append(account_data)
section_data.append(account_data)
- add_total_row_account(data, section_data, cash_flow_account['section_footer'],
+ add_total_row_account(data, section_data, cash_flow_account['section_footer'],
period_list, company_currency)
add_total_row_account(data, data, _("Net Change in Cash"), period_list, company_currency)
@@ -105,13 +105,15 @@
# combine all cash flow accounts for iteration
return [operation_accounts, investing_accounts, financing_accounts]
-def get_account_type_based_data(company, account_type, period_list, accumulated_values):
+def get_account_type_based_data(company, account_type, period_list, accumulated_values, filters):
data = {}
total = 0
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
- amount = get_account_type_based_gl_data(company, start_date, period['to_date'], account_type)
+ amount = get_account_type_based_gl_data(company, start_date,
+ period['to_date'], account_type, filters)
+
if amount and account_type == "Depreciation":
amount *= -1
@@ -121,14 +123,24 @@
data["total"] = total
return data
-def get_account_type_based_gl_data(company, start_date, end_date, account_type):
+def get_account_type_based_gl_data(company, start_date, end_date, account_type, filters):
+ cond = ""
+
+ if filters.finance_book:
+ cond = " and finance_book = %s" %(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)
+ """ %(frappe.db.escape(filters.finance_book), frappe.db.escape(company_fb))
+
gl_sum = frappe.db.sql_list("""
select sum(credit) - sum(debit)
from `tabGL Entry`
where company=%s and posting_date >= %s and posting_date <= %s
and voucher_type != 'Period Closing Voucher'
- and account in ( SELECT name FROM tabAccount WHERE account_type = %s)
- """, (company, start_date, end_date, account_type))
+ and account in ( SELECT name FROM tabAccount WHERE account_type = %s) {cond}
+ """.format(cond=cond), (company, start_date, end_date, account_type))
return gl_sum[0] if gl_sum and gl_sum[0] else 0
@@ -154,7 +166,7 @@
key = period if consolidated else period['key']
total_row.setdefault(key, 0.0)
total_row[key] += row.get(key, 0.0)
-
+
total_row.setdefault("total", 0.0)
total_row["total"] += row["total"]
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 7b373f0..e69a993 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.js
@@ -55,5 +55,10 @@
"fieldtype": "Check",
"default": 0
},
+ {
+ "fieldname": "include_default_book_entries",
+ "label": __("Include Default Book Entries"),
+ "fieldtype": "Check"
+ }
]
}
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 0024931..c40310b 100644
--- a/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
+++ b/erpnext/accounts/report/consolidated_financial_statement/consolidated_financial_statement.py
@@ -356,7 +356,8 @@
"lft": root_lft,
"rgt": root_rgt,
"company": d.name,
- "finance_book": filters.get("finance_book")
+ "finance_book": filters.get("finance_book"),
+ "company_fb": frappe.db.get_value("Company", d.name, 'default_finance_book')
},
as_dict=True)
@@ -387,7 +388,10 @@
additional_conditions.append("gl.posting_date >= %(from_date)s")
if filters.get("finance_book"):
- additional_conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
+ if filters.get("include_default_book_entries"):
+ additional_conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
+ else:
+ additional_conditions.append("finance_book in (%(finance_book)s)")
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 41205ae..43cb889 100644
--- a/erpnext/accounts/report/financial_statements.py
+++ b/erpnext/accounts/report/financial_statements.py
@@ -355,6 +355,10 @@
"to_date": to_date,
}
+ if filters.get("include_default_book_entries"):
+ gl_filters["company_fb"] = frappe.db.get_value("Company",
+ company, 'default_finance_book')
+
for key, value in filters.items():
if value:
gl_filters.update({
@@ -399,7 +403,10 @@
additional_conditions.append("cost_center in %(cost_center)s")
if filters.get("finance_book"):
- additional_conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
+ if filters.get("include_default_book_entries"):
+ additional_conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
+ else:
+ additional_conditions.append("finance_book in (%(finance_book)s)")
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 4235b7f..def25c9 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.js
+++ b/erpnext/accounts/report/general_ledger/general_ledger.js
@@ -217,6 +217,11 @@
"label": __("Show Opening Entries"),
"fieldtype": "Check"
},
+ {
+ "fieldname": "include_default_book_entries",
+ "label": __("Include Default Book Entries"),
+ "fieldtype": "Check"
+ }
]
}
diff --git a/erpnext/accounts/report/general_ledger/general_ledger.py b/erpnext/accounts/report/general_ledger/general_ledger.py
index d9f395b..3c16266 100644
--- a/erpnext/accounts/report/general_ledger/general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/general_ledger.py
@@ -134,6 +134,10 @@
sum(debit_in_account_currency) as debit_in_account_currency,
sum(credit_in_account_currency) as credit_in_account_currency"""
+ if filters.get("include_default_book_entries"):
+ filters['company_fb'] = frappe.db.get_value("Company",
+ filters.get("company"), 'default_finance_book')
+
gl_entries = frappe.db.sql(
"""
select
@@ -189,7 +193,10 @@
conditions.append("project in %(project)s")
if filters.get("finance_book"):
- conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
+ if filters.get("include_default_book_entries"):
+ conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
+ else:
+ conditions.append("finance_book in (%(finance_book)s)")
from frappe.desk.reportview import build_match_conditions
match_conditions = build_match_conditions("GL Entry")
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 250e516..7741a45 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
@@ -41,6 +41,11 @@
"fieldname": "accumulated_values",
"label": __("Accumulated Values"),
"fieldtype": "Check"
+ },
+ {
+ "fieldname": "include_default_book_entries",
+ "label": __("Include Default Book Entries"),
+ "fieldtype": "Check"
}
);
});
diff --git a/erpnext/accounts/report/trial_balance/trial_balance.py b/erpnext/accounts/report/trial_balance/trial_balance.py
index b5f0186..5758b0b 100644
--- a/erpnext/accounts/report/trial_balance/trial_balance.py
+++ b/erpnext/accounts/report/trial_balance/trial_balance.py
@@ -105,7 +105,7 @@
if filters.finance_book:
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) or finance_book is null)"
+ fb_conditions = " and (finance_book in (%(finance_book)s, %(company_fb)s))"
additional_conditions += fb_conditions