Merge pull request #31424 from rohitwaghchaure/feat-inventory-dimension
feat: Inventory Dimension
diff --git a/erpnext/controllers/stock_controller.py b/erpnext/controllers/stock_controller.py
index e90a4f6..e27718a 100644
--- a/erpnext/controllers/stock_controller.py
+++ b/erpnext/controllers/stock_controller.py
@@ -18,6 +18,9 @@
from erpnext.accounts.utils import get_fiscal_year
from erpnext.controllers.accounts_controller import AccountsController
from erpnext.stock import get_warehouse_account_map
+from erpnext.stock.doctype.inventory_dimension.inventory_dimension import (
+ get_evaluated_inventory_dimension,
+)
from erpnext.stock.stock_ledger import get_items_to_be_repost
@@ -364,8 +367,16 @@
)
sl_dict.update(args)
+ self.update_inventory_dimensions(d, sl_dict)
+
return sl_dict
+ def update_inventory_dimensions(self, row, sl_dict) -> None:
+ dimensions = get_evaluated_inventory_dimension(row, sl_dict, parent_doc=self)
+ for dimension in dimensions:
+ if dimension and row.get(dimension.source_fieldname):
+ sl_dict[dimension.target_fieldname] = row.get(dimension.source_fieldname)
+
def make_sl_entries(self, sl_entries, allow_negative_stock=False, via_landed_cost_voucher=False):
from erpnext.stock.stock_ledger import make_sl_entries
diff --git a/erpnext/public/js/utils.js b/erpnext/public/js/utils.js
index 51139b3..f2bea58 100755
--- a/erpnext/public/js/utils.js
+++ b/erpnext/public/js/utils.js
@@ -213,6 +213,32 @@
});
},
+ add_inventory_dimensions: function(report_name, index) {
+ let filters = frappe.query_reports[report_name].filters;
+
+ frappe.call({
+ method: "erpnext.stock.doctype.inventory_dimension.inventory_dimension.get_inventory_dimensions",
+ callback: function(r) {
+ if (r.message && r.message.length) {
+ r.message.forEach((dimension) => {
+ let found = filters.some(el => el.fieldname === dimension['fieldname']);
+
+ if (!found) {
+ filters.splice(index, 0, {
+ "fieldname": dimension["fieldname"],
+ "label": __(dimension["label"]),
+ "fieldtype": "MultiSelectList",
+ get_data: function(txt) {
+ return frappe.db.get_link_options(dimension["doctype"], txt);
+ },
+ });
+ }
+ });
+ }
+ }
+ });
+ },
+
make_subscription: function(doctype, docname) {
frappe.call({
method: "frappe.automation.doctype.auto_repeat.auto_repeat.make_auto_repeat",
diff --git a/erpnext/stock/doctype/inventory_dimension/__init__.py b/erpnext/stock/doctype/inventory_dimension/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/stock/doctype/inventory_dimension/__init__.py
diff --git a/erpnext/stock/doctype/inventory_dimension/inventory_dimension.js b/erpnext/stock/doctype/inventory_dimension/inventory_dimension.js
new file mode 100644
index 0000000..91a21f4
--- /dev/null
+++ b/erpnext/stock/doctype/inventory_dimension/inventory_dimension.js
@@ -0,0 +1,48 @@
+// Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.ui.form.on('Inventory Dimension', {
+ setup(frm) {
+ frm.trigger('set_query_on_fields');
+ },
+
+ set_query_on_fields(frm) {
+ frm.set_query('reference_document', () => {
+ let invalid_doctypes = frappe.model.core_doctypes_list;
+ invalid_doctypes.push('Batch', 'Serial No', 'Warehouse', 'Item', 'Inventory Dimension',
+ 'Accounting Dimension', 'Accounting Dimension Filter');
+
+ return {
+ filters: {
+ 'istable': 0,
+ 'issingle': 0,
+ 'name': ['not in', invalid_doctypes]
+ }
+ };
+ });
+
+ frm.set_query('document_type', () => {
+ return {
+ query: 'erpnext.stock.doctype.inventory_dimension.inventory_dimension.get_inventory_documents',
+ };
+ });
+ },
+
+ onload(frm) {
+ frm.trigger('render_traget_field');
+ },
+
+ refresh(frm) {
+ if (frm.doc.__onload && frm.doc.__onload.has_stock_ledger
+ && frm.doc.__onload.has_stock_ledger.length) {
+ let msg = __('Stock transactions exists against this dimension, user can not update document.');
+ frm.dashboard.add_comment(msg, 'blue', true);
+
+ frm.fields.forEach((field) => {
+ if (field.df.fieldname !== 'disabled') {
+ frm.set_df_property(field.df.fieldname, "read_only", "1");
+ }
+ });
+ }
+ }
+});
diff --git a/erpnext/stock/doctype/inventory_dimension/inventory_dimension.json b/erpnext/stock/doctype/inventory_dimension/inventory_dimension.json
new file mode 100644
index 0000000..8b334d1
--- /dev/null
+++ b/erpnext/stock/doctype/inventory_dimension/inventory_dimension.json
@@ -0,0 +1,194 @@
+{
+ "actions": [],
+ "allow_rename": 1,
+ "autoname": "field:dimension_name",
+ "creation": "2022-06-17 13:04:16.554051",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "dimension_details_tab",
+ "dimension_name",
+ "reference_document",
+ "column_break_4",
+ "disabled",
+ "section_break_7",
+ "field_mapping_section",
+ "source_fieldname",
+ "column_break_9",
+ "target_fieldname",
+ "applicable_for_documents_tab",
+ "apply_to_all_doctypes",
+ "document_type",
+ "istable",
+ "type_of_transaction",
+ "column_break_16",
+ "condition",
+ "applicable_condition_example_section",
+ "html_19"
+ ],
+ "fields": [
+ {
+ "fieldname": "dimension_details_tab",
+ "fieldtype": "Tab Break",
+ "label": "Dimension Details"
+ },
+ {
+ "fieldname": "reference_document",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Reference Document",
+ "options": "DocType",
+ "reqd": 1
+ },
+ {
+ "fieldname": "dimension_name",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Dimension Name",
+ "reqd": 1,
+ "unique": 1
+ },
+ {
+ "fieldname": "applicable_for_documents_tab",
+ "fieldtype": "Tab Break",
+ "label": "Applicable For Documents"
+ },
+ {
+ "depends_on": "eval:!doc.apply_to_all_doctypes",
+ "fieldname": "document_type",
+ "fieldtype": "Link",
+ "label": "Applicable to Document",
+ "mandatory_depends_on": "eval:!doc.apply_to_all_doctypes",
+ "options": "DocType"
+ },
+ {
+ "fieldname": "column_break_9",
+ "fieldtype": "Column Break"
+ },
+ {
+ "default": "0",
+ "depends_on": "eval:!doc.apply_to_all_doctypes && doc.document_type",
+ "fetch_from": "document_type.istable",
+ "fieldname": "istable",
+ "fieldtype": "Check",
+ "label": " Is Child Table",
+ "read_only": 1
+ },
+ {
+ "depends_on": "eval:!doc.apply_to_all_doctypes",
+ "fieldname": "condition",
+ "fieldtype": "Code",
+ "label": "Applicable Condition"
+ },
+ {
+ "default": "0",
+ "fieldname": "apply_to_all_doctypes",
+ "fieldtype": "Check",
+ "label": "Apply to All Inventory Document Types"
+ },
+ {
+ "default": "0",
+ "fieldname": "disabled",
+ "fieldtype": "Check",
+ "label": "Disabled"
+ },
+ {
+ "fieldname": "section_break_7",
+ "fieldtype": "Section Break"
+ },
+ {
+ "fieldname": "target_fieldname",
+ "fieldtype": "Data",
+ "label": "Target Fieldname (Stock Ledger Entry)",
+ "read_only": 1
+ },
+ {
+ "fieldname": "source_fieldname",
+ "fieldtype": "Data",
+ "label": "Source Fieldname",
+ "read_only": 1
+ },
+ {
+ "collapsible": 1,
+ "fieldname": "field_mapping_section",
+ "fieldtype": "Section Break",
+ "label": "Field Mapping"
+ },
+ {
+ "fieldname": "column_break_16",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "type_of_transaction",
+ "fieldtype": "Select",
+ "label": "Type of Transaction",
+ "options": "\nInward\nOutward"
+ },
+ {
+ "fieldname": "html_19",
+ "fieldtype": "HTML",
+ "options": "<table class=\"table table-bordered table-condensed\">\n<thead>\n <tr>\n <th class=\"table-sr\" style=\"width: 50%;\">Child Document</th>\n <th class=\"table-sr\" style=\"width: 50%;\">Non Child Document</th>\n </tr>\n</thead>\n<tbody>\n<tr>\n <td>\n <p> To access parent document field use parent.fieldname and to access child table document field use doc.fieldname </p>\n\n </td>\n <td>\n <p>To access document field use doc.fieldname </p>\n </td>\n</tr>\n<tr>\n <td>\n <p><b>Example: </b> parent.doctype == \"Stock Entry\" and doc.item_code == \"Test\" </p>\n\n </td>\n <td>\n <p><b>Example: </b> doc.doctype == \"Stock Entry\" and doc.purpose == \"Manufacture\"</p> \n </td>\n</tr>\n\n</tbody>\n</table>\n\n\n\n\n\n\n"
+ },
+ {
+ "collapsible": 1,
+ "depends_on": "eval:!doc.apply_to_all_doctypes",
+ "fieldname": "applicable_condition_example_section",
+ "fieldtype": "Section Break",
+ "label": "Applicable Condition Examples"
+ },
+ {
+ "fieldname": "column_break_4",
+ "fieldtype": "Column Break"
+ }
+ ],
+ "index_web_pages_for_search": 1,
+ "links": [],
+ "modified": "2022-07-19 21:06:11.824976",
+ "modified_by": "Administrator",
+ "module": "Stock",
+ "name": "Inventory Dimension",
+ "naming_rule": "By fieldname",
+ "owner": "Administrator",
+ "permissions": [
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "System Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Stock Manager",
+ "share": 1,
+ "write": 1
+ },
+ {
+ "create": 1,
+ "delete": 1,
+ "email": 1,
+ "export": 1,
+ "print": 1,
+ "read": 1,
+ "report": 1,
+ "role": "Stock User",
+ "share": 1,
+ "write": 1
+ }
+ ],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "states": []
+}
\ No newline at end of file
diff --git a/erpnext/stock/doctype/inventory_dimension/inventory_dimension.py b/erpnext/stock/doctype/inventory_dimension/inventory_dimension.py
new file mode 100644
index 0000000..5a9541f
--- /dev/null
+++ b/erpnext/stock/doctype/inventory_dimension/inventory_dimension.py
@@ -0,0 +1,196 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe import _, bold, scrub
+from frappe.custom.doctype.custom_field.custom_field import create_custom_fields
+from frappe.model.document import Document
+
+
+class DoNotChangeError(frappe.ValidationError):
+ pass
+
+
+class CanNotBeChildDoc(frappe.ValidationError):
+ pass
+
+
+class CanNotBeDefaultDimension(frappe.ValidationError):
+ pass
+
+
+class InventoryDimension(Document):
+ def onload(self):
+ if not self.is_new() and frappe.db.has_column("Stock Ledger Entry", self.target_fieldname):
+ self.set_onload("has_stock_ledger", self.has_stock_ledger())
+
+ def has_stock_ledger(self) -> str:
+ if not self.target_fieldname:
+ return
+
+ return frappe.get_all(
+ "Stock Ledger Entry", filters={self.target_fieldname: ("is", "set"), "is_cancelled": 0}, limit=1
+ )
+
+ def validate(self):
+ self.do_not_update_document()
+ self.reset_value()
+ self.validate_reference_document()
+ self.set_source_and_target_fieldname()
+
+ def do_not_update_document(self):
+ if self.is_new() or not self.has_stock_ledger():
+ return
+
+ old_doc = self._doc_before_save
+ for field in frappe.get_meta("Inventory Dimension").fields:
+ if field.fieldname != "disabled" and old_doc.get(field.fieldname) != self.get(field.fieldname):
+ msg = f"""The user can not change value of the field {bold(field.label)} because
+ stock transactions exists against the dimension {bold(self.name)}."""
+
+ frappe.throw(_(msg), DoNotChangeError)
+
+ def reset_value(self):
+ if self.apply_to_all_doctypes:
+ self.istable = 0
+ for field in ["document_type", "condition"]:
+ self.set(field, None)
+
+ def validate_reference_document(self):
+ if frappe.get_cached_value("DocType", self.reference_document, "istable") == 1:
+ msg = f"The reference document {self.reference_document} can not be child table."
+ frappe.throw(_(msg), CanNotBeChildDoc)
+
+ if self.reference_document in ["Batch", "Serial No", "Warehouse", "Item"]:
+ msg = f"The reference document {self.reference_document} can not be an Inventory Dimension."
+ frappe.throw(_(msg), CanNotBeDefaultDimension)
+
+ def set_source_and_target_fieldname(self) -> None:
+ if not self.source_fieldname:
+ self.source_fieldname = scrub(self.dimension_name)
+
+ if not self.target_fieldname:
+ self.target_fieldname = scrub(self.reference_document)
+
+ def on_update(self):
+ self.add_custom_fields()
+
+ def add_custom_fields(self):
+ dimension_field = dict(
+ fieldname=self.source_fieldname,
+ fieldtype="Link",
+ insert_after="warehouse",
+ options=self.reference_document,
+ label=self.dimension_name,
+ )
+
+ custom_fields = {}
+
+ if self.apply_to_all_doctypes:
+ for doctype in get_inventory_documents():
+ if not frappe.db.get_value(
+ "Custom Field", {"dt": doctype[0], "fieldname": self.source_fieldname}
+ ):
+ custom_fields.setdefault(doctype[0], dimension_field)
+ elif not frappe.db.get_value(
+ "Custom Field", {"dt": self.document_type, "fieldname": self.source_fieldname}
+ ):
+ custom_fields.setdefault(self.document_type, dimension_field)
+
+ if not frappe.db.get_value(
+ "Custom Field", {"dt": "Stock Ledger Entry", "fieldname": self.target_fieldname}
+ ):
+ dimension_field["fieldname"] = self.target_fieldname
+ custom_fields["Stock Ledger Entry"] = dimension_field
+
+ create_custom_fields(custom_fields)
+
+
+@frappe.whitelist()
+def get_inventory_documents(
+ doctype=None, txt=None, searchfield=None, start=None, page_len=None, filters=None
+):
+ and_filters = [["DocField", "parent", "not in", ["Batch", "Serial No"]]]
+ or_filters = [
+ ["DocField", "options", "in", ["Batch", "Serial No"]],
+ ["DocField", "parent", "in", ["Putaway Rule"]],
+ ]
+
+ if txt:
+ and_filters.append(["DocField", "parent", "like", f"%{txt}%"])
+
+ return frappe.get_all(
+ "DocField",
+ fields=["distinct parent"],
+ filters=and_filters,
+ or_filters=or_filters,
+ start=start,
+ page_length=page_len,
+ as_list=1,
+ )
+
+
+def get_evaluated_inventory_dimension(doc, sl_dict, parent_doc=None):
+ dimensions = get_document_wise_inventory_dimensions(doc.doctype)
+ filter_dimensions = []
+ for row in dimensions:
+ if (
+ row.type_of_transaction == "Inward"
+ if doc.docstatus == 1
+ else row.type_of_transaction != "Inward"
+ ) and sl_dict.actual_qty < 0:
+ continue
+ elif (
+ row.type_of_transaction == "Outward"
+ if doc.docstatus == 1
+ else row.type_of_transaction != "Inward"
+ ) and sl_dict.actual_qty > 0:
+ continue
+
+ evals = {"doc": doc}
+ if parent_doc:
+ evals["parent"] = parent_doc
+
+ if row.condition and frappe.safe_eval(row.condition, evals):
+ filter_dimensions.append(row)
+ else:
+ filter_dimensions.append(row)
+
+ return filter_dimensions
+
+
+def get_document_wise_inventory_dimensions(doctype) -> dict:
+ if not hasattr(frappe.local, "document_wise_inventory_dimensions"):
+ frappe.local.document_wise_inventory_dimensions = {}
+
+ if not frappe.local.document_wise_inventory_dimensions.get(doctype):
+ dimensions = frappe.get_all(
+ "Inventory Dimension",
+ fields=["name", "source_fieldname", "condition", "target_fieldname", "type_of_transaction"],
+ filters={"disabled": 0},
+ or_filters={"document_type": doctype, "apply_to_all_doctypes": 1},
+ )
+
+ frappe.local.document_wise_inventory_dimensions[doctype] = dimensions
+
+ return frappe.local.document_wise_inventory_dimensions[doctype]
+
+
+@frappe.whitelist()
+def get_inventory_dimensions():
+ if not hasattr(frappe.local, "inventory_dimensions"):
+ frappe.local.inventory_dimensions = {}
+
+ if not frappe.local.inventory_dimensions:
+ dimensions = frappe.get_all(
+ "Inventory Dimension",
+ fields=[
+ "distinct target_fieldname as fieldname",
+ "reference_document as doctype",
+ ],
+ filters={"disabled": 0},
+ )
+
+ frappe.local.inventory_dimensions = dimensions
+
+ return frappe.local.inventory_dimensions
diff --git a/erpnext/stock/doctype/inventory_dimension/test_inventory_dimension.py b/erpnext/stock/doctype/inventory_dimension/test_inventory_dimension.py
new file mode 100644
index 0000000..998a0e9
--- /dev/null
+++ b/erpnext/stock/doctype/inventory_dimension/test_inventory_dimension.py
@@ -0,0 +1,149 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+
+from erpnext.stock.doctype.inventory_dimension.inventory_dimension import (
+ CanNotBeChildDoc,
+ CanNotBeDefaultDimension,
+ DoNotChangeError,
+)
+from erpnext.stock.doctype.stock_entry.stock_entry_utils import make_stock_entry
+from erpnext.stock.doctype.warehouse.test_warehouse import create_warehouse
+
+
+class TestInventoryDimension(FrappeTestCase):
+ def setUp(self):
+ prepare_test_data()
+
+ def test_validate_inventory_dimension(self):
+ # Can not be child doc
+ inv_dim1 = create_inventory_dimension(
+ reference_document="Stock Entry Detail",
+ type_of_transaction="Outward",
+ dimension_name="Stock Entry",
+ apply_to_all_doctypes=0,
+ istable=0,
+ document_type="Stock Entry",
+ do_not_save=True,
+ )
+
+ self.assertRaises(CanNotBeChildDoc, inv_dim1.insert)
+
+ inv_dim1 = create_inventory_dimension(
+ reference_document="Batch",
+ type_of_transaction="Outward",
+ dimension_name="Batch",
+ apply_to_all_doctypes=0,
+ document_type="Stock Entry Detail",
+ do_not_save=True,
+ )
+
+ self.assertRaises(CanNotBeDefaultDimension, inv_dim1.insert)
+
+ def test_inventory_dimension(self):
+ warehouse = "Shelf Warehouse - _TC"
+ item_code = "_Test Item"
+
+ inv_dim1 = create_inventory_dimension(
+ reference_document="Shelf",
+ type_of_transaction="Outward",
+ dimension_name="Shelf",
+ apply_to_all_doctypes=0,
+ document_type="Stock Entry Detail",
+ condition="parent.purpose == 'Material Issue'",
+ )
+
+ create_inventory_dimension(
+ reference_document="Shelf",
+ type_of_transaction="Inward",
+ dimension_name="To Shelf",
+ apply_to_all_doctypes=0,
+ document_type="Stock Entry Detail",
+ condition="parent.purpose == 'Material Receipt'",
+ )
+
+ inward = make_stock_entry(
+ item_code=item_code,
+ target=warehouse,
+ qty=5,
+ basic_rate=10,
+ do_not_save=True,
+ purpose="Material Receipt",
+ )
+
+ inward.items[0].to_shelf = "Shelf 1"
+ inward.save()
+ inward.submit()
+ inward.load_from_db()
+
+ sle_data = frappe.db.get_value(
+ "Stock Ledger Entry", {"voucher_no": inward.name}, ["shelf", "warehouse"], as_dict=1
+ )
+
+ self.assertEqual(inward.items[0].to_shelf, "Shelf 1")
+ self.assertEqual(sle_data.warehouse, warehouse)
+ self.assertEqual(sle_data.shelf, "Shelf 1")
+
+ outward = make_stock_entry(
+ item_code=item_code,
+ source=warehouse,
+ qty=3,
+ basic_rate=10,
+ do_not_save=True,
+ purpose="Material Issue",
+ )
+
+ outward.items[0].shelf = "Shelf 1"
+ outward.save()
+ outward.submit()
+ outward.load_from_db()
+
+ sle_shelf = frappe.db.get_value("Stock Ledger Entry", {"voucher_no": outward.name}, "shelf")
+ self.assertEqual(sle_shelf, "Shelf 1")
+
+ inv_dim1.load_from_db()
+ inv_dim1.apply_to_all_doctypes = 1
+
+ self.assertTrue(inv_dim1.has_stock_ledger())
+ self.assertRaises(DoNotChangeError, inv_dim1.save)
+
+
+def prepare_test_data():
+ if not frappe.db.exists("DocType", "Shelf"):
+ frappe.get_doc(
+ {
+ "doctype": "DocType",
+ "name": "Shelf",
+ "module": "Stock",
+ "custom": 1,
+ "naming_rule": "By fieldname",
+ "autoname": "field:shelf_name",
+ "fields": [{"label": "Shelf Name", "fieldname": "shelf_name", "fieldtype": "Data"}],
+ "permissions": [
+ {"role": "System Manager", "permlevel": 0, "read": 1, "write": 1, "create": 1, "delete": 1}
+ ],
+ }
+ ).insert(ignore_permissions=True)
+
+ for shelf in ["Shelf 1", "Shelf 2"]:
+ if not frappe.db.exists("Shelf", shelf):
+ frappe.get_doc({"doctype": "Shelf", "shelf_name": shelf}).insert(ignore_permissions=True)
+
+ create_warehouse("Shelf Warehouse")
+
+
+def create_inventory_dimension(**args):
+ args = frappe._dict(args)
+
+ if frappe.db.exists("Inventory Dimension", args.dimension_name):
+ return frappe.get_doc("Inventory Dimension", args.dimension_name)
+
+ doc = frappe.new_doc("Inventory Dimension")
+ doc.update(args)
+
+ if not args.do_not_save:
+ doc.insert(ignore_permissions=True)
+
+ return doc
diff --git a/erpnext/stock/doctype/stock_entry/stock_entry.py b/erpnext/stock/doctype/stock_entry/stock_entry.py
index 9c49408..f719c1e 100644
--- a/erpnext/stock/doctype/stock_entry/stock_entry.py
+++ b/erpnext/stock/doctype/stock_entry/stock_entry.py
@@ -478,10 +478,10 @@
if not d.s_warehouse:
frappe.throw(_("Source warehouse is mandatory for row {0}").format(d.idx))
- if (
- cstr(d.s_warehouse) == cstr(d.t_warehouse)
- and not self.purpose == "Material Transfer for Manufacture"
- ):
+ if cstr(d.s_warehouse) == cstr(d.t_warehouse) and self.purpose not in [
+ "Material Transfer for Manufacture",
+ "Material Transfer",
+ ]:
frappe.throw(_("Source and target warehouse cannot be same for row {0}").format(d.idx))
if not (d.s_warehouse or d.t_warehouse):
diff --git a/erpnext/stock/report/stock_balance/stock_balance.js b/erpnext/stock/report/stock_balance/stock_balance.js
index ce6ffa0..9b3965d 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.js
+++ b/erpnext/stock/report/stock_balance/stock_balance.js
@@ -102,3 +102,5 @@
return value;
}
};
+
+erpnext.utils.add_inventory_dimensions('Stock Balance', 8);
\ 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 6369f91..679d234 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -13,6 +13,7 @@
from pypika.terms import ExistsCriterion
import erpnext
+from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.report.stock_ageing.stock_ageing import FIFOSlots, get_average_age
from erpnext.stock.utils import add_additional_uom_columns, is_reposting_item_valuation_in_progress
@@ -66,9 +67,14 @@
_func = itemgetter(1)
to_date = filters.get("to_date")
- for (company, item, warehouse) in sorted(iwb_map):
+
+ for group_by_key in iwb_map:
+ item = group_by_key[1]
+ warehouse = group_by_key[2]
+ company = group_by_key[0]
+
if item_map.get(item):
- qty_dict = iwb_map[(company, item, warehouse)]
+ qty_dict = iwb_map[group_by_key]
item_reorder_level = 0
item_reorder_qty = 0
if item + warehouse in item_reorder_detail_map:
@@ -135,88 +141,104 @@
"options": "Warehouse",
"width": 100,
},
- {
- "label": _("Stock UOM"),
- "fieldname": "stock_uom",
- "fieldtype": "Link",
- "options": "UOM",
- "width": 90,
- },
- {
- "label": _("Balance Qty"),
- "fieldname": "bal_qty",
- "fieldtype": "Float",
- "width": 100,
- "convertible": "qty",
- },
- {
- "label": _("Balance Value"),
- "fieldname": "bal_val",
- "fieldtype": "Currency",
- "width": 100,
- "options": "currency",
- },
- {
- "label": _("Opening Qty"),
- "fieldname": "opening_qty",
- "fieldtype": "Float",
- "width": 100,
- "convertible": "qty",
- },
- {
- "label": _("Opening Value"),
- "fieldname": "opening_val",
- "fieldtype": "Currency",
- "width": 110,
- "options": "currency",
- },
- {
- "label": _("In Qty"),
- "fieldname": "in_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {"label": _("In Value"), "fieldname": "in_val", "fieldtype": "Float", "width": 80},
- {
- "label": _("Out Qty"),
- "fieldname": "out_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {"label": _("Out Value"), "fieldname": "out_val", "fieldtype": "Float", "width": 80},
- {
- "label": _("Valuation Rate"),
- "fieldname": "val_rate",
- "fieldtype": "Currency",
- "width": 90,
- "convertible": "rate",
- "options": "currency",
- },
- {
- "label": _("Reorder Level"),
- "fieldname": "reorder_level",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {
- "label": _("Reorder Qty"),
- "fieldname": "reorder_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {
- "label": _("Company"),
- "fieldname": "company",
- "fieldtype": "Link",
- "options": "Company",
- "width": 100,
- },
]
+ for dimension in get_inventory_dimensions():
+ columns.append(
+ {
+ "label": _(dimension.doctype),
+ "fieldname": dimension.fieldname,
+ "fieldtype": "Link",
+ "options": dimension.doctype,
+ "width": 110,
+ }
+ )
+
+ columns.extend(
+ [
+ {
+ "label": _("Stock UOM"),
+ "fieldname": "stock_uom",
+ "fieldtype": "Link",
+ "options": "UOM",
+ "width": 90,
+ },
+ {
+ "label": _("Balance Qty"),
+ "fieldname": "bal_qty",
+ "fieldtype": "Float",
+ "width": 100,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Balance Value"),
+ "fieldname": "bal_val",
+ "fieldtype": "Currency",
+ "width": 100,
+ "options": "currency",
+ },
+ {
+ "label": _("Opening Qty"),
+ "fieldname": "opening_qty",
+ "fieldtype": "Float",
+ "width": 100,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Opening Value"),
+ "fieldname": "opening_val",
+ "fieldtype": "Currency",
+ "width": 110,
+ "options": "currency",
+ },
+ {
+ "label": _("In Qty"),
+ "fieldname": "in_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {"label": _("In Value"), "fieldname": "in_val", "fieldtype": "Float", "width": 80},
+ {
+ "label": _("Out Qty"),
+ "fieldname": "out_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {"label": _("Out Value"), "fieldname": "out_val", "fieldtype": "Float", "width": 80},
+ {
+ "label": _("Valuation Rate"),
+ "fieldname": "val_rate",
+ "fieldtype": "Currency",
+ "width": 90,
+ "convertible": "rate",
+ "options": "currency",
+ },
+ {
+ "label": _("Reorder Level"),
+ "fieldname": "reorder_level",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Reorder Qty"),
+ "fieldname": "reorder_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 100,
+ },
+ ]
+ )
+
if filters.get("show_stock_ageing_data"):
columns += [
{"label": _("Average Age"), "fieldname": "average_age", "width": 100},
@@ -296,6 +318,13 @@
.orderby(sle.actual_qty)
)
+ inventory_dimension_fields = get_inventory_dimension_fields()
+ if inventory_dimension_fields:
+ for fieldname in inventory_dimension_fields:
+ query = query.select(fieldname)
+ if fieldname in filters and filters.get(fieldname):
+ query = query.where(sle[fieldname].isin(filters.get(fieldname)))
+
if items:
query = query.where(sle.item_code.isin(items))
@@ -303,6 +332,10 @@
return query.run(as_dict=True)
+def get_inventory_dimension_fields():
+ return [dimension.fieldname for dimension in get_inventory_dimensions()]
+
+
def get_item_warehouse_map(filters: StockBalanceFilter, sle: List[SLEntry]):
iwb_map = {}
from_date = getdate(filters.get("from_date"))
@@ -310,10 +343,12 @@
float_precision = cint(frappe.db.get_default("float_precision")) or 3
+ inventory_dimensions = get_inventory_dimension_fields()
+
for d in sle:
- key = (d.company, d.item_code, d.warehouse)
- if key not in iwb_map:
- iwb_map[key] = frappe._dict(
+ group_by_key = get_group_by_key(d, filters, inventory_dimensions)
+ if group_by_key not in iwb_map:
+ iwb_map[group_by_key] = frappe._dict(
{
"opening_qty": 0.0,
"opening_val": 0.0,
@@ -327,7 +362,9 @@
}
)
- qty_dict = iwb_map[(d.company, d.item_code, d.warehouse)]
+ qty_dict = iwb_map[group_by_key]
+ for field in inventory_dimensions:
+ qty_dict[field] = d.get(field)
if d.voucher_type == "Stock Reconciliation" and not d.batch_no:
qty_diff = flt(d.qty_after_transaction) - flt(qty_dict.bal_qty)
@@ -356,24 +393,41 @@
qty_dict.bal_qty += qty_diff
qty_dict.bal_val += value_diff
- iwb_map = filter_items_with_no_transactions(iwb_map, float_precision)
+ iwb_map = filter_items_with_no_transactions(iwb_map, float_precision, inventory_dimensions)
return iwb_map
-def filter_items_with_no_transactions(iwb_map, float_precision: float):
- for (company, item, warehouse) in sorted(iwb_map):
- qty_dict = iwb_map[(company, item, warehouse)]
+def get_group_by_key(row, filters, inventory_dimension_fields) -> tuple:
+ group_by_key = [row.company, row.item_code, row.warehouse]
+
+ for fieldname in inventory_dimension_fields:
+ if filters.get(fieldname):
+ group_by_key.append(row.get(fieldname))
+
+ return tuple(group_by_key)
+
+
+def filter_items_with_no_transactions(iwb_map, float_precision: float, inventory_dimensions: list):
+ pop_keys = []
+ for group_by_key in iwb_map:
+ qty_dict = iwb_map[group_by_key]
no_transactions = True
for key, val in qty_dict.items():
+ if key in inventory_dimensions:
+ continue
+
val = flt(val, float_precision)
qty_dict[key] = val
if key != "val_rate" and val:
no_transactions = False
if no_transactions:
- iwb_map.pop((company, item, warehouse))
+ pop_keys.append(group_by_key)
+
+ for key in pop_keys:
+ iwb_map.pop(key)
return iwb_map
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.js b/erpnext/stock/report/stock_ledger/stock_ledger.js
index ef7c2cc..0def161 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.js
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.js
@@ -95,4 +95,6 @@
return value;
},
-}
+};
+
+erpnext.utils.add_inventory_dimensions('Stock Ledger', 10);
\ No newline at end of file
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index ef1642e..e18d4c7 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -4,8 +4,11 @@
import frappe
from frappe import _
+from frappe.query_builder.functions import CombineDatetime
from frappe.utils import cint, flt
+from pypika.terms import ExistsCriterion
+from erpnext.stock.doctype.inventory_dimension.inventory_dimension import get_inventory_dimensions
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos
from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import get_stock_balance_for
from erpnext.stock.utils import (
@@ -17,7 +20,7 @@
def execute(filters=None):
is_reposting_item_valuation_in_progress()
include_uom = filters.get("include_uom")
- columns = get_columns()
+ columns = get_columns(filters)
items = get_items(filters)
sl_entries = get_stock_ledger_entries(filters, items)
item_details = get_item_details(items, sl_entries, include_uom)
@@ -33,12 +36,14 @@
actual_qty = stock_value = 0
available_serial_nos = {}
+ inventory_dimension_filters_applied = check_inventory_dimension_filters_applied(filters)
+
for sle in sl_entries:
item_detail = item_details[sle.item_code]
sle.update(item_detail)
- if filters.get("batch_no"):
+ if filters.get("batch_no") or inventory_dimension_filters_applied:
actual_qty += flt(sle.actual_qty, precision)
stock_value += sle.stock_value_difference
@@ -67,7 +72,7 @@
key = (sle.item_code, sle.warehouse)
if key not in available_serial_nos:
stock_balance = get_stock_balance_for(
- sle.item_code, sle.warehouse, sle.date.split(" ")[0], sle.date.split(" ")[1]
+ sle.item_code, sle.warehouse, sle.posting_date, sle.posting_time
)
serials = get_serial_nos(stock_balance["serial_nos"]) if stock_balance["serial_nos"] else []
available_serial_nos.setdefault(key, serials)
@@ -88,7 +93,7 @@
sle.balance_serial_no = "\n".join(existing_serial_no)
-def get_columns():
+def get_columns(filters):
columns = [
{"label": _("Date"), "fieldname": "date", "fieldtype": "Datetime", "width": 150},
{
@@ -106,170 +111,210 @@
"options": "UOM",
"width": 90,
},
- {
- "label": _("In Qty"),
- "fieldname": "in_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {
- "label": _("Out Qty"),
- "fieldname": "out_qty",
- "fieldtype": "Float",
- "width": 80,
- "convertible": "qty",
- },
- {
- "label": _("Balance Qty"),
- "fieldname": "qty_after_transaction",
- "fieldtype": "Float",
- "width": 100,
- "convertible": "qty",
- },
- {
- "label": _("Voucher #"),
- "fieldname": "voucher_no",
- "fieldtype": "Dynamic Link",
- "options": "voucher_type",
- "width": 150,
- },
- {
- "label": _("Warehouse"),
- "fieldname": "warehouse",
- "fieldtype": "Link",
- "options": "Warehouse",
- "width": 150,
- },
- {
- "label": _("Item Group"),
- "fieldname": "item_group",
- "fieldtype": "Link",
- "options": "Item Group",
- "width": 100,
- },
- {
- "label": _("Brand"),
- "fieldname": "brand",
- "fieldtype": "Link",
- "options": "Brand",
- "width": 100,
- },
- {"label": _("Description"), "fieldname": "description", "width": 200},
- {
- "label": _("Incoming Rate"),
- "fieldname": "incoming_rate",
- "fieldtype": "Currency",
- "width": 110,
- "options": "Company:company:default_currency",
- "convertible": "rate",
- },
- {
- "label": _("Valuation Rate"),
- "fieldname": "valuation_rate",
- "fieldtype": "Currency",
- "width": 110,
- "options": "Company:company:default_currency",
- "convertible": "rate",
- },
- {
- "label": _("Balance Value"),
- "fieldname": "stock_value",
- "fieldtype": "Currency",
- "width": 110,
- "options": "Company:company:default_currency",
- },
- {
- "label": _("Value Change"),
- "fieldname": "stock_value_difference",
- "fieldtype": "Currency",
- "width": 110,
- "options": "Company:company:default_currency",
- },
- {"label": _("Voucher Type"), "fieldname": "voucher_type", "width": 110},
- {
- "label": _("Voucher #"),
- "fieldname": "voucher_no",
- "fieldtype": "Dynamic Link",
- "options": "voucher_type",
- "width": 100,
- },
- {
- "label": _("Batch"),
- "fieldname": "batch_no",
- "fieldtype": "Link",
- "options": "Batch",
- "width": 100,
- },
- {
- "label": _("Serial No"),
- "fieldname": "serial_no",
- "fieldtype": "Link",
- "options": "Serial No",
- "width": 100,
- },
- {"label": _("Balance Serial No"), "fieldname": "balance_serial_no", "width": 100},
- {
- "label": _("Project"),
- "fieldname": "project",
- "fieldtype": "Link",
- "options": "Project",
- "width": 100,
- },
- {
- "label": _("Company"),
- "fieldname": "company",
- "fieldtype": "Link",
- "options": "Company",
- "width": 110,
- },
]
+ for dimension in get_inventory_dimensions():
+ columns.append(
+ {
+ "label": _(dimension.doctype),
+ "fieldname": dimension.fieldname,
+ "fieldtype": "Link",
+ "options": dimension.doctype,
+ "width": 110,
+ }
+ )
+
+ columns.extend(
+ [
+ {
+ "label": _("In Qty"),
+ "fieldname": "in_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Out Qty"),
+ "fieldname": "out_qty",
+ "fieldtype": "Float",
+ "width": 80,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Balance Qty"),
+ "fieldname": "qty_after_transaction",
+ "fieldtype": "Float",
+ "width": 100,
+ "convertible": "qty",
+ },
+ {
+ "label": _("Voucher #"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": 150,
+ },
+ {
+ "label": _("Warehouse"),
+ "fieldname": "warehouse",
+ "fieldtype": "Link",
+ "options": "Warehouse",
+ "width": 150,
+ },
+ {
+ "label": _("Item Group"),
+ "fieldname": "item_group",
+ "fieldtype": "Link",
+ "options": "Item Group",
+ "width": 100,
+ },
+ {
+ "label": _("Brand"),
+ "fieldname": "brand",
+ "fieldtype": "Link",
+ "options": "Brand",
+ "width": 100,
+ },
+ {"label": _("Description"), "fieldname": "description", "width": 200},
+ {
+ "label": _("Incoming Rate"),
+ "fieldname": "incoming_rate",
+ "fieldtype": "Currency",
+ "width": 110,
+ "options": "Company:company:default_currency",
+ "convertible": "rate",
+ },
+ {
+ "label": _("Valuation Rate"),
+ "fieldname": "valuation_rate",
+ "fieldtype": "Currency",
+ "width": 110,
+ "options": "Company:company:default_currency",
+ "convertible": "rate",
+ },
+ {
+ "label": _("Balance Value"),
+ "fieldname": "stock_value",
+ "fieldtype": "Currency",
+ "width": 110,
+ "options": "Company:company:default_currency",
+ },
+ {
+ "label": _("Value Change"),
+ "fieldname": "stock_value_difference",
+ "fieldtype": "Currency",
+ "width": 110,
+ "options": "Company:company:default_currency",
+ },
+ {"label": _("Voucher Type"), "fieldname": "voucher_type", "width": 110},
+ {
+ "label": _("Voucher #"),
+ "fieldname": "voucher_no",
+ "fieldtype": "Dynamic Link",
+ "options": "voucher_type",
+ "width": 100,
+ },
+ {
+ "label": _("Batch"),
+ "fieldname": "batch_no",
+ "fieldtype": "Link",
+ "options": "Batch",
+ "width": 100,
+ },
+ {
+ "label": _("Serial No"),
+ "fieldname": "serial_no",
+ "fieldtype": "Link",
+ "options": "Serial No",
+ "width": 100,
+ },
+ {"label": _("Balance Serial No"), "fieldname": "balance_serial_no", "width": 100},
+ {
+ "label": _("Project"),
+ "fieldname": "project",
+ "fieldtype": "Link",
+ "options": "Project",
+ "width": 100,
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 110,
+ },
+ ]
+ )
+
return columns
def get_stock_ledger_entries(filters, items):
- item_conditions_sql = ""
- if items:
- item_conditions_sql = "and sle.item_code in ({})".format(
- ", ".join(frappe.db.escape(i) for i in items)
+ sle = frappe.qb.DocType("Stock Ledger Entry")
+ query = (
+ frappe.qb.from_(sle)
+ .select(
+ sle.item_code,
+ CombineDatetime(sle.posting_date, sle.posting_time).as_("date"),
+ sle.warehouse,
+ sle.posting_date,
+ sle.posting_time,
+ sle.actual_qty,
+ sle.incoming_rate,
+ sle.valuation_rate,
+ sle.company,
+ sle.voucher_type,
+ sle.qty_after_transaction,
+ sle.stock_value_difference,
+ sle.voucher_no,
+ sle.stock_value,
+ sle.batch_no,
+ sle.serial_no,
+ sle.project,
)
-
- sl_entries = frappe.db.sql(
- """
- SELECT
- concat_ws(' ', posting_date, posting_time) AS date,
- item_code,
- warehouse,
- actual_qty,
- qty_after_transaction,
- incoming_rate,
- valuation_rate,
- stock_value,
- voucher_type,
- voucher_no,
- batch_no,
- serial_no,
- company,
- project,
- stock_value_difference
- FROM
- `tabStock Ledger Entry` sle
- WHERE
- company = %(company)s
- AND is_cancelled = 0 AND posting_date BETWEEN %(from_date)s AND %(to_date)s
- {sle_conditions}
- {item_conditions_sql}
- ORDER BY
- posting_date asc, posting_time asc, creation asc
- """.format(
- sle_conditions=get_sle_conditions(filters), item_conditions_sql=item_conditions_sql
- ),
- filters,
- as_dict=1,
+ .where(
+ (sle.docstatus < 2)
+ & (sle.is_cancelled == 0)
+ & (sle.posting_date[filters.from_date : filters.to_date])
+ )
+ .orderby(CombineDatetime(sle.posting_date, sle.posting_time))
+ .orderby(sle.creation)
)
- return sl_entries
+ inventory_dimension_fields = get_inventory_dimension_fields()
+ if inventory_dimension_fields:
+ for fieldname in inventory_dimension_fields:
+ query = query.select(fieldname)
+ if fieldname in filters and filters.get(fieldname):
+ query = query.where(sle[fieldname].isin(filters.get(fieldname)))
+
+ if items:
+ query = query.where(sle.item_code.isin(items))
+
+ for field in ["voucher_no", "batch_no", "project", "company"]:
+ if filters.get(field):
+ query = query.where(sle[field] == filters.get(field))
+
+ if warehouse := filters.get("warehouse"):
+ lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
+
+ warehouse_table = frappe.qb.DocType("Warehouse")
+ chilren_subquery = (
+ frappe.qb.from_(warehouse_table)
+ .select(warehouse_table.name)
+ .where(
+ (warehouse_table.lft >= lft)
+ & (warehouse_table.rgt <= rgt)
+ & (warehouse_table.name == sle.warehouse)
+ )
+ )
+ query = query.where(ExistsCriterion(chilren_subquery))
+
+ return query.run(as_dict=True)
+
+
+def get_inventory_dimension_fields():
+ return [dimension.fieldname for dimension in get_inventory_dimensions()]
def get_items(filters):
@@ -341,6 +386,10 @@
if filters.get("project"):
conditions.append("project=%(project)s")
+ for dimension in get_inventory_dimensions():
+ if filters.get(dimension.fieldname):
+ conditions.append(f"{dimension.fieldname} in %({dimension.fieldname})s")
+
return "and {}".format(" and ".join(conditions)) if conditions else ""
@@ -363,7 +412,7 @@
for sle in sl_entries:
if (
sle.get("voucher_type") == "Stock Reconciliation"
- and sle.get("date").split()[0] == filters.from_date
+ and sle.posting_date == filters.from_date
and frappe.db.get_value("Stock Reconciliation", sle.voucher_no, "purpose") == "Opening Stock"
):
last_entry = sle
@@ -401,3 +450,11 @@
)
return ""
+
+
+def check_inventory_dimension_filters_applied(filters) -> bool:
+ for dimension in get_inventory_dimensions():
+ if dimension.fieldname in filters and filters.get(dimension.fieldname):
+ return True
+
+ return False