Hi,
I have created one report for Enquiry (nothing but Opportunity). Based on the report , I want to create dashboard chart but my need is simple, I need interval of Upcoming months in X Axis like Sep 2024, Oct 2024, Nov 2024… Instead of last quarter.
This is my report codes.
SQL QUERY:
SELECT
naming_series AS ‘Enquiry ID’,
customer_name AS ‘Customer’,
opportunity_type AS ‘Type’,
expected_closing AS ‘Expected Closing Date’,
opportunity_amount AS ‘Amount’,
status AS ‘Status’
FROM
tabOpportunity
WHERE
expected_closing BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH)
AND docstatus = 0
ORDER BY
expected_closing;
SCRIPT:
from frappe.utils import getdate, add_days, formatdate
from frappe import _
def execute(filters=None):
if not filters:
filters = {}
today = getdate()
end_date = add_days(today, 90)
# Generate list of next 3 months
months = [formatdate(add_days(today, 30 * i), "MMM") for i in range(3)]
# Adjust the SQL query to fit the expected date range
query = """
SELECT
naming_series AS 'Enquiry ID',
customer_name AS 'Customer',
opportunity_type AS 'Type',
expected_closing AS 'Expected Closing Date',
opportunity_amount AS 'Amount',
status AS 'Status'
FROM
tabOpportunity
WHERE
expected_closing BETWEEN %s AND %s
AND docstatus = 0
ORDER BY
expected_closing;
"""
data = frappe.db.sql(query, (today, end_date), as_dict=True)
# Process data to fit into monthly buckets
monthly_data = {month: 0 for month in months}
for record in data:
month = formatdate(getdate(record['Expected Closing Date']), "MMM")
if month in monthly_data:
monthly_data[month] += record['Amount']
return monthly_data