feat(report): fixed asset register (#19164)
* feat(report): fixed asset register
* fix: fetch supplier and check for asset status
* fix: fetch vendor name from purchase invoice
* style(fixed-asset-register): use conventional column name
* Update fixed_asset_register.py
diff --git a/erpnext/assets/report/fixed_asset_register/__init__.py b/erpnext/assets/report/fixed_asset_register/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/assets/report/fixed_asset_register/__init__.py
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
new file mode 100644
index 0000000..5e994b5
--- /dev/null
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.js
@@ -0,0 +1,30 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Fixed Asset Register"] = {
+ "filters": [
+ {
+ fieldname:"company",
+ label: __("Company"),
+ fieldtype: "Link",
+ options: "Company",
+ default: frappe.defaults.get_user_default("Company"),
+ reqd: 1
+ },
+ {
+ fieldname:"status",
+ label: __("Status"),
+ fieldtype: "Select",
+ options: "In Store\nDisposed",
+ default: 'In Store',
+ reqd: 1
+ },
+ {
+ fieldname:"finance_book",
+ label: __("Finance Book"),
+ fieldtype: "Link",
+ options: "Finance Book"
+ },
+ ]
+};
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.json b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.json
new file mode 100644
index 0000000..ae2aa54
--- /dev/null
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.json
@@ -0,0 +1,27 @@
+{
+ "add_total_row": 0,
+ "creation": "2019-09-23 16:35:02.836134",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2019-09-23 16:35:02.836134",
+ "modified_by": "Administrator",
+ "module": "Assets",
+ "name": "Fixed Asset Register",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Asset",
+ "report_name": "Fixed Asset Register",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Accounts User"
+ },
+ {
+ "role": "Quality Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
new file mode 100644
index 0000000..eac0e2a
--- /dev/null
+++ b/erpnext/assets/report/fixed_asset_register/fixed_asset_register.py
@@ -0,0 +1,172 @@
+# 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 _
+
+def execute(filters=None):
+ filters = frappe._dict(filters or {})
+ columns = get_columns(filters)
+ data = get_data(filters)
+ return columns, data
+
+def get_columns(filters):
+ return [
+ {
+ "label": _("Asset Id"),
+ "fieldtype": "Link",
+ "fieldname": "asset_id",
+ "options": "Asset",
+ "width": 100
+ },
+ {
+ "label": _("Asset Name"),
+ "fieldtype": "Data",
+ "fieldname": "asset_name",
+ "width": 140
+ },
+ {
+ "label": _("Asset Category"),
+ "fieldtype": "Link",
+ "fieldname": "asset_category",
+ "options": "Asset Category",
+ "width": 100
+ },
+ {
+ "label": _("Status"),
+ "fieldtype": "Data",
+ "fieldname": "status",
+ "width": 90
+ },
+ {
+ "label": _("Cost Center"),
+ "fieldtype": "Link",
+ "fieldname": "cost_center",
+ "options": "Cost Center",
+ "width": 100
+ },
+ {
+ "label": _("Department"),
+ "fieldtype": "Link",
+ "fieldname": "department",
+ "options": "Department",
+ "width": 100
+ },
+ {
+ "label": _("Location"),
+ "fieldtype": "Link",
+ "fieldname": "location",
+ "options": "Location",
+ "width": 100
+ },
+ {
+ "label": _("Purchase Date"),
+ "fieldtype": "Date",
+ "fieldname": "purchase_date",
+ "width": 90
+ },
+ {
+ "label": _("Gross Purchase Amount"),
+ "fieldname": "gross_purchase_amount",
+ "options": "Currency",
+ "width": 90
+ },
+ {
+ "label": _("Vendor Name"),
+ "fieldtype": "Data",
+ "fieldname": "vendor_name",
+ "width": 100
+ },
+ {
+ "label": _("Available For Use Date"),
+ "fieldtype": "Date",
+ "fieldname": "available_for_use_date",
+ "width": 90
+ },
+ {
+ "label": _("Current Value"),
+ "fieldname": "current_value",
+ "options": "Currency",
+ "width": 90
+ },
+ ]
+
+def get_conditions(filters):
+ conditions = {'docstatus': 1}
+ status = filters.status
+
+ if filters.company:
+ conditions["company"] = filters.company
+
+ # In Store assets are those that are not sold or scrapped
+ operand = 'not in'
+ if status not in 'In Store':
+ operand = 'in'
+
+ conditions['status'] = (operand, ['Sold', 'Scrapped'])
+
+ return conditions
+
+def get_data(filters):
+
+ data = []
+
+ conditions = get_conditions(filters)
+ current_value_map = get_finance_book_value_map(filters.finance_book)
+ pr_supplier_map = get_purchase_receipt_supplier_map()
+ pi_supplier_map = get_purchase_invoice_supplier_map()
+
+ assets_record = frappe.db.get_all("Asset",
+ filters=conditions,
+ fields=["name", "asset_name", "department", "cost_center", "purchase_receipt",
+ "asset_category", "purchase_date", "gross_purchase_amount", "location",
+ "available_for_use_date", "status", "purchase_invoice"])
+
+ for asset in assets_record:
+ if current_value_map.get(asset.name) is not None:
+ row = {
+ "asset_id": asset.name,
+ "asset_name": asset.asset_name,
+ "status": asset.status,
+ "department": asset.department,
+ "cost_center": asset.cost_center,
+ "vendor_name": pr_supplier_map.get(asset.purchase_receipt) or pi_supplier_map.get(asset.purchase_invoice),
+ "gross_purchase_amount": asset.gross_purchase_amount,
+ "available_for_use_date": asset.available_for_use_date,
+ "location": asset.location,
+ "asset_category": asset.asset_category,
+ "purchase_date": asset.purchase_date,
+ "current_value": current_value_map.get(asset.name)
+ }
+ data.append(row)
+
+ return data
+
+def get_finance_book_value_map(finance_book=''):
+ return frappe._dict(frappe.db.sql(''' Select
+ parent, value_after_depreciation
+ FROM `tabAsset Finance Book`
+ WHERE
+ parentfield='finance_books'
+ AND finance_book=%s''', (finance_book)))
+
+def get_purchase_receipt_supplier_map():
+ return frappe._dict(frappe.db.sql(''' Select
+ pr.name, pr.supplier
+ FROM `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pri
+ WHERE
+ pri.parent = pr.name
+ AND pri.is_fixed_asset=1
+ AND pr.docstatus=1
+ AND pr.is_return=0'''))
+
+def get_purchase_invoice_supplier_map():
+ return frappe._dict(frappe.db.sql(''' Select
+ pi.name, pi.supplier
+ FROM `tabPurchase Invoice` pi, `tabPurchase Invoice Item` pii
+ WHERE
+ pii.parent = pi.name
+ AND pii.is_fixed_asset=1
+ AND pi.docstatus=1
+ AND pi.is_return=0'''))