I want to get follow up child table information depending on the Lead and Customer Here is my code in the script report python file
def execute(filters=None):
columns, data = [], []
columns = [
{
“fieldname”: “date_of_contact”,
“label” : _(“Next Scheduled Communication”),
“fieldtype”: “Datetime”,
},
{
“fieldname”: “customer”,
“label”: _(“Customer”),
“fieldtype”: “Link”,
“options”: “Customer”,
},
{
“fieldname”: “reference_type”,
“label”: _(“Reference Type”),
“fieldtype”: “Link”,
“options”: “Customer”,
},
{
“fieldname”: “reference_name”,
“label”: _(“Reference name”),
“fieldtype”: “Data”,
},
{
“fieldname”: “assigned_to”,
“label”: _(“Assigned To”),
“fieldtype”: “Link”,
“options”: “User”,
},
{
“fieldname”: “last_communication_date”,
“label”: _(“Last Communication Date”),
“fieldtype”: “Datetime”,
},
{
“fieldname”: “last_feedback”,
“label”: _(“Last Communication Feedback”),
“fieldtype”: “Data”,
“width”: 200
},
{
“fieldname”:“followup_type”,
“label”: (“Alert Type”),
“fieldtype”: “Select”,
},
]
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.user:
where += ' AND `tabLead Follow Up`.contacted_by = %(user)s '
where_filter.update({"user": filters.user})
if filters.alert_type:
where += ' AND `tabLead Follow Up`.alert_type = %(alert_type)s'
where_filter.update({"alert_type": filters.alert_type})
if '''`tabLead`.lead_name''':
reference_type = 'lead'
else:
reference_type = 'customer'
data = frappe.db.sql('''SELECT
tfu.next_contact_date AS date_of_contact,
tl.lead_name AS reference_name,
tc.customer_name AS reference_name,
tfu.parenttype AS reference_type,
CASE
WHEN tfu.parent = tl.name THEN tl.lead_name
WHEN tfu.parent = tc.name THEN tc.customer_name
END
FROM
(`tabLead Follow Up` tfu)
LEFT JOIN tabLead tl
ON (tfu.parent=tl.name)
LEFT JOIN tabCustomer tc
ON (tfu.parent=tc.name)
'''
);
return columns, data