While creating a script report, the Report result is not displaying. filters are working,
I’m in Developer mode, Report window result is No data to display. I need a help for creating script report
Please post your code here so we can help you fix errors
Report is not triggering while refresh
from __future__ import unicode_literals
import frappe
from frappe.utils import getdate, add_days, today, cint
from frappe import _
def execute(filters=None):
columns = get_columns()
data = get_data(filters)
return columns, data
def get_columns():
columns = [
{
"fieldname": "territory",
"fieldtype": "Link",
"label": _("Territory1"),
"options": "Territory1",
"width": 150
},
{
"fieldname": "item_group",
"fieldtype": "Link",
"label": _("Item Group"),
"options": "Item Group",
"width": 150
},
{
"fieldname": "item",
"fieldtype": "Link",
"options": "Item",
"label": "Item",
"width": 150
},
{
"fieldname": "item_name",
"fieldtype": "Data",
"label": _("Item Name"),
"width": 150
},
{
"fieldname": "customer",
"fieldtype": "Link",
"label": _("Customer"),
"options": "Customer",
"width": 200
},
{
"fieldname": "last_order_date",
"fieldtype": "Date",
"label": _("Last Order Date"),
"width": 100
},
{
"fieldname": "qty",
"fieldtype": "Float",
"label": _("Quantity"),
"width": 100
},
{
"fieldname": "days_since_last_order",
"fieldtype": "Int",
"label": _("Days Since Last Order"),
"width": 100
},
]
return columns
def get_data(filters):
data = []
items = get_items(filters)
territories = get_territories(filters)
sales_invoice_data = get_sales_details(filters)
for territory in territories:
for item in items:
row = {
"territory": territory.name,
"item_group": item.item_group,
"item": item.item_code,
"item_name": item.item_name
}
if sales_invoice_data.get((territory.name,item.item_code)):
item_obj = sales_invoice_data[(territory.name,item.item_code)]
if item_obj.days_since_last_order > cint(filters['days']):
row.update({
"territory": item_obj.territory,
"customer": item_obj.customer,
"last_order_date": item_obj.last_order_date,
"qty": item_obj.qty,
"days_since_last_order": item_obj.days_since_last_order
})
else:
continue
data.append(row)
return data
def get_sales_details(filters):
data = []
item_details_map = {}
date_field = "s.transaction_date" if filters["based_on"] == "Sales Order" else "s.posting_date"
sales_data = frappe.db.sql("""
select s.territory, s.customer, si.item_group, si.item_code, si.qty, {date_field} as last_order_date,
DATEDIFF(CURDATE(), {date_field}) as days_since_last_order
from `tab{doctype}` s, `tab{doctype} Item` si
where s.name = si.parent and s.docstatus = 1
order by days_since_last_order """ #nosec
.format(date_field = date_field, doctype = filters['based_on']), as_dict=1)
for d in sales_data:
item_details_map.setdefault((d.territory,d.item_code), d)
return item_details_map
def get_territories(filters):
filter_dict = {}
if filters.get("territory"):
filter_dict.update({'name': filters['territory']})
territories = frappe.get_all("Territory", fields=["name"], filters=filter_dict)
return territories
def get_items(filters):
filters_dict = {
"disabled": 0,
"is_stock_item": 1
}
if filters.get("item_group"):
filters_dict.update({
"item_group": filters["item_group"]
})
if filters.get("item"):
filters_dict.update({
"name": filters["item"]
})
items = frappe.get_all("Item", fields=["name", "item_group", "item_name", "item_code"], filters=filters_dict, order_by="name")
return items
Hi,
Please check the data that you are returning to the report, and its format.
Format of data should be in list of list and not list of dictionary.
2 Likes