def get_type_id(type_name):
con, conn = connection()
con.execute("SELECT idnote_type FROM note_type WHERE name = %s",
pymysql.escape_string(type_name))
file_type = con.fetchone()
con.close()
conn.close()
return file_type['idnote_type']
def has_access_to_usergroup(id_usergroup, id_user):
"""Returns true if user has access to usergroup, else false"""
con, conn = connection()
con.execute("SELECT user_id FROM user_membership WHERE user_id = %s AND usergroup_id = %s",
(pymysql.escape_string(str(id_user)), pymysql.escape_string(str(id_usergroup))))
return_value = con.fetchone()
con.close()
conn.close()
return True if return_value else False
def has_access_to_notegroup(id_notegroup, id_user):
"""Returns true if user has access to notegroup, else false"""
con, conn = connection()
con.execute("SELECT iduser FROM notegroup_view WHERE iduser = %s AND idnotegroup = %s",
(pymysql.escape_string(str(id_user)), pymysql.escape_string(str(id_notegroup))))
return_value = con.fetchone()
con.close()
conn.close()
return True if return_value else False
def notegroup_empty(idnotegroup):
"""Checks if notegroup is empty"""
con, conn = connection()
con.execute(
"SELECT * FROM note WHERE notegroup_id = %s AND status_id = 1", pymysql.escape_string(str(idnotegroup)))
not_empty = con.fetchone()
con.close()
conn.close()
return False if not_empty else True
def get_usergroups_of_user(iduser):
"""Get list of usergroups"""
con, conn = connection()
con.execute(
"SELECT idusergroup, name, color, description, image_path FROM usergroup_membership WHERE iduser = %s",
pymysql.escape_string(str(iduser)))
usergroups = con.fetchall()
con.close()
conn.close()
return json.dumps(usergroups, ensure_ascii=False)
def get_notegroup(idnotegroup, iduser):
"""Get info about notegroup"""
if has_access_to_notegroup(idnotegroup, iduser):
con, conn = connection()
con.execute("SELECT * FROM notegroup WHERE idnotegroup = %s",
pymysql.escape_string(str(idnotegroup)))
notegroup_info = con.fetchone()
con.close()
conn.close()
return json.dumps(notegroup_info)
return False
def get_note(id_note, id_user):
"""Get note by id"""
if has_access_to_note(id_note, id_user):
con, conn = connection()
con.execute("SELECT * FROM note_view WHERE idnote = %s",
pymysql.escape_string(str(id_note)))
note = con.fetchone()
con.close()
conn.close()
return json.dumps(note)
return False
def get_users_of_usergroup(idusergroup, iduser):
"""Get list of users of usergroup"""
if has_access_to_usergroup(idusergroup, iduser):
con, conn = connection()
con.execute(
"SELECT iduser, login FROM usergroup_membership WHERE idusergroup = %s",
pymysql.escape_string(str(idusergroup)))
users = con.fetchall()
con.close()
conn.close()
return json.dumps(users)
return None
def has_access_to_note(id_note, id_user):
"""Check if user has access to note"""
if note_exists(idnote=id_note):
con, conn = connection()
con.execute("SELECT notegroup_id FROM note WHERE idnote = %s",
pymysql.escape_string(str(id_note)))
note = con.fetchone()
con.execute("SELECT 1 FROM notegroup_view WHERE iduser = %s AND idnotegroup = %s",
(pymysql.escape_string(str(id_user)), pymysql.escape_string(str(note['notegroup_id']))))
has_access = con.fetchone()
con.close()
conn.close()
if has_access:
return True
return False
def get_last_note_actions(idnote, iduser):
"""Get 5 last actions of note"""
con, conn = connection()
con.execute("SELECT * FROM action WHERE note_id = %s ORDER BY date DESC",
pymysql.escape_string(str(idnote)))
last_actions = con.fetchmany(5)
last_actions = [{
'idaction': row['idaction'],
'content': row['content'],
'user_id': row['user_id'],
'date': row['date'].strftime('%I:%M %d.%m.%Y')
} for row in last_actions]
con.close()
conn.close()
print(last_actions)
return json.dumps(last_actions)
def get_root_id(id_usergroup, id_user):
"""Get if of root directory in usergroup"""
if has_access_to_usergroup(id_usergroup, id_user):
con, conn = connection()
con.execute(
"SELECT idnotegroup FROM notegroup_view WHERE iduser = %s AND idusergroup = %s AND parent_id = 0",
(pymysql.escape_string(
str(id_user)), pymysql.escape_string(str(id_usergroup))))
root_id = con.fetchone()
if not root_id:
return "No root folder" + str(id_user)
root_id = root_id['idnotegroup']
con.close()
conn.close()
return root_id
return "Access denied"
def note_exists(idnote=None, title=None, notegroup_id=None):
"""Checks if note exists"""
con, conn = connection()
sql = "SELECT * FROM note WHERE {} AND status_id = 1"
args = (pymysql.escape_string(str(idnote)))
if idnote:
sql = sql.format("idnote= %s")
elif title and notegroup_id:
sql = sql.format("title = %s AND notegroup_id = %s")
args = (pymysql.escape_string(title),
pymysql.escape_string(str(notegroup_id)))
else:
return None
con.execute(sql, args)
note_exists = con.fetchone()
con.close()
conn.close()
return True if note_exists else False
