Hi all, i want to make a new report showing the commission for supplier, but i got stuck, i want make it if the purchase order have more than 1 item, it will all shown in the report, but what i make now doesnt showing all of the item, only the first item appear. can u help me with that? below i give my code. thanks a lot
import frappe
from frappe.utils import flt
def execute(filters=None):
if not filters:
filters = {}
columns = get_columns()
data = get_data(filters)
return columns, data
def get_columns():
return [
{“fieldname”: “supplier”, “label”: “Supplier”, “fieldtype”: “Link”, “options”: “Supplier”, “width”: 200},
{“fieldname”: “supplier_target_number”, “label”: “Supplier Target Number”, “fieldtype”: “Data”, “width”: 150},
{“fieldname”: “amount”, “label”: “Amount”, “fieldtype”: “Currency”, “width”: 150},
{“fieldname”: “po_number”, “label”: “PO Number”, “fieldtype”: “Link”, “options”: “Purchase Order”, “width”: 200},
{“fieldname”: “date”, “label”: “Date”, “fieldtype”: “Date”, “width”: 100},
{“fieldname”: “reqd_by_date”, “label”: “Reqd Date”, “fieldtype”: “Date”, “width”: 100},
{“fieldname”: “supplier_name”, “label”: “Supplier Name”, “fieldtype”: “Data”, “width”: 200},
{“fieldname”: “item_name”, “label”: “Item Name”, “fieldtype”: “Data”, “width”: 200},
{“fieldname”: “qty_po”, “label”: “Qty PO”, “fieldtype”: “Float”, “width”: 100},
{“fieldname”: “qty_to_receive”, “label”: “Qty to Receive”, “fieldtype”: “Float”, “width”: 100},
{“fieldname”: “total_amount”, “label”: “Total Amount”, “fieldtype”: “Currency”, “width”: 150},
{“fieldname”: “saldo_achievement”, “label”: “Saldo Achievement”, “fieldtype”: “Currency”, “width”: 150},
{“fieldname”: “saldo_target_supplier”, “label”: “Saldo Target Supplier”, “fieldtype”: “Currency”, “width”: 150},
{“fieldname”: “balance_target”, “label”: “Balance Target”, “fieldtype”: “Currency”, “width”: 150},
{“fieldname”: “qty_receive”, “label”: “Qty Receive”, “fieldtype”: “Float”, “width”: 100},
{“fieldname”: “amount_receive”, “label”: “Amount Receive”, “fieldtype”: “Currency”, “width”: 150},
{“fieldname”: “po_received_percent”, “label”: “% PO Received”, “fieldtype”: “Percent”, “width”: 100},
{“fieldname”: “qty_to_received”, “label”: “Qty To Received”, “fieldtype”: “Float”, “width”: 100},
{“fieldname”: “po_to_received_percent”, “label”: “% PO To Received”, “fieldtype”: “Percent”, “width”: 100},
{“fieldname”: “amount_to_receive”, “label”: “Amount to Receive”, “fieldtype”: “Currency”, “width”: 150},
]
def get_data(filters):
data =
supplier_targets = get_supplier_targets(filters)
for st in supplier_targets:
remaining_target = st.target_amount
purchase_orders = get_purchase_orders(st.supplier, filters)
first_entry = True
for po in purchase_orders:
po_items = get_purchase_order_items(po.name)
for item in po_items:
row = {
"supplier": st.supplier if first_entry else "",
"supplier_target_number": st.name if first_entry else "",
"amount": st.target_amount if first_entry else "",
"po_number": po.name,
"date": po.transaction_date,
"reqd_by_date": po.schedule_date,
"item_name": item.item_name,
"qty_po": item.qty,
"qty_to_receive": item.qty,
"total_amount": item.amount,
"saldo_achievement": item.amount,
"saldo_target_supplier": remaining_target,
"balance_target": remaining_target - item.amount,
"qty_receive": item.received_qty,
"amount_receive": (po.per_received / 100) * item.amount,
"po_received_percent": po.per_received,
"qty_to_received": item.qty - item.received_qty,
"po_to_received_percent": ((item.qty - item.received_qty) / item.qty) * 100,
"amount_to_receive": ((item.qty - item.received_qty) * item.amount) / item.qty
}
remaining_target -= item.amount
row["saldo_achievement"] = st.target_amount - remaining_target
first_entry = False
data.append(row)
# Add a row to display the remaining target if no purchase orders were found
if first_entry:
row = {
"supplier": st.supplier,
"supplier_target_number": st.name,
"amount": st.target_amount,
"po_number": "",
"date": "",
"reqd_by_date": "",
"item_name": item.item_name,
"qty_po": "",
"qty_to_receive": "",
"total_amount": "",
"saldo_achievement": "",
"saldo_target_supplier": remaining_target,
"balance_target": remaining_target - item.amount,
"qty_receive": "",
"amount_receive": "",
"po_received_percent": "",
"qty_to_received": "",
"po_to_received_percent": "",
"amount_to_receive": ""
}
data.append(row)
# If no purchase orders found for supplier target, add a row with remaining target
if not purchase_orders:
row = {
"supplier": st.supplier,
"supplier_target_number": st.name,
"amount": st.target_amount,
"po_number": "",
"date": "",
"reqd_by_date": "",
"supplier_name": "",
"item_name": "",
"qty_po": "",
"qty_to_receive": "",
"total_amount": "",
"saldo_achievement": "",
"saldo_target_supplier": remaining_target,
"balance_target": remaining_target - item.amount,
"qty_receive": "",
"amount_receive": "",
"po_received_percent": "",
"po_to_received_percent": "",
"amount_to_receive": ""
}
data.append(row)
return data
def get_supplier_targets(filters):
conditions = get_supplier_target_conditions(filters)
query = """
SELECT
supplier, name, target_amount
FROM
`tabSupplier Target`
WHERE
{conditions}
""".format(conditions=conditions)
return frappe.db.sql(query, filters, as_dict=True)
def get_purchase_orders(supplier, filters):
conditions = get_purchase_order_conditions(filters)
query = """
SELECT
name, transaction_date, schedule_date, supplier_name, per_received, grand_total, status
FROM
`tabPurchase Order`
WHERE
supplier = %(supplier)s
{conditions}
""".format(conditions=conditions)
return frappe.db.sql(query, {"supplier": supplier, **filters}, as_dict=True)
def get_purchase_order_items(po_name):
query = “”"
SELECT
item_name, qty, received_qty, amount
FROM
tabPurchase Order Item
WHERE
parent = %s
“”"
return frappe.db.sql(query, (po_name,), as_dict=True)
def get_supplier_target_conditions(filters):
conditions =
if filters.get(“supplier”):
conditions.append(“supplier = %(supplier)s”)
return " AND ".join(conditions) if conditions else "1=1"
def get_purchase_order_conditions(filters):
conditions =
if filters.get(“from_date”) and filters.get(“to_date”):
conditions.append(“transaction_date BETWEEN %(from_date)s AND %(to_date)s”)
return " AND " + " AND ".join(conditions) if conditions else “”