feat: report to show difference between stock and account value (#20186)
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 89c8467..e01d6d5 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -891,3 +891,9 @@
def generator():
return cint(frappe.db.get_value('Accounts Settings', None, 'allow_cost_center_in_entry_of_bs_account'))
return frappe.local_cache("get_allow_cost_center_in_entry_of_bs_account", (), generator, regenerate_if_none=True)
+
+def get_stock_accounts(company):
+ return frappe.get_all("Account", filters = {
+ "account_type": "Stock",
+ "company": company
+ })
\ No newline at end of file
diff --git a/erpnext/config/stock.py b/erpnext/config/stock.py
index 03e26b2..dd35f5a 100644
--- a/erpnext/config/stock.py
+++ b/erpnext/config/stock.py
@@ -348,6 +348,12 @@
"is_query_report": True,
"name": "Subcontracted Item To Be Received",
"doctype": "Purchase Order"
+ },
+ {
+ "type": "report",
+ "is_query_report": True,
+ "name": "Stock and Account Value Comparison",
+ "doctype": "Stock Ledger Entry"
}
]
},
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index 6cdb56b..6ed6044 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -177,7 +177,26 @@
return frappe.get_doc("Warehouse", args.docname).convert_to_group_or_ledger()
def get_child_warehouses(warehouse):
- p_warehouse = frappe.get_doc("Warehouse", warehouse)
+ lft, rgt = frappe.get_cached_value("Warehouse", warehouse, [lft, rgt])
return frappe.db.sql_list("""select name from `tabWarehouse`
- where lft >= %s and rgt =< %s""", (p_warehouse.lft, p_warehouse.rgt))
+ where lft >= %s and rgt =< %s""", (lft, rgt))
+
+def get_warehouses_based_on_account(account, company=None):
+ warehouses = []
+ for d in frappe.get_all("Warehouse", fields = ["name", "is_group"],
+ filters = {"account": account}):
+ if d.is_group:
+ warehouses.extend(get_child_warehouses(d.name))
+ else:
+ warehouses.append(d.name)
+
+ if (not warehouses and company and
+ frappe.get_cached_value("Company", company, "default_inventory_account") == account):
+ warehouses = [d.name for d in frappe.get_all("Warehouse", filters={'is_group': 0})]
+
+ if not warehouses:
+ frappe.throw(_("Warehouse not found against the account {0}")
+ .format(account))
+
+ return warehouses
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_and_account_value_comparison/__init__.py b/erpnext/stock/report/stock_and_account_value_comparison/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/report/stock_and_account_value_comparison/__init__.py
diff --git a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js
new file mode 100644
index 0000000..7a170be
--- /dev/null
+++ b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.js
@@ -0,0 +1,37 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Stock and Account Value Comparison"] = {
+ "filters": [
+ {
+ "label": __("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "reqd": 1,
+ "default": frappe.defaults.get_user_default("Company")
+ },
+ {
+ "label": __("Account"),
+ "fieldname": "account",
+ "fieldtype": "Link",
+ "options": "Account",
+ get_query: function() {
+ var company = frappe.query_report.get_filter_value('company');
+ return {
+ filters: {
+ "account_type": "Stock",
+ "company": company
+ }
+ }
+ }
+ },
+ {
+ "label": __("As On Date"),
+ "fieldname": "as_on_date",
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today(),
+ },
+ ]
+};
diff --git a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.json b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.json
new file mode 100644
index 0000000..021159a
--- /dev/null
+++ b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.json
@@ -0,0 +1,28 @@
+{
+ "add_total_row": 1,
+ "creation": "2020-01-09 14:42:45.254751",
+ "disable_prepared_report": 0,
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "letter_head": "",
+ "modified": "2020-01-09 14:42:45.254751",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Stock and Account Value Comparison",
+ "owner": "Administrator",
+ "prepared_report": 0,
+ "ref_doctype": "Stock Ledger Entry",
+ "report_name": "Stock and Account Value Comparison",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Stock User"
+ },
+ {
+ "role": "Accounts Manager"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py
new file mode 100644
index 0000000..eef121e
--- /dev/null
+++ b/erpnext/stock/report/stock_and_account_value_comparison/stock_and_account_value_comparison.py
@@ -0,0 +1,131 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe, erpnext
+from frappe import _
+from erpnext.accounts.utils import get_stock_accounts
+from erpnext.accounts.utils import get_currency_precision
+from erpnext.stock.doctype.warehouse.warehouse import get_warehouses_based_on_account
+
+def execute(filters=None):
+ if not erpnext.is_perpetual_inventory_enabled(filters.company):
+ frappe.throw(_("Perpetual inventory required for the company {0} to view this report.")
+ .format(filters.company))
+
+ data = get_data(filters)
+ columns = get_columns(filters)
+
+ return columns, data
+
+def get_data(report_filters):
+ data = []
+
+ filters = {
+ "company": report_filters.company,
+ "posting_date": ("<=", report_filters.as_on_date)
+ }
+
+ currency_precision = get_currency_precision() or 2
+ stock_ledger_entries = get_stock_ledger_data(report_filters, filters)
+ voucher_wise_gl_data = get_gl_data(report_filters, filters)
+
+ for d in stock_ledger_entries:
+ key = (d.voucher_type, d.voucher_no)
+ gl_data = voucher_wise_gl_data.get(key) or {}
+ d.account_value = gl_data.get("account_value", 0)
+ d.difference_value = (d.stock_value - d.account_value)
+ if abs(d.difference_value) > 1.0/10 ** currency_precision:
+ data.append(d)
+
+ return data
+
+def get_stock_ledger_data(report_filters, filters):
+ if report_filters.account:
+ warehouses = get_warehouses_based_on_account(report_filters.account,
+ report_filters.warehouse)
+
+ filters["warehouse"] = ("in", warehouses)
+
+ return frappe.get_all("Stock Ledger Entry", filters=filters,
+ fields = ["name", "voucher_type", "voucher_no",
+ "sum(stock_value_difference) as stock_value", "posting_date", "posting_time"],
+ group_by = "voucher_type, voucher_no",
+ order_by = "posting_date ASC, posting_time ASC")
+
+def get_gl_data(report_filters, filters):
+ if report_filters.account:
+ stock_accounts = [report_filters.account]
+ else:
+ stock_accounts = [k.name
+ for k in get_stock_accounts(report_filters.company)]
+
+ filters.update({
+ "account": ("in", stock_accounts)
+ })
+
+ if filters.get("warehouse"):
+ del filters["warehouse"]
+
+ gl_entries = frappe.get_all("GL Entry", filters=filters,
+ fields = ["name", "voucher_type", "voucher_no",
+ "sum(debit_in_account_currency) - sum(credit_in_account_currency) as account_value"],
+ group_by = "voucher_type, voucher_no")
+
+ voucher_wise_gl_data = {}
+ for d in gl_entries:
+ key = (d.voucher_type, d.voucher_no)
+ voucher_wise_gl_data[key] = d
+
+ return voucher_wise_gl_data
+
+def get_columns(filters):
+ return [
+ {
+ "label": _("Stock Ledger ID"),
+ "fieldname": "name",
+ "fieldtype": "Link",
+ "options": "Stock Ledger Entry",
+ "width": "80"
+ },
+ {
+ "label": _("Posting Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date"
+ },
+ {
+ "label": _("Posting Time"),
+ "fieldname": "posting_time",
+ "fieldtype": "Time"
+ },
+ {
+ "label": _("Voucher Type"),
+ "fieldname": "voucher_type",
+ "width": "110"
+ },
+ {
+ "label": _("Voucher No"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": "110"
+ },
+ {
+ "label": _("Stock Value"),
+ "fieldname": "stock_value",
+ "fieldtype": "Currency",
+ "width": "120"
+ },
+ {
+ "label": _("Account Value"),
+ "fieldname": "account_value",
+ "fieldtype": "Currency",
+ "width": "120"
+ },
+ {
+ "label": _("Difference Value"),
+ "fieldname": "difference_value",
+ "fieldtype": "Currency",
+ "width": "120"
+ }
+ ]
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index ccba8b0..ff03381 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -264,7 +264,7 @@
`tabItem` item
%s
where
- item.name in (%s) and ifnull(item.disabled, 0) = 0
+ item.name in (%s)
""" % (cf_field, cf_join, ','.join(['%s'] *len(items))), items, as_dict=1)
for item in res: