In this below code, I want the ID row value should be clickable and it directed to that particular oppoertunity. I need that ID column clickable.
def execute(filters=None):
columns = [
_("ID") + "::150", # New ID column
_("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"
]
# Define the SQL query
query = """
SELECT
o.name AS `ID`, -- Get the ID (e.g., CRM-OPP-2024-00016)
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`,
o.opportunity_amount AS `Enquiry 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 'Enquiry Amount' with commas and 2 decimal places
for row in data:
row['Enquiry Amount'] = f"{row['Enquiry Amount']:,.2f}"
# Make each ID clickable link to the enquiry
row['ID'] = f'<a href="/desk#Form/Opportunity/{row["ID"]}">{row["ID"]}</a>'
return columns, data, True # Indicate HTML rendering