Filter bin list based on warehouses that are accessible to the user
diff --git a/erpnext/stock/dashboard/item_dashboard.py b/erpnext/stock/dashboard/item_dashboard.py
index f95daaf..8762920 100644
--- a/erpnext/stock/dashboard/item_dashboard.py
+++ b/erpnext/stock/dashboard/item_dashboard.py
@@ -1,43 +1,37 @@
from __future__ import unicode_literals
import frappe
+from frappe.model.db_query import DatabaseQuery
@frappe.whitelist()
def get_data(item_code=None, warehouse=None, item_group=None,
start=0, sort_by='actual_qty', sort_order='desc'):
'''Return data to render the item dashboard'''
- conditions = []
- values = []
+ filters = []
if item_code:
- conditions.append('b.item_code=%s')
- values.append(item_code)
+ filters.append(['item_code', '=', item_code])
if warehouse:
- conditions.append('b.warehouse=%s')
- values.append(warehouse)
+ filters.append(['warehouse', '=', warehouse])
if item_group:
- conditions.append('i.item_group=%s')
- values.append(item_group)
+ filters.append(['item_group', '=', item_group])
+ try:
+ # check if user has any restrictions based on user permissions on warehouse
+ if DatabaseQuery('Warehouse', user=frappe.session.user).build_match_conditions():
+ filters.append(['warehouse', 'in', [w.name for w in frappe.get_list('Warehouse')]])
+ except frappe.PermissionError:
+ # user does not have access to warehouse
+ return []
- if conditions:
- conditions = ' and ' + ' and '.join(conditions)
- else:
- conditions = ''
-
- return frappe.db.sql('''
- select
- b.item_code, b.warehouse, b.projected_qty, b.reserved_qty,
- b.reserved_qty_for_production, b.reserved_qty_for_sub_contract, b.actual_qty, b.valuation_rate, i.item_name
- from
- tabBin b, tabItem i
- where
- b.item_code = i.name
- and
- (b.projected_qty != 0 or b.reserved_qty != 0 or b.reserved_qty_for_production != 0
- or b.reserved_qty_for_sub_contract != 0 or b.actual_qty != 0)
- {conditions}
- order by
- {sort_by} {sort_order}
- limit
- {start}, 21
- '''.format(conditions=conditions, sort_by=sort_by, sort_order=sort_order,
- start=start), values, as_dict=True)
+ return frappe.db.get_all('Bin', fields=['item_code', 'warehouse', 'projected_qty',
+ 'reserved_qty', 'reserved_qty_for_sub_contract', 'actual_qty', 'valuation_rate'],
+ or_filters={
+ 'projected_qty': ['!=', 0],
+ 'reserved_qty': ['!=', 0],
+ 'reserved_qty_for_production': ['!=', 0],
+ 'reserved_qty_for_sub_contract': ['!=', 0],
+ 'actual_qty': ['!=', 0],
+ },
+ filters=filters,
+ order_by=sort_by + ' ' + sort_order,
+ limit_start=start,
+ limit_page_length='21')
\ No newline at end of file