Server Error when attempting to save after entering employee within job card "time logs" table.

[sailingphreek]

Description of the issue

Job Card can not be saved with employee entered within "Time Logs" table.

Context information (for bug reports)

Output of bench version

erpnext 13.7.0
frappe 13.7.0

Steps to reproduce the issue

1. Create a Job Card
2. Select an employee within the "Time Logs" table
3. Try to 'Save' the Job Card

Observed result

Server Error
pymysql.err.OperationalError: (1054, "Unknown column 'jc.employee' in 'where clause'")

Expected result

For the Job Card to save.

Stacktrace / full error message

Having a hard time getting the full error message for v13.7, but here it is for v13.6 where i was getting the same error.

{
“erpnext”: “13.6.0”,
“erpnext_support”: “0.0.3”,
“frappe”: “13.6.0”
}
Route
Form/Job Card/PO-JOB00009
Error Report
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 68, in application
response = frappe.api.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/api.py”, line 55, in handle
return frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 31, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 67, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 1172, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/manufacturing/doctype/job_card/job_card.py”, line 532, in make_time_log
doc.add_time_log(args)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/manufacturing/doctype/job_card/job_card.py”, line 209, in add_time_log
self.save()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 285, in save
return self._save(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 320, in _save
self.run_before_save_methods()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 961, in run_before_save_methods
self.run_method(“validate”)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 859, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 1148, in composer
return composed(self, method, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 1131, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 853, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/manufacturing/doctype/job_card/job_card.py”, line 25, in validate
self.validate_time_logs()
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/manufacturing/doctype/job_card/job_card.py”, line 50, in validate_time_logs
data = self.get_overlap_for(d)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/manufacturing/doctype/job_card/job_card.py”, line 100, in get_overlap_for
}, as_dict=True)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 146, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/cursors.py”, line 148, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/cursors.py”, line 310, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 775, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 1156, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “/home/frappe/frappe-bench/env/lib/python3.7/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, “Unknown column ‘jc.employee’ in ‘where clause’”)
Request Data
{
“type”: “POST”,
“args”: {
“args”: "{“job_card_id”:“PO-JOB00009”,“start_time”:“2021-07-15 16:37:11”,"employees

Additional information

Thank you for the help!!!! Great software and grateful to be able to use it. Thank you!
OS version / distribution, ERPNext install method, etc.
Observed error on both 'trial license' on frappe hosted platform and on self-hosted Ubuntu server 20.04.2 LTS

[marination]

@sailingphreek cant replicate this on the latest version as you said. Can this issue be closed ? We want to keep issues open only if they are persistent on the latest minor version or a previous major version.

[reubenromandy]

I experienced this same behavior on version:
erpnext 13.10.2
frappe 13.10.0

The table 'TabJob Card' does not have a field 'employee'. The code below attempts to check for an overlap using jc.employee
But I'm guessing that the code should be referring to jctl.employee since it's a valid field that table.
I made the change from jc.employee to jctl.employee on my instance and I can submit work orders and create job cards now.
pertinent code:

if args.get("employee"):
                        # override capacity for employee
                        production_capacity = 1
                        validate_overlap_for = " and jc.employee = %(employee)s "

                extra_cond = ''
                if check_next_available_slot:
                        extra_cond = " or (%(from_time)s <= jctl.from_time and %(to_time)s <= jctl.to_time)"

                existing = frappe.db.sql("""select jc.name as name, jctl.to_time from
                        `tabJob Card Time Log` jctl, `tabJob Card` jc where jctl.parent = jc.name and
                        (
                                (%(from_time)s > jctl.from_time and %(from_time)s < jctl.to_time) or
                                (%(to_time)s > jctl.from_time and %(to_time)s < jctl.to_time) or
                                (%(from_time)s <= jctl.from_time and %(to_time)s >= jctl.to_time) {0}
                        )
                        and jctl.name != %(name)s and jc.name != %(parent)s and jc.docstatus < 2 {1}
                        order by jctl.to_time desc limit 1""".format(extra_cond, validate_overlap_for),
                        {
                                "from_time": args.from_time,
                                "to_time": args.to_time,
                                "name": args.name or "No Name",
                                "parent": args.parent or "No Name",
                                "employee": args.get("employee"),
                                "workstation": self.workstation
                        }, as_dict=True)