Help Needed to Create A Report

ERPNext Selling/CRM
1

Hii All,
I want to create a Report for Event and Lead,Code is working expected what i want but now i want to build a Report/Dashboard using this report from frontend i want to give filters

  1. drop to fetch all sales person below him If the single sales person is selected then his data, if multiple then of that persons
  2. Drop down for time range
  3. Based on event Status

Below is the Code:

from frappe.utils import add_days, now

def execute(filters=None):
    user_full_name = frappe.get_value("User", frappe.session.user, "full_name")

    # Get the sales hierarchy for the current user
    hierarchy = frappe.get_all("Sales Person", filters={"parent_sales_person": user_full_name}, fields=["name"])

    # Extract sales person names from the hierarchy
    sales_persons = [d.name for d in hierarchy]

    # Convert the list to a tuple
    sales_persons_tuple = tuple(sales_persons)

    # Dynamic filters
    time_filters = {
        '1_month': add_days(now(), -30),
        '1_week': add_days(now(), -7),
        # Add more time filters as needed
    }

    from_date = filters.get("from_date") or time_filters.get(filters.get("time_filter"), add_days(now(), -30))

    query = f"""
        SELECT
            e.name AS event_id, e.subject AS event_name, e.starts_on, e.ends_on, e.owner AS event_owner,
            l.lead_name, l.email_id, l.mobile_no, l.status AS lead_status
        FROM
            `tabEvent` e
        LEFT JOIN
            `tabEvent Participants` ep ON e.name = ep.parent
        LEFT JOIN
            `tabLead` l ON ep.reference_doctype = 'Lead' AND ep.reference_docname = l.name
        WHERE
            e.status = 'Open'
            AND e.sales_person IN {sales_persons_tuple}
            AND e.starts_on >= '{from_date}'
    """

    events = frappe.db.sql(query, as_dict=True)
    return events

Help needed to implement this.

I want to add this as a Dashboard Page.

2

@NCP Any Help on This ?

3

How to filter in report.js for check the reference:

filter according to data shown for.

def execute(filters=None):
    sales_person = filters.get("sales_person")
    time_filter = filters.get("time_filter")
    event_status = filters.get("event_status")
    
    # your code
    ...
    

e.status = '{event_status}'

e.sales_person IN {sales_persons_tuple}

from_date = filters.get("from_date") or time_filters.get(time_filter, add_days(now(), -30))

Check it.