Hi, so i already make a custom report, and i need to make the date filter is based on payment entry date only, but when i select another date that didn’t have payment entry, it kept showing the sales invoice data that has payment entry on it. is anyone can help? below i give my code. thanks a lot
from future import unicode_literals
import frappe
from frappe import _, msgprint
def execute(filters=None):
if not filters: filters = {}
columns = get_columns(filters)
entries = get_entries(filters)
data = []
for d in entries:
data.append([
d.name, d.customer, d.territory, d.dt_posting_date,
d.base_net_amount, d.sales_person, d.allocated_percentage,
d.commission_rate, d.allocated_amount, d.incentives,
d.payment_entry_name, d.payment_entry_posting_date
])
if data:
total_row = [""]*len(data[0])
data.append(total_row)
return columns, data
def get_columns(filters):
if not filters.get(“doc_type”):
msgprint(_(“Please select the document type first”), raise_exception=1)
columns =[
{
"label": _(filters["doc_type"]),
"options": filters["doc_type"],
"fieldname": filters['doc_type'],
"fieldtype": "Link",
"width": 140
},
{
"label": _("Customer"),
"options": "Customer",
"fieldname": "customer",
"fieldtype": "Link",
"width": 140
},
{
"label": _("Territory"),
"options": "Territory",
"fieldname": "territory",
"fieldtype": "Link",
"width": 100
},
{
"label": _("Posting Date"),
"fieldname": "dt_posting_date",
"fieldtype": "Date",
"width": 100
},
{
"label": _("Amount"),
"fieldname": "amount",
"fieldtype": "Currency",
"width": 120
},
{
"label": _("Sales Person"),
"options": "Sales Person",
"fieldname": "sales_person",
"fieldtype": "Link",
"width": 140
},
{
"label": _("Contribution %"),
"fieldname": "contribution_percentage",
"fieldtype": "Data",
"width": 110
},
{
"label": _("Commission Rate %"),
"fieldname": "commission_rate",
"fieldtype": "Data",
"width": 100
},
{
"label": _("Contribution Amount"),
"fieldname": "contribution_amount",
"fieldtype": "Currency",
"width": 120
},
{
"label": _("Incentives"),
"fieldname": "incentives",
"fieldtype": "Currency",
"width": 120
},
{
"label": _("Payment Entry"),
"fieldname": "payment_entry_name",
"fieldtype": "Link",
"options": "Payment Entry",
"width": 140
},
{
"label": _("Payment Entry Posting Date"),
"fieldname": "payment_entry_posting_date",
"fieldtype": "Date",
"width": 100
}
]
return columns
def get_entries(filters):
conditions, values = get_conditions(filters)
query = “”"
SELECT
dt.name, dt.customer, dt.territory, dt.posting_date AS dt_posting_date,
dt.base_net_total AS base_net_amount, st.commission_rate,
st.sales_person, st.allocated_percentage, st.allocated_amount,
st.incentives, pe.name AS payment_entry_name, pe.posting_date AS payment_entry_posting_date
FROM
tab{doc_type} dt
JOIN tabSales Team st ON st.parent = dt.name AND st.parenttype = ‘{doc_type}’
JOIN tabPayment Entry Reference per ON per.reference_name = dt.name
JOIN tabPayment Entry pe ON pe.name = per.parent
WHERE
dt.docstatus = 1 AND dt.status = ‘Paid’ {conditions}
ORDER BY dt.name DESC, st.sales_person
“”".format(doc_type=filters[“doc_type”], conditions=conditions)
entries = frappe.db.sql(query, tuple(values), as_dict=1)
return entries
def get_conditions(filters):
conditions =
values =
for field in ["company", "customer", "territory"]:
if filters.get(field):
conditions.append("dt.{0}=%s".format(field))
values.append(filters[field])
if filters.get("sales_person"):
conditions.append("st.sales_person = %s")
values.append(filters.get("sales_person"))
if filters.get("date"):
conditions.append("pe.posting_date >= %s")
values.append(filters["date"])
if conditions:
return " AND " + " AND ".join(conditions), values
else:
return "", values