fix: date and finance book fixes in fixed asset register (#35751)
* fix: handle finance books properly and show all assets by default in fixed asset register
* chore: rename value to depr amount
* chore: get asset value for correct fb properly
* chore: rename include_default_book_entries to include_default_book_assets
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
index 4f7b836..b788a32 100644
--- a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
@@ -20,56 +20,6 @@
default: 'In Location'
},
{
- "fieldname":"filter_based_on",
- "label": __("Period Based On"),
- "fieldtype": "Select",
- "options": ["Fiscal Year", "Date Range"],
- "default": "Fiscal Year",
- "reqd": 1
- },
- {
- "fieldname":"from_date",
- "label": __("Start Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.add_months(frappe.datetime.nowdate(), -12),
- "depends_on": "eval: doc.filter_based_on == 'Date Range'",
- "reqd": 1
- },
- {
- "fieldname":"to_date",
- "label": __("End Date"),
- "fieldtype": "Date",
- "default": frappe.datetime.nowdate(),
- "depends_on": "eval: doc.filter_based_on == 'Date Range'",
- "reqd": 1
- },
- {
- "fieldname":"from_fiscal_year",
- "label": __("Start Year"),
- "fieldtype": "Link",
- "options": "Fiscal Year",
- "default": frappe.defaults.get_user_default("fiscal_year"),
- "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
- "reqd": 1
- },
- {
- "fieldname":"to_fiscal_year",
- "label": __("End Year"),
- "fieldtype": "Link",
- "options": "Fiscal Year",
- "default": frappe.defaults.get_user_default("fiscal_year"),
- "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
- "reqd": 1
- },
- {
- "fieldname":"date_based_on",
- "label": __("Date Based On"),
- "fieldtype": "Select",
- "options": ["Purchase Date", "Available For Use Date"],
- "default": "Purchase Date",
- "reqd": 1
- },
- {
fieldname:"asset_category",
label: __("Asset Category"),
fieldtype: "Link",
@@ -90,21 +40,66 @@
reqd: 1
},
{
+ fieldname:"only_existing_assets",
+ label: __("Only existing assets"),
+ fieldtype: "Check"
+ },
+ {
fieldname:"finance_book",
label: __("Finance Book"),
fieldtype: "Link",
options: "Finance Book",
- depends_on: "eval: doc.filter_by_finance_book == 1",
},
{
- fieldname:"filter_by_finance_book",
- label: __("Filter by Finance Book"),
- fieldtype: "Check"
+ "fieldname": "include_default_book_assets",
+ "label": __("Include Default Book Assets"),
+ "fieldtype": "Check",
+ "default": 1
},
{
- fieldname:"only_existing_assets",
- label: __("Only existing assets"),
- fieldtype: "Check"
+ "fieldname":"filter_based_on",
+ "label": __("Period Based On"),
+ "fieldtype": "Select",
+ "options": ["--Select a period--", "Fiscal Year", "Date Range"],
+ "default": "--Select a period--",
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("Start Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.add_months(frappe.datetime.nowdate(), -12),
+ "depends_on": "eval: doc.filter_based_on == 'Date Range'",
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("End Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.nowdate(),
+ "depends_on": "eval: doc.filter_based_on == 'Date Range'",
+ },
+ {
+ "fieldname":"from_fiscal_year",
+ "label": __("Start Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
+ },
+ {
+ "fieldname":"to_fiscal_year",
+ "label": __("End Year"),
+ "fieldtype": "Link",
+ "options": "Fiscal Year",
+ "default": frappe.defaults.get_user_default("fiscal_year"),
+ "depends_on": "eval: doc.filter_based_on == 'Fiscal Year'",
+ },
+ {
+ "fieldname":"date_based_on",
+ "label": __("Date Based On"),
+ "fieldtype": "Select",
+ "options": ["Purchase Date", "Available For Use Date"],
+ "default": "Purchase Date",
+ "depends_on": "eval: doc.filter_based_on == 'Date Range' || doc.filter_based_on == 'Fiscal Year'",
},
]
};
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
index 984b3fd..cb61914 100644
--- a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
@@ -2,9 +2,11 @@
# For license information, please see license.txt
+from itertools import chain
+
import frappe
from frappe import _
-from frappe.query_builder.functions import Sum
+from frappe.query_builder.functions import IfNull, Sum
from frappe.utils import cstr, flt, formatdate, getdate
from erpnext.accounts.report.financial_statements import (
@@ -13,7 +15,6 @@
validate_fiscal_year,
)
from erpnext.assets.doctype.asset.asset import get_asset_value_after_depreciation
-from erpnext.assets.doctype.asset.depreciation import get_depreciation_accounts
def execute(filters=None):
@@ -64,11 +65,9 @@
def get_data(filters):
-
data = []
conditions = get_conditions(filters)
- depreciation_amount_map = get_finance_book_value_map(filters)
pr_supplier_map = get_purchase_receipt_supplier_map()
pi_supplier_map = get_purchase_invoice_supplier_map()
@@ -102,20 +101,27 @@
]
assets_record = frappe.db.get_all("Asset", filters=conditions, fields=fields)
- assets_linked_to_fb = None
+ assets_linked_to_fb = get_assets_linked_to_fb(filters)
- if filters.filter_by_finance_book:
- assets_linked_to_fb = frappe.db.get_all(
- doctype="Asset Finance Book",
- filters={"finance_book": filters.finance_book or ("is", "not set")},
- pluck="parent",
- )
+ company_fb = frappe.get_cached_value("Company", filters.company, "default_finance_book")
+
+ if filters.include_default_book_assets and company_fb:
+ finance_book = company_fb
+ elif filters.finance_book:
+ finance_book = filters.finance_book
+ else:
+ finance_book = None
+
+ depreciation_amount_map = get_asset_depreciation_amount_map(filters, finance_book)
for asset in assets_record:
if assets_linked_to_fb and asset.asset_id not in assets_linked_to_fb:
continue
- asset_value = get_asset_value_after_depreciation(asset.asset_id, filters.finance_book)
+ asset_value = get_asset_value_after_depreciation(
+ asset.asset_id, finance_book
+ ) or get_asset_value_after_depreciation(asset.asset_id)
+
row = {
"asset_id": asset.asset_id,
"asset_name": asset.asset_name,
@@ -126,7 +132,7 @@
or pi_supplier_map.get(asset.purchase_invoice),
"gross_purchase_amount": asset.gross_purchase_amount,
"opening_accumulated_depreciation": asset.opening_accumulated_depreciation,
- "depreciated_amount": get_depreciation_amount_of_asset(asset, depreciation_amount_map, filters),
+ "depreciated_amount": get_depreciation_amount_of_asset(asset, depreciation_amount_map),
"available_for_use_date": asset.available_for_use_date,
"location": asset.location,
"asset_category": asset.asset_category,
@@ -140,14 +146,23 @@
def prepare_chart_data(data, filters):
labels_values_map = {}
- date_field = frappe.scrub(filters.date_based_on)
+ if filters.filter_based_on not in ("Date Range", "Fiscal Year"):
+ filters_filter_based_on = "Date Range"
+ date_field = "purchase_date"
+ filters_from_date = min(data, key=lambda a: a.get(date_field)).get(date_field)
+ filters_to_date = max(data, key=lambda a: a.get(date_field)).get(date_field)
+ else:
+ filters_filter_based_on = filters.filter_based_on
+ date_field = frappe.scrub(filters.date_based_on)
+ filters_from_date = filters.from_date
+ filters_to_date = filters.to_date
period_list = get_period_list(
filters.from_fiscal_year,
filters.to_fiscal_year,
- filters.from_date,
- filters.to_date,
- filters.filter_based_on,
+ filters_from_date,
+ filters_to_date,
+ filters_filter_based_on,
"Monthly",
company=filters.company,
ignore_fiscal_year=True,
@@ -184,59 +199,78 @@
}
-def get_depreciation_amount_of_asset(asset, depreciation_amount_map, filters):
- if asset.calculate_depreciation:
- depr_amount = depreciation_amount_map.get(asset.asset_id) or 0.0
- else:
- depr_amount = get_manual_depreciation_amount_of_asset(asset, filters)
+def get_assets_linked_to_fb(filters):
+ afb = frappe.qb.DocType("Asset Finance Book")
- return flt(depr_amount, 2)
-
-
-def get_finance_book_value_map(filters):
- date = filters.to_date if filters.filter_based_on == "Date Range" else filters.year_end_date
-
- return frappe._dict(
- frappe.db.sql(
- """ Select
- ads.asset, SUM(depreciation_amount)
- FROM `tabAsset Depreciation Schedule` ads, `tabDepreciation Schedule` ds
- WHERE
- ds.parent = ads.name
- AND ifnull(ads.finance_book, '')=%s
- AND ads.docstatus=1
- AND ds.parentfield='depreciation_schedule'
- AND ds.schedule_date<=%s
- AND ds.journal_entry IS NOT NULL
- GROUP BY ads.asset""",
- (cstr(filters.finance_book or ""), date),
- )
+ query = frappe.qb.from_(afb).select(
+ afb.parent,
)
+ if filters.include_default_book_assets:
+ company_fb = frappe.get_cached_value("Company", filters.company, "default_finance_book")
-def get_manual_depreciation_amount_of_asset(asset, filters):
+ if filters.finance_book and company_fb and cstr(filters.finance_book) != cstr(company_fb):
+ frappe.throw(
+ _("To use a different finance book, please uncheck 'Include Default Book Entries'")
+ )
+
+ query = query.where(
+ (afb.finance_book.isin([cstr(filters.finance_book), cstr(company_fb), ""]))
+ | (afb.finance_book.isnull())
+ )
+ else:
+ query = query.where(
+ (afb.finance_book.isin([cstr(filters.finance_book), ""])) | (afb.finance_book.isnull())
+ )
+
+ assets_linked_to_fb = list(chain(*query.run(as_list=1)))
+
+ return assets_linked_to_fb
+
+
+def get_depreciation_amount_of_asset(asset, depreciation_amount_map):
+ return depreciation_amount_map.get(asset.asset_id) or 0.0
+
+
+def get_asset_depreciation_amount_map(filters, finance_book):
date = filters.to_date if filters.filter_based_on == "Date Range" else filters.year_end_date
- (_, _, depreciation_expense_account) = get_depreciation_accounts(asset)
-
+ asset = frappe.qb.DocType("Asset")
gle = frappe.qb.DocType("GL Entry")
+ aca = frappe.qb.DocType("Asset Category Account")
+ company = frappe.qb.DocType("Company")
- result = (
+ query = (
frappe.qb.from_(gle)
- .select(Sum(gle.debit))
- .where(gle.against_voucher == asset.asset_id)
- .where(gle.account == depreciation_expense_account)
+ .join(asset)
+ .on(gle.against_voucher == asset.name)
+ .join(aca)
+ .on((aca.parent == asset.asset_category) & (aca.company_name == asset.company))
+ .join(company)
+ .on(company.name == asset.company)
+ .select(asset.name.as_("asset"), Sum(gle.debit).as_("depreciation_amount"))
+ .where(
+ gle.account == IfNull(aca.depreciation_expense_account, company.depreciation_expense_account)
+ )
.where(gle.debit != 0)
.where(gle.is_cancelled == 0)
- .where(gle.posting_date <= date)
- ).run()
+ .where(asset.docstatus == 1)
+ .groupby(asset.name)
+ )
- if result and result[0] and result[0][0]:
- depr_amount = result[0][0]
+ if finance_book:
+ query = query.where(
+ (gle.finance_book.isin([cstr(finance_book), ""])) | (gle.finance_book.isnull())
+ )
else:
- depr_amount = 0
+ query = query.where((gle.finance_book.isin([""])) | (gle.finance_book.isnull()))
- return depr_amount
+ if filters.filter_based_on in ("Date Range", "Fiscal Year"):
+ query = query.where(gle.posting_date <= date)
+
+ asset_depr_amount_map = query.run()
+
+ return dict(asset_depr_amount_map)
def get_purchase_receipt_supplier_map():