[report] Asset Depreciations and Balances
diff --git a/erpnext/accounts/report/asset_depreciations_and_balances/__init__.py b/erpnext/accounts/report/asset_depreciations_and_balances/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/accounts/report/asset_depreciations_and_balances/__init__.py
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
new file mode 100644
index 0000000..1da35cd
--- /dev/null
+++ b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.js
@@ -0,0 +1,35 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.query_reports["Asset Depreciations and Balances"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": frappe.defaults.get_user_default("Company"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_start_date"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_end_date"),
+ "reqd": 1
+ },
+ {
+ "fieldname":"asset_category",
+ "label": __("Asset Category"),
+ "fieldtype": "Link",
+ "options": "Asset Category"
+ }
+ ]
+}
diff --git a/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.json b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.json
new file mode 100644
index 0000000..1298d7c
--- /dev/null
+++ b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.json
@@ -0,0 +1,18 @@
+{
+ "add_total_row": 0,
+ "apply_user_permissions": 1,
+ "creation": "2016-04-08 14:56:37.235981",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2016-04-08 14:56:37.235981",
+ "modified_by": "Administrator",
+ "module": "Accounts",
+ "name": "Asset Depreciations and Balances",
+ "owner": "Administrator",
+ "ref_doctype": "Asset",
+ "report_name": "Asset Depreciations and Balances",
+ "report_type": "Script Report"
+}
\ No newline at end of file
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
new file mode 100644
index 0000000..d651892
--- /dev/null
+++ b/erpnext/accounts/report/asset_depreciations_and_balances/asset_depreciations_and_balances.py
@@ -0,0 +1,183 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import formatdate, getdate, flt, add_days
+
+def execute(filters=None):
+ filters.day_before_from_date = add_days(filters.from_date, -1)
+ columns, data = get_columns(filters), get_data(filters)
+ return columns, data
+
+def get_data(filters):
+ data = []
+
+ asset_categories = get_asset_categories(filters)
+ assets = get_assets(filters)
+ asset_costs = get_asset_costs(assets, filters)
+ asset_depreciations = get_accumulated_depreciations(assets, filters)
+
+ for asset_category in asset_categories:
+ row = frappe._dict()
+ row.asset_category = asset_category
+ row.update(asset_costs.get(asset_category))
+
+ 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(asset_depreciations.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))
+
+ 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) - flt(row.depreciation_eliminated))
+
+ data.append(row)
+
+ return data
+
+def get_asset_categories(filters):
+ return frappe.db.sql_list("""
+ select distinct asset_category from `tabAsset`
+ where docstatus=1 and company=%s and purchase_date <= %s
+ """, (filters.company, filters.to_date))
+
+def get_assets(filters):
+ return frappe.db.sql("""
+ select name, asset_category, purchase_date, gross_purchase_amount, disposal_date, status
+ from `tabAsset`
+ where docstatus=1 and company=%s and purchase_date <= %s""",
+ (filters.company, filters.to_date), as_dict=1)
+
+def get_asset_costs(assets, filters):
+ asset_costs = frappe._dict()
+ for d in assets:
+ asset_costs.setdefault(d.asset_category, frappe._dict({
+ "cost_as_on_from_date": 0,
+ "cost_of_new_purchase": 0,
+ "cost_of_sold_asset": 0,
+ "cost_of_scrapped_asset": 0
+ }))
+
+ costs = asset_costs[d.asset_category]
+
+ if getdate(d.purchase_date) < getdate(filters.from_date):
+ if not d.disposal_date or getdate(d.disposal_date) >= getdate(filters.from_date):
+ costs.cost_as_on_from_date += flt(d.gross_purchase_amount)
+ else:
+ costs.cost_of_new_purchase += flt(d.gross_purchase_amount)
+
+ if d.disposal_date and getdate(d.disposal_date) >= getdate(filters.from_date) \
+ and getdate(d.disposal_date) <= getdate(filters.to_date):
+ if d.status == "Sold":
+ costs.cost_of_sold_asset += flt(d.gross_purchase_amount)
+ elif d.status == "Scrapped":
+ costs.cost_of_scrapped_asset += flt(d.gross_purchase_amount)
+
+ return asset_costs
+
+def get_accumulated_depreciations(assets, filters):
+ asset_depreciations = frappe._dict()
+ for d in assets:
+ asset = frappe.get_doc("Asset", d.name)
+
+ asset_depreciations.setdefault(d.asset_category, frappe._dict({
+ "accumulated_depreciation_as_on_from_date": asset.opening_accumulated_depreciation,
+ "depreciation_amount_during_the_period": 0,
+ "depreciation_eliminated": 0
+ }))
+
+ depr = asset_depreciations[d.asset_category]
+
+ for schedule in asset.get("schedules"):
+ if getdate(schedule.schedule_date) < getdate(filters.from_date):
+ depr.accumulated_depreciation_as_on_from_date += flt(schedule.depreciation_amount)
+ elif getdate(schedule.schedule_date) <= getdate(filters.to_date):
+ depr.depreciation_amount_during_the_period += flt(schedule.depreciation_amount)
+
+ if asset.disposal_date and getdate(schedule.schedule_date) > getdate(asset.disposal_date):
+ depr.depreciation_eliminated += flt(schedule.depreciation_amount)
+
+ return asset_depreciations
+
+def get_columns(filters):
+ return [
+ {
+ "label": _("Asset Category"),
+ "fieldname": "asset_category",
+ "fieldtype": "Link",
+ "options": "Asset Category",
+ "width": 120
+ },
+ {
+ "label": _("Cost as on") + " " + formatdate(filters.day_before_from_date),
+ "fieldname": "cost_as_on_from_date",
+ "fieldtype": "Currency",
+ "width": 140
+ },
+ {
+ "label": _("Cost of New Purchase"),
+ "fieldname": "cost_of_new_purchase",
+ "fieldtype": "Currency",
+ "width": 140
+ },
+ {
+ "label": _("Cost of Sold Asset"),
+ "fieldname": "cost_of_sold_asset",
+ "fieldtype": "Currency",
+ "width": 140
+ },
+ {
+ "label": _("Cost of Scrapped Asset"),
+ "fieldname": "cost_of_scrapped_asset",
+ "fieldtype": "Currency",
+ "width": 140
+ },
+ {
+ "label": _("Cost as on") + " " + formatdate(filters.to_date),
+ "fieldname": "cost_as_on_to_date",
+ "fieldtype": "Currency",
+ "width": 140
+ },
+ {
+ "label": _("Accumulated Depreciation as on") + " " + formatdate(filters.day_before_from_date),
+ "fieldname": "accumulated_depreciation_as_on_from_date",
+ "fieldtype": "Currency",
+ "width": 270
+ },
+ {
+ "label": _("Depreciation Amount during the period"),
+ "fieldname": "depreciation_amount_during_the_period",
+ "fieldtype": "Currency",
+ "width": 240
+ },
+ {
+ "label": _("Depreciation Eliminated due to disposal of assets"),
+ "fieldname": "depreciation_eliminated",
+ "fieldtype": "Currency",
+ "width": 300
+ },
+ {
+ "label": _("Accumulated Depreciation as on") + " " + formatdate(filters.to_date),
+ "fieldname": "accumulated_depreciation_as_on_to_date",
+ "fieldtype": "Currency",
+ "width": 270
+ },
+ {
+ "label": _("Net Asset value as on") + " " + formatdate(filters.day_before_from_date),
+ "fieldname": "net_asset_value_as_on_from_date",
+ "fieldtype": "Currency",
+ "width": 200
+ },
+ {
+ "label": _("Net Asset value as on") + " " + formatdate(filters.to_date),
+ "fieldname": "net_asset_value_as_on_to_date",
+ "fieldtype": "Currency",
+ "width": 200
+ }
+ ]