feat: group by Asset in Asset Depreciations and Balances report (#38923)
feat: group by asset in asset depreciations and balances report
diff --git a/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.js b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.js
index 5f78b77..06fa9f3 100644
--- a/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.js
+++ b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.js
@@ -26,10 +26,25 @@
"reqd": 1
},
{
+ "fieldname":"group_by",
+ "label": __("Group By"),
+ "fieldtype": "Select",
+ "options": ["Asset Category", "Asset"],
+ "default": "Asset Category",
+ },
+ {
"fieldname":"asset_category",
"label": __("Asset Category"),
"fieldtype": "Link",
- "options": "Asset Category"
- }
+ "options": "Asset Category",
+ "depends_on": "eval: doc.group_by == 'Asset Category'",
+ },
+ {
+ "fieldname":"asset",
+ "label": __("Asset"),
+ "fieldtype": "Link",
+ "options": "Asset",
+ "depends_on": "eval: doc.group_by == 'Asset'",
+ },
]
}
diff --git a/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.py b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.py
index bdc8d85..48da17a 100644
--- a/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.py
+++ b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.py
@@ -14,10 +14,17 @@
def get_data(filters):
+ if filters.get("group_by") == "Asset Category":
+ return get_group_by_asset_category_data(filters)
+ elif filters.get("group_by") == "Asset":
+ return get_group_by_asset_data(filters)
+
+
+def get_group_by_asset_category_data(filters):
data = []
- asset_categories = get_asset_categories(filters)
- assets = get_assets(filters)
+ asset_categories = get_asset_categories_for_grouped_by_category(filters)
+ assets = get_assets_for_grouped_by_category(filters)
for asset_category in asset_categories:
row = frappe._dict()
@@ -38,6 +45,7 @@
if asset["asset_category"] == asset_category.get("asset_category", "")
)
)
+
row.accumulated_depreciation_as_on_to_date = (
flt(row.accumulated_depreciation_as_on_from_date)
+ flt(row.depreciation_amount_during_the_period)
@@ -57,7 +65,7 @@
return data
-def get_asset_categories(filters):
+def get_asset_categories_for_grouped_by_category(filters):
condition = ""
if filters.get("asset_category"):
condition += " and asset_category = %(asset_category)s"
@@ -116,7 +124,105 @@
)
-def get_assets(filters):
+def get_asset_details_for_grouped_by_category(filters):
+ condition = ""
+ if filters.get("asset"):
+ condition += " and name = %(asset)s"
+ return frappe.db.sql(
+ """
+ SELECT name,
+ ifnull(sum(case when purchase_date < %(from_date)s then
+ case when ifnull(disposal_date, 0) = 0 or disposal_date >= %(from_date)s then
+ gross_purchase_amount
+ else
+ 0
+ end
+ else
+ 0
+ end), 0) as cost_as_on_from_date,
+ ifnull(sum(case when purchase_date >= %(from_date)s then
+ gross_purchase_amount
+ else
+ 0
+ end), 0) as cost_of_new_purchase,
+ ifnull(sum(case when ifnull(disposal_date, 0) != 0
+ and disposal_date >= %(from_date)s
+ and disposal_date <= %(to_date)s then
+ case when status = "Sold" then
+ gross_purchase_amount
+ else
+ 0
+ end
+ else
+ 0
+ end), 0) as cost_of_sold_asset,
+ ifnull(sum(case when ifnull(disposal_date, 0) != 0
+ and disposal_date >= %(from_date)s
+ and disposal_date <= %(to_date)s then
+ case when status = "Scrapped" then
+ gross_purchase_amount
+ else
+ 0
+ end
+ else
+ 0
+ end), 0) as cost_of_scrapped_asset
+ from `tabAsset`
+ where docstatus=1 and company=%(company)s and purchase_date <= %(to_date)s {}
+ group by name
+ """.format(
+ condition
+ ),
+ {
+ "to_date": filters.to_date,
+ "from_date": filters.from_date,
+ "company": filters.company,
+ "asset": filters.get("asset"),
+ },
+ as_dict=1,
+ )
+
+
+def get_group_by_asset_data(filters):
+ data = []
+
+ asset_details = get_asset_details_for_grouped_by_category(filters)
+ assets = get_assets_for_grouped_by_asset(filters)
+
+ for asset_detail in asset_details:
+ row = frappe._dict()
+ # row.asset_category = asset_category
+ row.update(asset_detail)
+
+ row.cost_as_on_to_date = (
+ flt(row.cost_as_on_from_date)
+ + flt(row.cost_of_new_purchase)
+ - flt(row.cost_of_sold_asset)
+ - flt(row.cost_of_scrapped_asset)
+ )
+
+ row.update(next(asset for asset in assets if asset["asset"] == asset_detail.get("name", "")))
+
+ row.accumulated_depreciation_as_on_to_date = (
+ flt(row.accumulated_depreciation_as_on_from_date)
+ + flt(row.depreciation_amount_during_the_period)
+ - flt(row.depreciation_eliminated_during_the_period)
+ )
+
+ row.net_asset_value_as_on_from_date = flt(row.cost_as_on_from_date) - flt(
+ row.accumulated_depreciation_as_on_from_date
+ )
+
+ row.net_asset_value_as_on_to_date = flt(row.cost_as_on_to_date) - flt(
+ row.accumulated_depreciation_as_on_to_date
+ )
+
+ data.append(row)
+
+ return data
+
+
+def get_assets_for_grouped_by_category(filters):
condition = ""
if filters.get("asset_category"):
condition = " and a.asset_category = '{}'".format(filters.get("asset_category"))
@@ -178,15 +284,93 @@
)
+def get_assets_for_grouped_by_asset(filters):
+ condition = ""
+ if filters.get("asset"):
+ condition = " and a.name = '{}'".format(filters.get("asset"))
+ return frappe.db.sql(
+ """
+ SELECT results.name as asset,
+ sum(results.accumulated_depreciation_as_on_from_date) as accumulated_depreciation_as_on_from_date,
+ sum(results.depreciation_eliminated_during_the_period) as depreciation_eliminated_during_the_period,
+ sum(results.depreciation_amount_during_the_period) as depreciation_amount_during_the_period
+ from (SELECT a.name as name,
+ ifnull(sum(case when gle.posting_date < %(from_date)s and (ifnull(a.disposal_date, 0) = 0 or a.disposal_date >= %(from_date)s) then
+ gle.debit
+ else
+ 0
+ end), 0) as accumulated_depreciation_as_on_from_date,
+ ifnull(sum(case when ifnull(a.disposal_date, 0) != 0 and a.disposal_date >= %(from_date)s
+ and a.disposal_date <= %(to_date)s and gle.posting_date <= a.disposal_date then
+ gle.debit
+ else
+ 0
+ end), 0) as depreciation_eliminated_during_the_period,
+ ifnull(sum(case when gle.posting_date >= %(from_date)s and gle.posting_date <= %(to_date)s
+ and (ifnull(a.disposal_date, 0) = 0 or gle.posting_date <= a.disposal_date) then
+ gle.debit
+ else
+ 0
+ end), 0) as depreciation_amount_during_the_period
+ from `tabGL Entry` gle
+ join `tabAsset` a on
+ gle.against_voucher = a.name
+ join `tabAsset Category Account` aca on
+ aca.parent = a.asset_category and aca.company_name = %(company)s
+ join `tabCompany` company on
+ company.name = %(company)s
+ where a.docstatus=1 and a.company=%(company)s and a.purchase_date <= %(to_date)s and gle.debit != 0 and gle.is_cancelled = 0 and gle.account = ifnull(aca.depreciation_expense_account, company.depreciation_expense_account) {0}
+ group by a.name
+ union
+ SELECT a.name as name,
+ ifnull(sum(case when ifnull(a.disposal_date, 0) != 0 and (a.disposal_date < %(from_date)s or a.disposal_date > %(to_date)s) then
+ 0
+ else
+ a.opening_accumulated_depreciation
+ end), 0) as accumulated_depreciation_as_on_from_date,
+ ifnull(sum(case when a.disposal_date >= %(from_date)s and a.disposal_date <= %(to_date)s then
+ a.opening_accumulated_depreciation
+ else
+ 0
+ end), 0) as depreciation_eliminated_during_the_period,
+ 0 as depreciation_amount_during_the_period
+ from `tabAsset` a
+ where a.docstatus=1 and a.company=%(company)s and a.purchase_date <= %(to_date)s {0}
+ group by a.name) as results
+ group by results.name
+ """.format(
+ condition
+ ),
+ {"to_date": filters.to_date, "from_date": filters.from_date, "company": filters.company},
+ as_dict=1,
+ )
+
+
def get_columns(filters):
- return [
- {
- "label": _("Asset Category"),
- "fieldname": "asset_category",
- "fieldtype": "Link",
- "options": "Asset Category",
- "width": 120,
- },
+ columns = []
+
+ if filters.get("group_by") == "Asset Category":
+ columns.append(
+ {
+ "label": _("Asset Category"),
+ "fieldname": "asset_category",
+ "fieldtype": "Link",
+ "options": "Asset Category",
+ "width": 120,
+ }
+ )
+ elif filters.get("group_by") == "Asset":
+ columns.append(
+ {
+ "label": _("Asset"),
+ "fieldname": "asset",
+ "fieldtype": "Link",
+ "options": "Asset",
+ "width": 120,
+ }
+ )
+
+ columns += [
{
"label": _("Cost as on") + " " + formatdate(filters.day_before_from_date),
"fieldname": "cost_as_on_from_date",
@@ -254,3 +438,5 @@
"width": 200,
},
]
+
+ return columns