Filter Employee Checkin by Company in Custom Script Report

ERPNext
#1

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 frappe

def 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()
}
]
};

#2

I don’t think there is any company field in Employee Checkin

#3

Yes Sir, Do you have any idea on how Link the company to filter employee per company?

thanks

#4

you could use JOIN in your SQL query and join Employee Checkin and Employee and get the company from Employee company field

1 Like
#5

Ok Sir Thank you for the input