I have created a script report on a custom doctype and This error is comming in the server but in my local machine it’s working fine.
Python Code:
import frappe
from frappe import _
def execute(filters=None):
if not filters:
filters = {}
columns = get_columns()
data = get_referral_records(filters)
return columns, data
def get_columns():
columns = [
{
"label": _("ID"),
"fieldname": "name",
"fieldtype": "Link",
"options": "Referral Record",
"width": 150,
},
{
"label": _("Referee/Customer Name"),
"fieldname": "referee_name",
"fieldtype": "Data",
"width": 150,
},
{
"label": _("Referrer Name"),
"fieldname": "referrer_name",
"fieldtype": "Data",
"width": 150,
},
{
"label": _("Referee Points Earned"),
"fieldname": "referee_points_earned",
"fieldtype": "Int",
"width": 150,
},
{
"label": _("Referrer Points Earned"),
"fieldname": "referrer_points_earned",
"fieldtype": "Int",
"width": 150,
},
{
"label": _("Reference ID"),
"fieldname": "reference_id",
"fieldtype": "Data",
"width": 150,
},
{
"label": _("Referrer Reference ID"),
"fieldname": "referrer_reference_id",
"fieldtype": "Data",
"width": 150,
},
{
"label": _("Created At"),
"fieldname": "created_at",
"fieldtype": "Datetime",
"width": 150,
}
]
return columns
def get_referral_records(filters):
filter_list = filters or {}
show_top_rows = filter_list.get("show_top_rows")
if show_top_rows is not None:
show_top_rows = int(filter_list.get("show_top_rows"))
else:
show_top_rows = 20
data = frappe.db.sql(f"""
SELECT
name,
referee_name,
referrer_name,
referee_points_earned,
referrer_points_earned,
reference_id,
(SELECT rr.reference_id FROM `tabReferral Record` AS rr WHERE rr.referee_customer_id = r.referrer_unique_id LIMIT 1) AS referrer_reference_id,
created_at
FROM
`tabReferral Record` AS r
ORDER BY
created_at DESC
LIMIT {show_top_rows}
""", as_dict=True)
return data
Javascript Code
// Copyright (c) 2024, Spareit and contributors
// For license information, please see license.txt
/* eslint-disable */
frappe.query_reports["Referral Records Report"] = {
"filters": [
{
"fieldname": "show_top_rows",
"label": __("Show Top Rows"),
"fieldtype": "Select",
"options": "20\n100\n500",
"width": "80",
},
]
};
JSON file content
{
"add_total_row": 0,
"columns": [],
"creation": "2024-03-22 18:30:48.301435",
"disable_prepared_report": 0,
"disabled": 0,
"docstatus": 0,
"doctype": "Report",
"filters": [],
"idx": 0,
"is_standard": "Yes",
"modified": "2024-03-22 18:30:48.301435",
"modified_by": "Administrator",
"module": "Spareit Loyalty",
"name": "Referral Records Report",
"owner": "Administrator",
"prepared_report": 0,
"query": "",
"ref_doctype": "Referral Record",
"report_name": "Referral Records Report",
"report_type": "Script Report",
"roles": [
{
"role": "System Manager"
}
]
}