Hi All,
Is there anything that I missed from script below, I got error message when I select Company
pymysql.err.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 ‘Company ABC’‘’ at line 6”)
employee_checkin.py
from future import unicode_literals
from frappe import _
import frappedef execute(filters=None):
return get_columns(), get_data(filters)def get_data(filters):
from_date = filters.get('from_date') to_date = filters.get('to_date') conditions = " AND 1=1 " if filters.get("company"):conditions += f" AND company = '%s'" % frappe.db.escape(filters.get("company")) data = frappe.db.sql(f""" SELECT employee_name, employee, log_type, DATE(time), LOWER(DATE_FORMAT(TIME,'%l:%i %p')) FROM `tabEmployee Checkin` WHERE (creation BETWEEN '{from_date}' AND '{to_date}') {conditions}; """) return data
def get_columns():
return [
“Employee Name:Link/Employee:250”,
“Emp ID:Data:80”,
“Log Type:Data:100”,
“Date:Date:100”,
“Time:time:170”
]
employee_checkin.js
// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
// For license information, please see license.txt
/* eslint-disable */frappe.query_reports[“Employee Checkin”] = {
“filters”: [
{
“fieldname”: “company”,
“label”: __(“Company”),
“fieldtype”: “Link”,
“width”: “80”,
“options”: “Company”,
“default”: frappe.defaults.get_default(“company”)
},
{
“fieldname”:“from_date”,
“label”: __(“From Date”),
“fieldtype”: “Date”,
“width”: “80”,
“reqd”: 1,
“default”: frappe.datetime.get_today(),
},
{
“fieldname”:“to_date”,
“label”: __(“To Date”),
“fieldtype”: “Date”,
“width”: “80”,
“reqd”: 1,
“default”: frappe.datetime.get_today()
}
]
};