ERPNext Foundation ERPNext Cloud Chat Blog Discuss Frappé* Donate

Script report filters


#1

i user script report in appraisal, but its not working. i have branch filter field, employee filter field and start and end date filter. but know of them are working

i use below js script:

frappe.query_reports[“Appraisal Report”] = {
“filters”: [
{
“fieldname”: “branch”,
“label”:__(“Branch Name”),
“fieldtype”:“Link”,
“options”:“Branch”,

},

{	
		"fieldname":"employee_name",
		"label": __("Employee Name"),
		"fieldtype": "Link",
		"options": "Employee",

		"get_query" : function(){
			var branch = frappe.query_report_filters_by_name.branch.get_value();
			return{
				"doctype": "Appraisal",
				"filters":{
					"branch":branch,
				}
			}
		}
	},

{
	"fieldname":"start_date",
	"label": __("From Date"),
	"fieldtype": "Date",
	"reqd": 1,
	"default": frappe.datetime.year_start()
},
{
		"fieldname":"end_date",
		"label": __("To Date"),
		"fieldtype": "Date",
		"reqd": 1,
		"default": frappe.datetime.year_end()
	}, 

in firstly i have try to filter employee name, but its not working.

please help me


#2

share your python code …


#3

def execute(filters=None):
columns = get_columns(filters)
data = get_data(filters)

return columns, data

def get_columns(filters):
return[
(“Appraisal Type”),
(“From Date”)+ “:Data:90”,
(“To Date”)+ “:Data:90”,
(“Employee”) + “:Link/Employee:100”,
(“Employee Name”) + “:Data:150”,
(“Designation”) + “:Data:160”,
(“Grade”) + “:Data:50”,
(“Branch”) + “:Data:150”,
(“Supervisor Name”)+":Data:150",
(“Total Score”),
(“Status”)+ “:Data:80”
]

def get_data(filters):
query = “select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal”
return frappe.db.sql(query)

if filters.get("employee"):
	data = "select employee from tabAppraisal"

return frappe.db.sql(data)

#4

@Maheshwari_Bhavesh,

can you suggestion python code. i don’t have much idea about python.


#5
def execute(filters=None):
	columns = get_columns(filters)
	data = get_data(filters)
	return columns, data

def get_columns(filters):
	return[
		(“Appraisal Type”),
		(“From Date”)+ “:Data:90”,
		(“To Date”)+ “:Data:90”,
		(“Employee”) + “:Link/Employee:100”,
		(“Employee Name”) + “:Data:150”,
		(“Designation”) + “:Data:160”,
		(“Grade”) + “:Data:50”,
		(“Branch”) + “:Data:150”,
		(“Supervisor Name”)+":Data:150",
		(“Total Score”),
		(“Status”)+ “:Data:80”
		]

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal""" % conditions)

def get_conditions(filters):
	conditions = ""
	if filters.get("employee"): conditions += "employee = %(employee_name)s"
	return conditions

#6

thanks @Maheshwari_Bhavesh have done same like yours code but its giving error. error are screen short below


#7

Try This

def execute(filters=None):
	columns = get_columns(filters)
	data = get_data(filters)
	return columns, data

def get_columns(filters):
	return[
		(“Appraisal Type”),
		(“From Date”)+ “:Data:90”,
		(“To Date”)+ “:Data:90”,
		(“Employee”) + “:Link/Employee:100”,
		(“Employee Name”) + “:Data:150”,
		(“Designation”) + “:Data:160”,
		(“Grade”) + “:Data:50”,
		(“Branch”) + “:Data:150”,
		(“Supervisor Name”)+":Data:150",
		(“Total Score”),
		(“Status”)+ “:Data:80”
		]

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions)
        return query

def get_conditions(filters):
	conditions = ""
	if filters.get("employee"): conditions += "employee = %(employee_name)s"
	return conditions

#8

its giving same error:
File “/home/erp/erpnext/apps/erpnext/erpnext/hr/report/appraisal_report/appraisal_report.py”, line 36, in get_data
query=frappe.db.sql(""“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal”"" % conditions)
TypeError: not all arguments converted during string formatting


#9

It should be

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions, filters)
    return query

#10

@shreya115, its giving same error;

my code:

def get_data(filters):
conditions=get_conditions(filters)
query=frappe.db.sql(""“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s”"" % conditions)
return query

def get_conditions(filters):
conditions = “”
if filters.get(“employee”): conditions += “employee = %(employee_name)s”

	return conditions, filters

#11
def get_data(filters):
conditions=get_conditions(filters)
query=frappe.db.sql(""“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s”"" % conditions)
return query

def get_conditions(filters):
conditions = “”
if filters.get(“employee”): conditions += “employee = %(employee_name)s”

	return conditions

#12

My bad, this will work.

def get_data(filters):
	conditions=get_conditions(filters)
	query=frappe.db.sql("""select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions, filters)
    return query

#13

@shreya115, but its not filtering any thing, when i enter branch or employee name, date between, it should filter. but its not filtering. please help me.

i have report page:


#14

Why don’t you go through some already created script reports and try?

You can refer this:


#15

thanks @shreya115, i will try


#16

@shreya115,

i tried lot but fail,

my code:

{
def execute(filters=None):
columns = get_columns(filters)
data = get_data(filters)

return columns, data

def get_columns(filters):
return[
(“Appraisal Type”),
(“From Date”)+ “:Data:90”,
(“To Date”)+ “:Data:90”,
(“Employee”) + “:Link/Employee:100”,
(“Employee Name”) + “:Data:150”,
(“Designation”) + “:Data:160”,
(“Grade”) + “:Data:50”,
(“Branch”) + “:Data:150”,
(“Supervisor Name”)+":Data:150",
(“Total Score”),
(“Status”)+ “:Data:80”
]

def get_data(filters):
conditions=get_conditions(filters)
query=frappe.db.sql(""“select kra_template, start_date, end_date, employee, employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal wheere %s”"" % conditions, filters)
return query
employee_name, designation, grade, branch, leave_approver_name, total_supervisor, status from tabAppraisal where %s""" % conditions, filters)

def get_conditions(filters):
conditions = “”

	if filters.get("employee"): 
			conditions += " and employee = %(employee)s"

	if filters.get("branch"):
			conditions += " and branch = %(branch)s"

	if filters.get("status"):
			conditions += " and status = %(status)s"	

	return conditions

but its giving error:

ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘and status = ‘Draft’’ at line 1”)

please help me…


#17

Why are you writing the query twice? Also, check the where condition, there’s a typo and modify it to something like where docstatus != 2 %s