Merge pull request #16327 from nabinhait/group_warehouse_stock_reco
Fetch items based on group warehouse in Stock Reconciliation
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 257434f..561868f 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -270,24 +270,27 @@
@frappe.whitelist()
def get_items(warehouse, posting_date, posting_time):
- items = frappe.get_list("Bin", fields=["item_code"], filters={"warehouse": warehouse}, as_list=1)
+ lft, rgt = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt"])
+ items = frappe.db.sql("""select item_code, warehouse from tabBin
+ where exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=`tabBin`.warehouse)
+ """, (lft, rgt))
- items += frappe.get_list("Item", fields=["name"], filters= {"is_stock_item": 1, "has_serial_no": 0,
- "has_batch_no": 0, "has_variants": 0, "disabled": 0, "default_warehouse": warehouse},
- as_list=1)
+ items += frappe.db.sql("""select name, default_warehouse from tabItem
+ where exists(select name from `tabWarehouse` where lft >= %s and rgt <= %s and name=`tabItem`.default_warehouse)
+ and is_stock_item = 1 and has_serial_no = 0 and has_batch_no = 0 and has_variants = 0 and disabled = 0
+ """, (lft, rgt))
res = []
- for item in set(items):
- stock_bal = get_stock_balance(item[0], warehouse, posting_date, posting_time,
+ for item, wh in set(items):
+ stock_bal = get_stock_balance(item, wh, posting_date, posting_time,
with_valuation_rate=True)
- if frappe.db.get_value("Item",item[0],"disabled") == 0:
-
+ if frappe.db.get_value("Item", item, "disabled") == 0:
res.append({
- "item_code": item[0],
- "warehouse": warehouse,
+ "item_code": item,
+ "warehouse": wh,
"qty": stock_bal[0],
- "item_name": frappe.db.get_value('Item', item[0], 'item_name'),
+ "item_name": frappe.db.get_value('Item', item, 'item_name'),
"valuation_rate": stock_bal[1],
"current_qty": stock_bal[0],
"current_valuation_rate": stock_bal[1]
diff --git a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index 212bb51..bc991b4 100644
--- a/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -10,7 +10,9 @@
from erpnext.accounts.utils import get_stock_and_account_difference
from erpnext.stock.doctype.purchase_receipt.test_purchase_receipt import set_perpetual_inventory
from erpnext.stock.stock_ledger import get_previous_sle, update_entries_after
-from erpnext.stock.doctype.stock_reconciliation.stock_reconciliation import EmptyStockReconciliationItemsError
+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
class TestStockReconciliation(unittest.TestCase):
def setUp(self):
@@ -79,6 +81,17 @@
set_perpetual_inventory(0)
+ 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})
+
+ items = get_items("_Test Warehouse Group 1 - _TC", nowdate(), nowtime())
+
+ self.assertEqual(["_Test Stock Reco Item", "_Test Warehouse Ledger 1 - _TC", 100],
+ [items[0]["item_code"], items[0]["warehouse"], items[0]["qty"]])
+
def insert_existing_sle(self):
from erpnext.stock.doctype.stock_entry.test_stock_entry import make_stock_entry
diff --git a/erpnext/stock/doctype/warehouse/test_warehouse.py b/erpnext/stock/doctype/warehouse/test_warehouse.py
index d010313..961d0a7 100644
--- a/erpnext/stock/doctype/warehouse/test_warehouse.py
+++ b/erpnext/stock/doctype/warehouse/test_warehouse.py
@@ -90,7 +90,7 @@
self.assertTrue(frappe.db.get_value("Warehouse",
filters={"account": "Test Warehouse for Merging 2 - _TC"}))
-def create_warehouse(warehouse_name):
+def create_warehouse(warehouse_name, properties=None):
if not frappe.db.exists("Warehouse", warehouse_name + " - _TC"):
w = frappe.new_doc("Warehouse")
w.warehouse_name = warehouse_name
@@ -98,11 +98,13 @@
w.company = "_Test Company"
make_account_for_warehouse(warehouse_name, w)
w.account = warehouse_name + " - _TC"
+ if properties:
+ w.update(properties)
w.save()
def make_account_for_warehouse(warehouse_name, warehouse_obj):
if not frappe.db.exists("Account", warehouse_name + " - _TC"):
- parent_account = frappe.db.get_value('Account',
+ parent_account = frappe.db.get_value('Account',
{'company': warehouse_obj.company, 'is_group':1, 'account_type': 'Stock'},'name')
account = create_account(account_name=warehouse_name, \
account_type="Stock", parent_account= parent_account, company=warehouse_obj.company)
\ No newline at end of file