I want date filter in my script report like if I select date fromdate To todate then I want data between that.
I’ve tested a lot of methods but couldn’t find any.
Here is my script 
columns = [
{
"label": _("Supplier"),
"fieldname": "supplier",
"fieldtype": "Data",
"width": 200,
},
{
"label": _("Total Quantity"),
"fieldname": "total_qty",
"fieldtype": "Int",
"width": 150,
},
{
"label": _("Received Quantity"),
"fieldname": "received_qty",
"fieldtype": "Int",
"width": 150,
},
{
"label": _("Balance Quantity"),
"fieldname": "balance_qty",
"fieldtype": "Int",
"width": 150,
}
]
result = frappe.get_list("Purchase Order", filters={"docstatus": 1}, fields=['supplier', 'total_qty'])
# Dictionary to store supplier-wise totals
supplier_totals = {}
for i in result:
supplier = i.supplier
# Initialize supplier's total quantities if not present
if supplier not in supplier_totals:
supplier_totals[supplier] = {
"total_qty": 0,
"received_qty": 0
}
# Increment total quantity for the supplier
supplier_totals[supplier]["total_qty"] = supplier_totals[supplier].get("total_qty", 0) + i.total_qty
# Fetch received quantities for each supplier
for supplier, totals in supplier_totals.items():
values = {'supplier': supplier}
rq = frappe.db.sql("""
SELECT
SUM(pri.received_qty)
FROM `tabPurchase Receipt Item` pri
INNER JOIN `tabPurchase Receipt` pr ON pri.parent = pr.name
WHERE pr.supplier = %(supplier)s
AND pri.docstatus = 1
""", values=values, as_dict=0)
received_qty = int(rq[0][0]) if rq and rq[0][0] is not None else 0
supplier_totals[supplier]["received_qty"] = received_qty
# Calculate balance quantities and prepare the result
result = []
for supplier, totals in supplier_totals.items():
total_qty = totals["total_qty"]
received_qty = totals["received_qty"]
balance_qty = total_qty - received_qty
result.append({
"supplier": supplier,
"total_qty": total_qty,
"received_qty": received_qty,
"balance_qty": balance_qty
})
# Return columns and modified result
data = columns, result
Output: But Filter not work
Please Help Me.
Thank You.
Replace this line with the line in your code:
result = frappe.get_list("Purchase Order", filters={"docstatus": 1, "transaction_date": [">",filters.get("from_dt"), "and", "<",filters.get("to_dt")]}, fields=['supplier', 'total_qty'])
Here according to your code,
from_dt → from_date
to_dt → to_date
After replacing this code nothing is coming in the report
Thanks for your reply but not working.
Pls share your updated code…
Because i have tried and is working on my device.
I think so you didn’t change the filter values as i said,
result = frappe.get_list(“Purchase Order”, filters={“docstatus”: 1, “transaction_date”: [“>”,filters.get(“from_date”), “and”, “<”,filters.get(“to_date”)]}, fields=[‘supplier’, ‘total_qty’])
Try this code, it will definitely work.
NCP
6
Otherwise use it.
from_date = filters.get('from_date')
to_date = filters.get('to_date')
filters = {
'transaction_date': ['between', [from_date, to_date]],
'docstatus': 1
}
result = frappe.get_list("Purchase Order", filters=filters, fields=['supplier', 'total_qty'])
columns = [
{
"label": _("Supplier"),
"fieldname": "supplier",
"fieldtype": "Data",
"width": 200,
},
{
"label": _("Total Quantity"),
"fieldname": "total_qty",
"fieldtype": "Int",
"width": 150,
},
{
"label": _("Received Quantity"),
"fieldname": "received_qty",
"fieldtype": "Int",
"width": 150,
},
{
"label": _("Balance Quantity"),
"fieldname": "balance_qty",
"fieldtype": "Int",
"width": 150,
}
]
result = frappe.get_list(“Purchase Order”, filters={“docstatus”: 1, “transaction_date”:
[“>”,filters.get(“from_date”), “and”, “<”,filters.get(“to_date”)]}, fields=[‘supplier’, ‘total_qty’])
# Dictionary to store supplier-wise totals
supplier_totals = {}
for i in result:
supplier = i.supplier
# Initialize supplier's total quantities if not present
if supplier not in supplier_totals:
supplier_totals[supplier] = {
"total_qty": 0,
"received_qty": 0
}
# Increment total quantity for the supplier
supplier_totals[supplier]["total_qty"] = supplier_totals[supplier].get("total_qty", 0) + i.total_qty
# Fetch received quantities for each supplier
for supplier, totals in supplier_totals.items():
values = {'supplier': supplier}
rq = frappe.db.sql("""
SELECT
SUM(pri.received_qty)
FROM `tabPurchase Receipt Item` pri
INNER JOIN `tabPurchase Receipt` pr ON pri.parent = pr.name
WHERE pr.supplier = %(supplier)s
AND pri.docstatus = 1
""", values=values, as_dict=0)
received_qty = int(rq[0][0]) if rq and rq[0][0] is not None else 0
supplier_totals[supplier]["received_qty"] = received_qty
# Calculate balance quantities and prepare the result
result = []
for supplier, totals in supplier_totals.items():
total_qty = totals["total_qty"]
received_qty = totals["received_qty"]
balance_qty = total_qty - received_qty
result.append({
"supplier": supplier,
"total_qty": total_qty,
"received_qty": received_qty,
"balance_qty": balance_qty
})
# Return columns and modified result
data = columns, result
Updated Code.
Actually this code and also shown above by @NCP should work as it is working on our device,
You can try removing docstatus filter and check.
And verify if you are not doing something wrong.
