Filter Employee Checkin by Company in Custom Script Report

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

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

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

thanks

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

1 Like

Ok Sir Thank you for the input