[Staging] Stock Reconciliation (#15382)
* optimize query and some fixes
* add get_query to filter warehouses based on company
* minor changes
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
index ce32e01..4d34d96 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.js
@@ -42,7 +42,14 @@
},
get_items: function(frm) {
- frappe.prompt({label:"Warehouse", fieldtype:"Link", options:"Warehouse", reqd: 1},
+ frappe.prompt({label:"Warehouse", fieldtype:"Link", options:"Warehouse", reqd: 1,
+ "get_query": function() {
+ return {
+ "filters": {
+ "company": frm.doc.company,
+ }
+ }
+ }},
function(data) {
frappe.call({
method:"erpnext.stock.doctype.stock_reconciliation.stock_reconciliation.get_items",
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 58255d4..5ec1db1 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -271,28 +271,27 @@
@frappe.whitelist()
def get_items(warehouse, posting_date, posting_time, company):
- items = [d.item_code for d in frappe.get_list("Bin", fields=["item_code"], filters={"warehouse": warehouse})]
+ 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)
- items += frappe.db.sql_list('''select i.name from `tabItem` i, `tabItem Default` id where i.name = id.parent
+ 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''', (warehouse, company))
+ and id.default_warehouse=%s and id.company=%s group by i.name''', (warehouse, company), as_dict=True)
res = []
- for item in set(items):
- stock_bal = get_stock_balance(item[0], warehouse, posting_date, posting_time,
+ for item in items:
+ qty, rate = get_stock_balance(item.name, warehouse, posting_date, posting_time,
with_valuation_rate=True)
- if frappe.db.get_value("Item",item[0],"disabled") == 0:
-
- res.append({
- "item_code": item[0],
- "warehouse": warehouse,
- "qty": stock_bal[0],
- "item_name": frappe.db.get_value('Item', item[0], 'item_name'),
- "valuation_rate": stock_bal[1],
- "current_qty": stock_bal[0],
- "current_valuation_rate": stock_bal[1]
- })
+ 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
+ })
return res