I have done one report. I added some columns. But, i need column for months based on intervals like current month, next 3 month. For eg: Sep, Oct, Nov, Dec column should be
appear.If the current month sep done, then the sep column should be disappear and the jan column should newly appear like oct,nov,dec,jan.
Now, i give name like current name, next month, etc…
I attached some screen shots and codes below.
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`,
-- Dynamic month amount columns based on expected_closing
CASE
WHEN MONTH(o.expected_closing) = MONTH(CURDATE())
AND YEAR(o.expected_closing) = YEAR(CURDATE()) THEN o.opportunity_amount
ELSE 0
END AS `Current Month`,
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 o.opportunity_amount
ELSE 0
END AS `Next Month`,
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 o.opportunity_amount
ELSE 0
END AS `Month after next`,
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 o.opportunity_amount
ELSE 0
END AS `Two months after next`
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;
def format_inr(amount):
"""Format the number into Indian currency format."""
if amount is None:
return "0.00"
# Split into whole and decimal parts
whole_part, decimal_part = str(amount).split(".") if "." in str(amount) else (str(amount), "00")
# Format whole part
whole_part = whole_part[::-1] # Reverse the string
groups = []
for i in range(0, len(whole_part), 2):
if i == 0: # First group can be of 3 digits
groups.append(whole_part[i:i + 3])
else:
groups.append(whole_part[i:i + 2])
formatted_whole = ",".join(groups)[::-1] # Join and reverse back
# Format final result
return f"{formatted_whole}.{decimal_part[:2]}"
def execute(filters=None):
from datetime import datetime, timedelta
current_date = datetime.now()
month_names = [current_date.strftime('%B')] # Current month
for i in range(1, 4):
month_names.append((current_date + timedelta(days=30 * i)).strftime('%B')) # Next three 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",
]
# Add dynamic month columns
for month in month_names:
columns.append(f"{month} Amount::150")
# Define the SQL query
query = """ /* The SQL query defined above */ """
# Execute the query
data = frappe.db.sql(query, as_dict=True)
# Format the amounts with Indian number format
for row in data:
row['Enquiry Amount'] = format_inr(float(row['Enquiry Amount']))
# Format the month-specific amounts
for month in month_names:
row[f"{month} Amount"] = format_inr(float(row.get(f'{month} Amount', 0)))
return columns, data