Hi,
In this below code i need month names like setember, october, november instead of {current_month}, {next_month_name}, etc and the condition is this report works as interval of 3 months like if the september over then that month diappear and jan month should be appear.
Can you please correct the code.
def execute(filters=None):
from datetime import datetime
from frappe import _ # Ensure _ is imported properly for translation
current_date = datetime.now()
# Get current month and future months
current_month = current_date.strftime('%B') # Get current month name
next_month = (current_date.month % 12) + 1
year_offset = 1 if next_month == 1 else 0
next_month_name = datetime(current_date.year + year_offset, next_month, 1).strftime('%B')
next_month_plus_1 = (next_month % 12) + 1
year_offset = 1 if next_month_plus_1 == 1 else 0
next_month_plus_1_name = datetime(current_date.year + year_offset, next_month_plus_1, 1).strftime('%B')
next_month_plus_2 = (next_month_plus_1 % 12) + 1
year_offset = 1 if next_month_plus_2 == 1 else 0
next_month_plus_2_name = datetime(current_date.year + year_offset, next_month_plus_2, 1).strftime('%B')
# Define columns with dynamic month names
columns = [
_("Enquiry Name") + "::150",
_("Enquiry Owner") + "::150",
_("Status") + "::150",
_("Stage") + "::150",
_("Probability") + "::150",
_("Expected Closing Date") + "::150",
_("Next Activity Date") + "::150",
_("Closing Month") + "::150",
_("Enquiry Amount") + "::150",
f"{current_month} Amount::150", # Column for Current Month
f"{next_month_name} Amount::150", # Column for Next Month
f"{next_month_plus_1_name} Amount::150", # Column for Next Month +1
f"{next_month_plus_2_name} Amount::150" # Column for Next Month +2
]
# Query to fetch data
query = f"""
SELECT
o.customer_name AS `Enquiry Name`,
u.full_name AS `Enquiry Owner`,
o.status AS `Status`,
o.custom_stage AS `Stage`,
o.custom_probability__copy AS `Probability`,
o.expected_closing AS `Expected Closing Date`,
o.custom_next_activity_date AS `Next Activity Date`,
CONCAT(
CASE
WHEN MONTH(o.expected_closing) = 1 THEN 'January'
WHEN MONTH(o.expected_closing) = 2 THEN 'February'
WHEN MONTH(o.expected_closing) = 3 THEN 'March'
WHEN MONTH(o.expected_closing) = 4 THEN 'April'
WHEN MONTH(o.expected_closing) = 5 THEN 'May'
WHEN MONTH(o.expected_closing) = 6 THEN 'June'
WHEN MONTH(o.expected_closing) = 7 THEN 'July'
WHEN MONTH(o.expected_closing) = 8 THEN 'August'
WHEN MONTH(o.expected_closing) = 9 THEN 'September'
WHEN MONTH(o.expected_closing) = 10 THEN 'October'
WHEN MONTH(o.expected_closing) = 11 THEN 'November'
WHEN MONTH(o.expected_closing) = 12 THEN 'December'
END,
' ',
YEAR(o.expected_closing)
) AS `Closing Month`,
FORMAT(o.opportunity_amount, 2) AS `Enquiry Amount`,
CASE
WHEN MONTH(o.expected_closing) = MONTH(CURDATE()) AND YEAR(o.expected_closing) = YEAR(CURDATE()) THEN FORMAT(o.opportunity_amount, 2)
ELSE 0
END AS `{current_month} Amount`,
CASE
WHEN MONTH(o.expected_closing) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(o.expected_closing) = YEAR(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) THEN FORMAT(o.opportunity_amount, 2)
ELSE 0
END AS `{next_month_name} Amount`,
CASE
WHEN MONTH(o.expected_closing) = MONTH(DATE_ADD(CURDATE(), INTERVAL 2 MONTH)) AND YEAR(o.expected_closing) = YEAR(DATE_ADD(CURDATE(), INTERVAL 2 MONTH)) THEN FORMAT(o.opportunity_amount, 2)
ELSE 0
END AS `{next_month_plus_1_name} Amount`,
CASE
WHEN MONTH(o.expected_closing) = MONTH(DATE_ADD(CURDATE(), INTERVAL 3 MONTH)) AND YEAR(o.expected_closing) = YEAR(DATE_ADD(CURDATE(), INTERVAL 3 MONTH)) THEN FORMAT(o.opportunity_amount, 2)
ELSE 0
END AS `{next_month_plus_2_name} Amount`
FROM
tabOpportunity o
LEFT JOIN
tabUser u ON u.email = o.opportunity_owner
WHERE
o.expected_closing BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH)
AND o.docstatus = 0
ORDER BY
o.expected_closing;
"""
# Execute the query
data = frappe.db.sql(query, as_dict=True)
# Format the amounts safely
for row in data:
row['Enquiry Amount'] = f"{float(row['Enquiry Amount'] or 0):,.2f}"
row[f"{current_month} Amount"] = f"{float(row[f'{current_month} Amount'] or 0):,.2f}"
row[f"{next_month_name} Amount"] = f"{float(row[f'{next_month_name} Amount'] or 0):,.2f}"
row[f"{next_month_plus_1_name} Amount"] = f"{float(row[f'{next_month_plus_1_name} Amount'] or 0):,.2f}"
row[f"{next_month_plus_2_name} Amount"] = f"{float(row[f'{next_month_plus_2_name} Amount'] or 0):,.2f}"
return columns, data
