feat(stock-reco): Fetch items in stock reco based on group warehouse
diff --git a/erpnext/regional/india/utils.py b/erpnext/regional/india/utils.py
index 78aeee7..a1fba07 100644
--- a/erpnext/regional/india/utils.py
+++ b/erpnext/regional/india/utils.py
@@ -11,7 +11,7 @@
if not hasattr(doc, 'gstin'):
return
- doc.gstin = doc.gstin.upper().strip()
+ doc.gstin = doc.gstin.upper().strip() if doc.gstin else ""
if not doc.gstin or doc.gstin == 'NA':
return
diff --git a/erpnext/stock/doctype/item/test_item.py b/erpnext/stock/doctype/item/test_item.py
index abdd676..d02559e 100644
--- a/erpnext/stock/doctype/item/test_item.py
+++ b/erpnext/stock/doctype/item/test_item.py
@@ -378,7 +378,7 @@
test_records = frappe.get_test_records('Item')
-def create_item(item_code, is_stock_item=None, valuation_rate=0, warehouse=None):
+def create_item(item_code, is_stock_item=None, valuation_rate=0, warehouse=None, opening_stock=None):
if not frappe.db.exists("Item", item_code):
item = frappe.new_doc("Item")
item.item_code = item_code
@@ -386,6 +386,7 @@
item.description = item_code
item.item_group = "All Item Groups"
item.is_stock_item = is_stock_item or 1
+ item.opening_stock = opening_stock or 0
item.valuation_rate = valuation_rate or 0.0
item.append("item_defaults", {
"default_warehouse": warehouse or '_Test Warehouse - _TC',
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 6b5bb28..eb60ce5 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -271,27 +271,39 @@
@frappe.whitelist()
def get_items(warehouse, posting_date, posting_time, company):
- items = frappe.db.sql('''select i.name, i.item_name from `tabItem` i, `tabBin` bin where i.name=bin.item_code
- and i.disabled=0 and bin.warehouse=%s''', (warehouse), as_dict=True)
+ lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
+ items = frappe.db.sql("""
+ select i.name, i.item_name, bin.warehouse
+ from tabBin bin, tabItem i
+ where i.name=bin.item_code and i.disabled=0
+ and exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=bin.warehouse)
+ """, (lft, rgt))
- items += frappe.db.sql('''select i.name, i.item_name from `tabItem` i, `tabItem Default` id where i.name = id.parent
- and i.is_stock_item=1 and i.has_serial_no=0 and i.has_batch_no=0 and i.has_variants=0 and i.disabled=0
- and id.default_warehouse=%s and id.company=%s group by i.name''', (warehouse, company), as_dict=True)
+ items += frappe.db.sql("""
+ select i.name, i.item_name, id.default_warehouse
+ from tabItem i, `tabItem Default` id
+ where i.name = id.parent
+ and exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=id.default_warehouse)
+ and i.is_stock_item = 1 and i.has_serial_no = 0 and i.has_batch_no = 0
+ and i.has_variants = 0 and i.disabled = 0 and id.company=%s
+ group by i.name
+ """, (lft, rgt, company))
res = []
- for item in items:
- qty, rate = get_stock_balance(item.name, warehouse, posting_date, posting_time,
+ for d in set(items):
+ stock_bal = get_stock_balance(d[0], d[2], posting_date, posting_time,
with_valuation_rate=True)
- res.append({
- "item_code": item.name,
- "warehouse": warehouse,
- "qty": qty,
- "item_name": item.item_name,
- "valuation_rate": rate,
- "current_qty": qty,
- "current_valuation_rate": rate
- })
+ if frappe.db.get_value("Item", d[0], "disabled") == 0:
+ res.append({
+ "item_code": d[0],
+ "warehouse": d[2],
+ "qty": stock_bal[0],
+ "item_name": d[1],
+ "valuation_rate": stock_bal[1],
+ "current_qty": stock_bal[0],
+ "current_valuation_rate": stock_bal[1]
+ })
return res
diff --git a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index bc991b4..2dc585b 100644
--- a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -12,7 +12,7 @@
from erpnext.stock.stock_ledger import get_previous_sle, update_entries_after
from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import EmptyStockReconciliationItemsError, get_items
from erpnext.stock.doctype.warehouse.test_warehouse import create_warehouse
-from erpnext.stock.doctype.item.test_item import make_item
+from erpnext.stock.doctype.item.test_item import create_item
class TestStockReconciliation(unittest.TestCase):
def setUp(self):
@@ -83,11 +83,13 @@
def test_get_items(self):
create_warehouse("_Test Warehouse Group 1", {"is_group": 1})
- create_warehouse("_Test Warehouse Ledger 1", {"is_group": 0, "parent_warehouse": "_Test Warehouse Group 1 - _TC"})
- make_item("_Test Stock Reco Item", {"default_warehouse": "_Test Warehouse Ledger 1 - _TC",
- "is_stock_item": 1, "opening_stock": 100, "valuation_rate": 100})
+ create_warehouse("_Test Warehouse Ledger 1",
+ {"is_group": 0, "parent_warehouse": "_Test Warehouse Group 1 - _TC"})
- items = get_items("_Test Warehouse Group 1 - _TC", nowdate(), nowtime())
+ create_item("_Test Stock Reco Item", is_stock_item=1, valuation_rate=100,
+ warehouse="_Test Warehouse Ledger 1 - _TC", opening_stock=100)
+
+ items = get_items("_Test Warehouse Group 1 - _TC", nowdate(), nowtime(), "_Test Company")
self.assertEqual(["_Test Stock Reco Item", "_Test Warehouse Ledger 1 - _TC", 100],
[items[0]["item_code"], items[0]["warehouse"], items[0]["qty"]])