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 _
current_date = datetime.now()
month_names = [
"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"
]
current_month_index = current_date.month - 1 # Adjust for 0-based index
rolling_months = [
month_names[(current_month_index + i) % 12] for i in range(4)
]
print("Rolling months:", rolling_months)
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",
]
for month in rolling_months:
columns.append(f"{month} Amount::150")
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 `{rolling_months[0]} 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 `{rolling_months[1]} 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 `{rolling_months[2]} 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 `{rolling_months[3]} 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;
"""
data = frappe.db.sql(query, as_dict=True)
for row in data:
row['Enquiry Amount'] = f"{float(row['Enquiry Amount'] or 0):,.2f}"
for month in rolling_months:
row[f"{month} Amount"] = f"{float(row[f'{month} Amount'] or 0):,.2f}"
return columns, data
````Preformatted text`
I just need the column heading should show month names like setember, october, november instead of {current_month}, {next_month_name},etc.
there is interval function. so the logic is, when the current month “september” over then the column disappear and the current month should be OCT and new column created for Jan.