def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
item_map = get_item_details()
data = []
for item in sorted(item_map):
data.append([item_map[item]["item_code"], item_map[item]["actual_qty"], item_map[item]["stock_uom"], item_map[item]["warehouse"],
item_map[item]["item_name"],item_map[item]["item_group"],
item_map[item]["description"]
])
return columns, data
–
def get_item_details():
"""returns all items details"""
item_map = {}
for i in frappe.db.sql("select it.item_code, it.item_group, it.item_name, it.description, \
it.stock_uom, bin.actual_qty, bin.warehouse from tabItem it LEFT JOIN tabBin bin ON (it.item_code = bin.item_code and it.stock_uom = bin.stock_uom) \
order by it.item_code, it.item_group", as_dict=1):
item_map.setdefault(i.item_code, i)
return item_map
Above script report returned:
If I run the query from sql console then the result:
Item ‘fr’ returns 2 rows as it has 2 records in tabBin
What’s wrong in the script report?