Hello!
I need that users of Department can see Tasks only from Projects for this Department.
For each Users appointed Department like and for each Project.
Now Users can’t see other Projects, but they can see all tasks in other Projects.
Or maybe exists better way?
prerequisite
created employee and filled the user_id field
create query_condition server script for both Project and Task respectively.
Query condition server script for Project doctype
department = frappe.db.get_value('Employee',{'user_id':frappe.user},'department')
conditions = """ department = '%s')""" %(department)
for Task
department = frappe.db.get_value('Employee',{'user_id':frappe.user},'department')
conditions = """ department = '%s')""" %(department)
@szufisher tried to do this
and have the error:
### App Versions
{
"erpnext": "13.18.0",
"frappe": "13.18.0"
}
Route
List/Task/List
Trackeback
Syntax error in query:
select `tabTask`.`name`, `tabTask`.`owner`, `tabTask`.`creation`, `tabTask`.`modified`, `tabTask`.`modified_by`, `tabTask`.`_user_tags`, `tabTask`.`_comments`, `tabTask`.`_assign`, `tabTask`.`_liked_by`, `tabTask`.`docstatus`, `tabTask`.`parent`, `tabTask`.`parenttype`, `tabTask`.`parentfield`, `tabTask`.`idx`, `tabTask`.`project`, `tabTask`.`is_group`, `tabTask`.`status`, `tabTask`.`priority`, `tabTask`.`is_milestone`, `tabTask`.`subject`, `tabTask`.`exp_start_date`, `tabTask`.`exp_end_date`, `tabTask`.`progress`, `tabTask`.`depends_on_tasks`, `tabTask`.`_seen`, `tabTask`.`color`
from `tabTask`
where `tabTask`.status = 'Open' and ((((coalesce(`tabTask`.`department`, '')='' or `tabTask`.`department` in ('Управление - УЦ\"')) and (coalesce(`tabTask`.`company`, '')='' or `tabTask`.`company` in ('Учебно-репетиторский центр \"Учазия\"')))) and department = 'Управление - УЦ"'))
group by `tabTask`.`name`
order by `tabTask`.`modified` desc
limit 20 offset 0
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 68, in application
response = frappe.api.handle()
File "apps/frappe/frappe/api.py", line 55, in handle
return frappe.handler.handle()
File "apps/frappe/frappe/handler.py", line 31, in handle
data = execute_cmd(cmd)
File "apps/frappe/frappe/handler.py", line 67, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File "apps/frappe/frappe/__init__.py", line 1208, in call
return fn(*args, **newargs)
File "apps/frappe/frappe/__init__.py", line 624, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
File "apps/frappe/frappe/desk/reportview.py", line 28, in get
data = compress(execute(**args), args=args)
File "apps/frappe/frappe/desk/reportview.py", line 47, in execute
return DatabaseQuery(doctype).execute(*args, **kwargs)
File "apps/frappe/frappe/model/db_query.py", line 107, in execute
result = self.build_and_run()
File "apps/frappe/frappe/model/db_query.py", line 144, in build_and_run
return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug,
File "apps/frappe/frappe/database/database.py", line 154, in sql
self._cursor.execute(query)
File "env/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
result = self._query(query)
File "env/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
conn.query(q)
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "env/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "env/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
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 ')\n\t\t\t group by `tabTask`.`name`\n\t\t\t order by `tabTask`.`modified` desc\n\t\t\tlim...' at line 3")
Request Data
{
"type": "POST",
"args": {
"doctype": "Task",
"fields": "[\"`tabTask`.`name`\",\"`tabTask`.`owner`\",\"`tabTask`.`creation`\",\"`tabTask`.`modified`\",\"`tabTask`.`modified_by`\",\"`tabTask`.`_user_tags`\",\"`tabTask`.`_comments`\",\"`tabTask`.`_assign`\",\"`tabTask`.`_liked_by`\",\"`tabTask`.`docstatus`\",\"`tabTask`.`parent`\",\"`tabTask`.`parenttype`\",\"`tabTask`.`parentfield`\",\"`tabTask`.`idx`\",\"`tabTask`.`project`\",\"`tabTask`.`is_group`\",\"`tabTask`.`status`\",\"`tabTask`.`priority`\",\"`tabTask`.`is_milestone`\",\"`tabTask`.`subject`\",\"`tabTask`.`exp_start_date`\",\"`tabTask`.`exp_end_date`\",\"`tabTask`.`progress`\",\"`tabTask`.`depends_on_tasks`\",\"`tabTask`.`_seen`\",\"`tabTask`.`color`\"]",
"filters": "[[\"Task\",\"status\",\"=\",\"Open\"]]",
"order_by": "`tabTask`.`modified` desc",
"start": 0,
"page_length": 20,
"view": "List",
"group_by": "`tabTask`.`name`",
"with_comment_count": true
},
"freeze": false,
"freeze_message": "Идёт загрузка...",
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.reportview.get"
}
Response Data
{
"exception": "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 ')\\n\\t\\t\\t group by `tabTask`.`name`\\n\\t\\t\\t order by `tabTask`.`modified` desc\\n\\t\\t\\tlim...' at line 3\")"
}
@goldenscrew frappe.user is an object which is why it raises a SQL error. You want to use frappe.session.user to access a string that is the logged in user’s name/ PK.
sorry, the ) need to be removed. the corrected script as below
department = frappe.db.get_value('Employee',{'user_id':frappe.user},'department')
conditions = """ department = '%s' """ %(department)
please try again.
1 Like