Hello guys, i am having trouble filters not working for my script report. Hopefully someone can point out what i’m doing wrong. See code below
def execute(filters=None):
columns, data = [], []
columns = [
{
"fieldname": "name",
"label" : _("Material Request"),
"fieldtype": "Link",
"options": "Material Request"
},
{
"fieldname": "transaction_date",
"label": _("Date "),
"fieldtype": "Date",
},
{
"fieldname": "item_code",
"label": _("Item Code"),
"fieldtype": "Link",
"options": "Item",
},
{
"fieldname": "qty",
"label": _("Qty"),
"fieldtype": "Float",
"width": 150
},
{
"fieldname": "item_name",
"label": _("Item Name"),
"fieldtype": "Data",
"width": 150
},
{
"fieldname": "description",
"label": _("Description"),
"fieldtype": "Data",
"width": 200
},
{
"fieldname": "company",
"label": _("Company"),
"fieldtype": "Link",
"options": "Company",
"width": 150
},
]
if filters.from_date > filters.to_date:
frappe.throw(_("From Date must be before To Date {}").format(filters.to_date))
where_filter = {"from_date": filters.from_date,"to_date": filters.to_date,}
where = ""
if filters.material_req:
where += ' AND tmr.name = %(material_req)s '
where_filter.update({"material_req": filters.material_req})
if filters.item:
where += ' AND tmr_item.item_code = %(item)s '
where_filter.update({"item": filters.item})
data = frappe.db.sql('''SELECT
tmr.name ,
tmr.transaction_date ,
tmr_item.item_code,
tmr_item.qty ,
tmr_item.item_name,
tmr_item.description,
tmr.company
FROM
(`tabMaterial Request` tmr), (`tabMaterial Request Item` tmr_item)
WHERE
tmr_item.parent = tmr.name
and tmr.docstatus = 1
and tmr.status != "Stopped"
and not exists(select name from `tabSupplier Quotation Item` where material_request=tmr.name)
AND tmr.transaction_date BETWEEN %(from_date)s AND %(to_date)s
ORDER BY tmr.transaction_date
'''+ where,
where_filter, as_dict=1,as_list=1
);
return columns, data