def deleteGroup():
group_id = request.args[0]
# projekty, kde je skupina
projects_q = "SELECT project_id FROM project_domaingroups WHERE domaingroup_id = '%s'" % group_id
projects = db.executesql(projects_q, as_dict=True)
del_users_q = "SELECT user_id FROM domaingroup_users WHERE domaingroup_id = '%s'" % group_id
del_users = db.executesql(del_users_q, as_dict=True)
del_user_q = "DELETE FROM project_users WHERE user_id IN ({uids}) AND project_id = '{pid}'"
del_user_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({uids}); \
DELETE FROM auth_user WHERE user_id IN ({uids});"
del_group_q = "DELETE FROM domain_groups WHERE domaingroup_id = '%s'" % group_id
del_group_pj_q = "DELETE FROM userspace.usergroups WHERE usergroup_id = '%s'" % group_id
# odstrani uzivatele ze vsch projektu, kde byli pridani touhle skupinou
if len(projects) and len(del_users):
del_users_s = getSqlInCond([uid['user_id'] for uid in del_users])
for pj in projects:
del_users_q = "SELECT user_id FROM project_users WHERE project_id = '{pid}' \
AND user_id IN ({del_users}) AND user_id NOT IN ( \
SELECT user_id FROM project_users WHERE privilege_id != -1 AND project_id = '{pid}' \
) AND user_id NOT IN ( \
SELECT gu.user_id FROM project_domaingroups pg \
INNER JOIN domaingroup_users gu USING(domaingroup_id) \
WHERE project_id = '{pid}' AND domaingroup_id != '{gid}')\
".format(del_users=del_users_s,
pid=pj['project_id'],
gid=group_id)
del_users = db.executesql(del_users_q, as_dict=True)
# uzivatele, kteri pujdou uplne smazat
if len(del_users):
del_users_l = getSqlInCond(
[uid['user_id'] for uid in del_users])
db.executesql(
del_user_q.format(uids=del_users_l, pid=pj['project_id']))
mng.executeRemoteSQL(pj['project_id'],
del_user_pj_q.format(uids=del_users_l),
False, True, False)
mng.executeRemoteSQL(pj['project_id'], del_group_pj_q, False, True,
False)
# smaze skupinu v lotylda mng
db.executesql(del_group_q)
return False
def deleteGroup():
group_id = request.args[0]
# projekty, kde je skupina
projects_q = "SELECT project_id FROM project_domaingroups WHERE domaingroup_id = '%s'"%group_id
projects = db.executesql(projects_q, as_dict = True)
del_users_q = "SELECT user_id FROM domaingroup_users WHERE domaingroup_id = '%s'"%group_id
del_users = db.executesql(del_users_q, as_dict = True)
del_user_q = "DELETE FROM project_users WHERE user_id IN ({uids}) AND project_id = '{pid}'"
del_user_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({uids}); \
DELETE FROM auth_user WHERE user_id IN ({uids});"
del_group_q = "DELETE FROM domain_groups WHERE domaingroup_id = '%s'"%group_id
del_group_pj_q = "DELETE FROM userspace.usergroups WHERE usergroup_id = '%s'"%group_id
# odstrani uzivatele ze vsch projektu, kde byli pridani touhle skupinou
if len(projects) and len(del_users):
del_users_s = getSqlInCond([uid['user_id'] for uid in del_users])
for pj in projects:
del_users_q = "SELECT user_id FROM project_users WHERE project_id = '{pid}' \
AND user_id IN ({del_users}) AND user_id NOT IN ( \
SELECT user_id FROM project_users WHERE privilege_id != -1 AND project_id = '{pid}' \
) AND user_id NOT IN ( \
SELECT gu.user_id FROM project_domaingroups pg \
INNER JOIN domaingroup_users gu USING(domaingroup_id) \
WHERE project_id = '{pid}' AND domaingroup_id != '{gid}')\
".format(del_users = del_users_s, pid = pj['project_id'], gid = group_id)
del_users = db.executesql(del_users_q, as_dict = True)
# uzivatele, kteri pujdou uplne smazat
if len(del_users):
del_users_l = getSqlInCond([uid['user_id'] for uid in del_users])
db.executesql(del_user_q.format(uids = del_users_l, pid = pj['project_id']))
mng.executeRemoteSQL(pj['project_id'], del_user_pj_q.format(uids = del_users_l)
, False, True, False)
mng.executeRemoteSQL(pj['project_id'], del_group_pj_q, False, True, False)
# smaze skupinu v lotylda mng
db.executesql(del_group_q)
return False
def getUserItems(project_id, user_id):
'''Return dic of user's dasbhboards, reports, measures by user_id'''
items_dic = {'dasbhboards':[],
'reports':[],
'measures':[]
}
items_dic_empty = copy.deepcopy(items_dic)
# get users dashboards
dashs_q = "SELECT dashboard_id, dashboard_name FROM userspace.dashboards WHERE created_user = '******'"%user_id
dashs = mng.executeRemoteSQL(project_id, dashs_q , True, True, False)
if len(dashs):
for d in dashs:
items_dic['dasbhboards'].append((d['dashboard_id'],d['dashboard_name']))
# get users reports
reps_q = "SELECT report_id, report_name FROM userspace.reports WHERE created_user = '******'"%user_id
reps = mng.executeRemoteSQL(project_id, reps_q , True, True, False)
if len(reps):
for r in reps:
items_dic['reports'].append((r['report_id'],r['report_name']))
# get users measures
meas_q = "SELECT measure_id, measure_name FROM engine.measures WHERE created_user = '******'"%user_id
meas = mng.executeRemoteSQL(project_id, meas_q , True, True, False)
if len(dashs):
for m in meas:
items_dic['measures'].append((m['measure_id'],m['measure_name']))
if items_dic == items_dic_empty:
return {}
else:
return items_dic
def saveGroupProjects():
postData = request.post_vars
upd_priv_q = "UPDATE project_domaingroups SET privilege_id = {priv_id} \
WHERE project_id = '{project_id}' AND domaingroup_id = '{group_id}'"
upd_priv_pj_q = "UPDATE userspace.usergroups SET privilege_id = {priv_id} WHERE usergroup_id = '{group_id}'"
# only one project -> string
if type(postData['projectPrivileges']) is str:
pom = postData['projectPrivileges'].split('|sep|')
if pom[1] != '0':
db.executesql(
upd_priv_q.format(priv_id=pom[1],
project_id=pom[0],
group_id=postData['groupId']))
mng.executeRemoteSQL(
pom[0],
upd_priv_pj_q.format(priv_id=pom[1],
group_id=postData['groupId']), False,
True, False)
# more than one projects -> array
else:
for project in postData['projectPrivileges']:
pom = project.split('|sep|')
if pom[1] == '0':
continue
db.executesql(
upd_priv_q.format(priv_id=pom[1],
project_id=pom[0],
group_id=postData['groupId']))
mng.executeRemoteSQL(
pom[0],
upd_priv_pj_q.format(priv_id=pom[1],
group_id=postData['groupId']), False,
True, False)
return True
def deleteUser():
domainId = request.args[0]
user_id = request.args[1]
# projekty, ve kterych je uzivatel
user_projects_q = "SELECT project_id FROM project_users WHERE user_id = '%s'" % user_id
user_projects = db.executesql(user_projects_q, as_dict=True)
del_user_pj_q = "DELETE FROM userspace.users WHERE user_id = '{user_id}'; \
DELETE FROM auth_user WHERE user_id = '{user_id}'; \
".format(user_id=user_id)
if len(user_projects):
for pj in user_projects:
mng.executeRemoteSQL(pj['project_id'], del_user_pj_q, False, True,
False)
del_user_q = "DELETE FROM auth_user WHERE user_id = '%s'" % user_id
db.executesql(del_user_q)
return True
def getUserItems(project_id, user_id):
'''Return dic of user's dasbhboards, reports, measures by user_id'''
items_dic = {'dasbhboards': [], 'reports': [], 'measures': []}
items_dic_empty = copy.deepcopy(items_dic)
# get users dashboards
dashs_q = "SELECT dashboard_id, dashboard_name FROM userspace.dashboards WHERE created_user = '******'" % user_id
dashs = mng.executeRemoteSQL(project_id, dashs_q, True, True, False)
if len(dashs):
for d in dashs:
items_dic['dasbhboards'].append(
(d['dashboard_id'], d['dashboard_name']))
# get users reports
reps_q = "SELECT report_id, report_name FROM userspace.reports WHERE created_user = '******'" % user_id
reps = mng.executeRemoteSQL(project_id, reps_q, True, True, False)
if len(reps):
for r in reps:
items_dic['reports'].append((r['report_id'], r['report_name']))
# get users measures
meas_q = "SELECT measure_id, measure_name FROM engine.measures WHERE created_user = '******'" % user_id
meas = mng.executeRemoteSQL(project_id, meas_q, True, True, False)
if len(dashs):
for m in meas:
items_dic['measures'].append((m['measure_id'], m['measure_name']))
if items_dic == items_dic_empty:
return {}
else:
return items_dic
def saveGroupProjects():
postData = request.post_vars
upd_priv_q = "UPDATE project_domaingroups SET privilege_id = {priv_id} \
WHERE project_id = '{project_id}' AND domaingroup_id = '{group_id}'"
upd_priv_pj_q = "UPDATE userspace.usergroups SET privilege_id = {priv_id} WHERE usergroup_id = '{group_id}'"
# only one project -> string
if type(postData['projectPrivileges']) is str:
pom = postData['projectPrivileges'].split('|sep|')
if pom[1] != '0':
db.executesql(upd_priv_q.format(priv_id = pom[1],
project_id = pom[0],
group_id = postData['groupId']
)
)
mng.executeRemoteSQL(pom[0], upd_priv_pj_q.format(priv_id = pom[1], group_id = postData['groupId'])
, False, True, False)
# more than one projects -> array
else:
for project in postData['projectPrivileges']:
pom = project.split('|sep|')
if pom[1] == '0':
continue
db.executesql(upd_priv_q.format(priv_id = pom[1],
project_id = pom[0],
group_id = postData['groupId']
)
)
mng.executeRemoteSQL(pom[0], upd_priv_pj_q.format(priv_id = pom[1], group_id = postData['groupId'])
, False, True, False)
return True
def saveUserGroup():
postData = request.post_vars
group_id = postData['domainId']
prev_users_q = "SELECT user_id FROM domaingroup_users WHERE domaingroup_id = '%s'"%group_id
prev_users = db.executesql(prev_users_q, as_dict=True)
prev_users_a = []
if len(prev_users):
prev_users_a = [u['user_id'] for u in prev_users]
cur_users = []
if 'userGroup' in postData:
if type(postData['userGroup']) is str:
cur_users.append(postData['userGroup'])
else:
cur_users = postData['userGroup']
add_users = list(set(cur_users) - set(prev_users_a))
del_users = list(set(prev_users_a) - set(cur_users))
# prida uzivatele do projektu, kde je skupina, pokud tam jeste neni
ins_user_g_q = "INSERT INTO domaingroup_users(user_id, domaingroup_id) VALUES ('{uid}','%s');"%group_id
ins_user_g_pj_q = "INSERT INTO userspace.usergroup_users(usergroup_id, user_id) VALUES ('%s','{uid}');"%group_id
ins_user_q = "INSERT INTO project_users(project_id, user_id, privilege_id) VALUES ('{pid}', '{uid}', -1);"
ins_user_pj_q = "INSERT INTO userspace.users(user_id, username) VALUES ('{uid}', '{username}');\
INSERT INTO auth_user(first_name, last_name, email, user_id, username) \
VALUES ('{f_name}', '{l_name}', '{email}', '{uid}', '{username}');"
del_user_g_q = "DELETE FROM domaingroup_users WHERE user_id IN ({uids}) AND domaingroup_id = '{gid}'"
del_user_q = "DELETE FROM project_users WHERE user_id IN ({uids}) AND project_id = '{pid}'"
del_user_g_pj_q = "DELETE FROM userspace.usergroup_users WHERE user_id IN ({uids}) AND usergroup_id = '%s';"%group_id
del_user_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({uids}); \
DELETE FROM auth_user WHERE user_id IN ({uids});"
projects_q = "SELECT project_id FROM project_domaingroups WHERE domaingroup_id = '%s'"%postData['domainId']
projects = db.executesql(projects_q, as_dict = True)
if len(projects):
for pj in projects:
if len(add_users):
add_users_l = getSqlInCond(add_users)
add_users_a = []
add_users_q = "SELECT first_name, last_name, email, user_id, username FROM auth_user WHERE user_id IN (%s)"%add_users_l
add_users_tmp = db.executesql(add_users_q, as_dict=True)
add_users_a = [u['user_id'] for u in add_users_tmp]
pj_users_a = []
pj_users_q = "SELECT user_id FROM project_users WHERE project_id = '%s'"%pj['project_id']
pj_users = db.executesql(pj_users_q, as_dict=True)
if len(pj_users):
pj_users_a = [u['user_id'] for u in pj_users]
pj_new_users = set(add_users_a) - set(pj_users_a)
# prida uzivatele do tabulky users a user groups
for au in add_users_tmp:
if au['user_id'] in pj_new_users:
db.executesql(ins_user_q.format(pid = pj['project_id'], uid = au['user_id']))
mng.executeRemoteSQL(pj['project_id'], ins_user_pj_q.format(uid = au['user_id'],
username = au['username'],
f_name = au['first_name'],
l_name = au['last_name'],
email = au['email']
)
, False, True, False)
db.executesql(ins_user_g_q.format(uid = au['user_id']))
mng.executeRemoteSQL(pj['project_id'], ins_user_g_pj_q.format(uid = au['user_id'])
, False, True, False)
if len(del_users):
del_users_s = getSqlInCond(del_users)
# vybere uzivatele, kteri pujdou smazat
del_users_q = "SELECT user_id FROM project_users WHERE project_id = '{pid}' \
AND user_id IN ({del_users}) AND user_id NOT IN ( \
SELECT user_id FROM project_users WHERE privilege_id != -1 AND project_id = '{pid}' \
) AND user_id NOT IN ( \
SELECT gu.user_id FROM project_domaingroups pg \
INNER JOIN domaingroup_users gu USING(domaingroup_id) \
WHERE project_id = '{pid}' AND domaingroup_id != '{gid}')\
".format(del_users = del_users_s, pid = pj['project_id'], gid = group_id)
del_users = db.executesql(del_users_q, as_dict = True)
db.executesql(del_user_g_q.format(uids = del_users_s, gid = group_id))
mng.executeRemoteSQL(pj['project_id'], del_user_g_pj_q.format(uids = del_users_s)
, False, True, False)
# uzivatele, kteri pujdou uplne smazat
if len(del_users):
del_users_l = getSqlInCond([uid['user_id'] for uid in del_users])
db.executesql(del_user_q.format(uids = del_users_l, pid = pj['project_id']))
mng.executeRemoteSQL(pj['project_id'], del_user_pj_q.format(uids = del_users_l)
, False, True, False)
else:
ins_user_g_q = "INSERT INTO domaingroup_users(user_id, domaingroup_id) VALUES ('{uid}','%s');"%group_id
del_users_q = "DELETE FROM domaingroup_users WHERE domaingroup_id = '%s' AND user_id IN ({del_users_s})"%group_id
if len(add_users):
for u in add_users:
db.executesql(ins_user_g_q.format(uid = u))
if len(del_users):
del_users_s = getSqlInCond(del_users)
db.executesql(del_users_q.format(del_users_s = del_users_s))
return True
def saveUserGroups():
postData = request.post_vars
user_id = postData['userId']
user_info_q = "SELECT first_name, last_name, email, user_id, username FROM auth_user WHERE user_id = '%s'" % user_id
user_info = db.executesql(user_info_q, as_dict=True)[0]
print postData
prev_user_groups_a = []
prev_user_groups_q = "SELECT domaingroup_id FROM domaingroup_users WHERE user_id = '%s'" % user_id
prev_user_groups = db.executesql(prev_user_groups_q, as_dict=True)
if len(prev_user_groups):
for g in prev_user_groups:
prev_user_groups_a.append(g['domaingroup_id'])
cur_user_groups = []
if 'groupNodes' in postData:
if type(postData['groupNodes']) is str:
cur_user_groups.append(postData['groupNodes'])
else:
cur_user_groups = postData['groupNodes']
new_user_groups = list(set(cur_user_groups) - set(prev_user_groups_a))
del_user_groups = list(set(prev_user_groups_a) - set(cur_user_groups))
user_groups = list(set(prev_user_groups_a + cur_user_groups))
# prida uzivatele do projektu, kde je skupina, pokud tam jeste neni
ins_user_g_q = "INSERT INTO domaingroup_users(user_id, domaingroup_id) VALUES ('%s','{gid}');" % user_id
ins_user_g_pj_q = "INSERT INTO userspace.usergroup_users(usergroup_id, user_id) VALUES ('{gid}','%s');" % user_id
ins_user_q = "INSERT INTO project_users(project_id, user_id, privilege_id) VALUES ('{pid}', '%s', -1);" % user_id
ins_user_pj_q = "INSERT INTO userspace.users(user_id, username) VALUES ('{uid}', '{username}'); \
INSERT INTO auth_user(first_name, last_name, email, user_id, username) \
VALUES ('{f_name}', '{l_name}', '{email}', '{uid}', '{username}');"
del_user_g_q = "DELETE FROM domaingroup_users WHERE user_id = '{user_id}' AND domaingroup_id = '{gid}'"
del_user_g_pj_q = "DELETE FROM userspace.usergroup_users WHERE user_id = '{user_id}' AND \
usergroup_id = '{gid}'"
del_user_q = "DELETE FROM project_users WHERE user_id = '{user_id}' AND project_id = '{pid}'"
del_user_pj_q = "DELETE FROM userspace.users WHERE user_id = '{user_id}'; \
DELETE FROM auth_user WHERE user_id = '{user_id}';".format(
user_id=user_id)
for user_group in user_groups:
projects_q = "SELECT project_id FROM project_domaingroups WHERE domaingroup_id = '%s'" % user_group
projects = db.executesql(projects_q, as_dict=True)
if len(projects):
for pj in projects:
# prida uzivatele do skupin
if user_group in new_user_groups:
# kontrola, jeli uzivatel j*z v projektu
user_pj_q = "SELECT 1 FROM project_users WHERE project_id = '{pid}' AND \
user_id = '{user_id}'".format(
pid=pj['project_id'], user_id=user_id)
user_pj = db.executesql(user_pj_q)
# prida uzivatele do users projektu, pokud tam neni
if not len(user_pj):
db.executesql(ins_user_q.format(pid=pj['project_id']))
mng.executeRemoteSQL(
pj['project_id'],
ins_user_pj_q.format(
uid=user_info['user_id'],
username=user_info['username'],
f_name=user_info['first_name'],
l_name=user_info['last_name'],
email=user_info['email']), False, True, False)
# prida uzivatele do skupiny
db.executesql(ins_user_g_q.format(gid=user_group))
mng.executeRemoteSQL(
pj['project_id'],
ins_user_g_pj_q.format(gid=user_group), False, True,
False)
# vymaze uzivatele ze skupin
elif user_group in del_user_groups:
# ma smazat uzivatele v projektu?
delUser_q = "SELECT 1 FROM project_users WHERE project_id = '{pid}' \
AND user_id = '{user_id}' AND user_id NOT IN ( \
SELECT user_id FROM project_users WHERE privilege_id != -1 AND project_id = '{pid}' \
AND user_id = '{user_id}') AND user_id NOT IN ( \
SELECT gu.user_id FROM project_domaingroups pg \
INNER JOIN domaingroup_users gu USING(domaingroup_id) \
WHERE project_id = '{pid}' AND domaingroup_id != '{gid}' AND gu.user_id = '{user_id}')\
".format(user_id=user_id,
pid=pj['project_id'],
gid=user_group)
delUser = db.executesql(delUser_q)
# smaze uzivatele v projektu
if len(delUser):
db.executesql(
del_user_q.format(pid=pj['project_id'],
user_id=user_id))
mng.executeRemoteSQL(pj['project_id'], del_user_pj_q,
False, True, False)
# vymaze pouze ze skupiny
db.executesql(
del_user_g_q.format(gid=user_group, user_id=user_id))
mng.executeRemoteSQL(
pj['project_id'],
del_user_g_pj_q.format(gid=user_group,
user_id=user_id), False, True,
False)
# skupina neni v zadnem projektu - prida/ odebere uzivatele pouze do skupiny
else:
# prida
if user_group in new_user_groups:
db.executesql(ins_user_g_q.format(gid=user_group))
# odebere
elif user_group in del_user_groups:
print "MAZU VOE"
db.executesql(
del_user_g_q.format(gid=user_group, user_id=user_id))
return True
def saveUserGroup():
postData = request.post_vars
group_id = postData['domainId']
prev_users_q = "SELECT user_id FROM domaingroup_users WHERE domaingroup_id = '%s'" % group_id
prev_users = db.executesql(prev_users_q, as_dict=True)
prev_users_a = []
if len(prev_users):
prev_users_a = [u['user_id'] for u in prev_users]
cur_users = []
if 'userGroup' in postData:
if type(postData['userGroup']) is str:
cur_users.append(postData['userGroup'])
else:
cur_users = postData['userGroup']
add_users = list(set(cur_users) - set(prev_users_a))
del_users = list(set(prev_users_a) - set(cur_users))
# prida uzivatele do projektu, kde je skupina, pokud tam jeste neni
ins_user_g_q = "INSERT INTO domaingroup_users(user_id, domaingroup_id) VALUES ('{uid}','%s');" % group_id
ins_user_g_pj_q = "INSERT INTO userspace.usergroup_users(usergroup_id, user_id) VALUES ('%s','{uid}');" % group_id
ins_user_q = "INSERT INTO project_users(project_id, user_id, privilege_id) VALUES ('{pid}', '{uid}', -1);"
ins_user_pj_q = "INSERT INTO userspace.users(user_id, username) VALUES ('{uid}', '{username}');\
INSERT INTO auth_user(first_name, last_name, email, user_id, username) \
VALUES ('{f_name}', '{l_name}', '{email}', '{uid}', '{username}');"
del_user_g_q = "DELETE FROM domaingroup_users WHERE user_id IN ({uids}) AND domaingroup_id = '{gid}'"
del_user_q = "DELETE FROM project_users WHERE user_id IN ({uids}) AND project_id = '{pid}'"
del_user_g_pj_q = "DELETE FROM userspace.usergroup_users WHERE user_id IN ({uids}) AND usergroup_id = '%s';" % group_id
del_user_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({uids}); \
DELETE FROM auth_user WHERE user_id IN ({uids});"
projects_q = "SELECT project_id FROM project_domaingroups WHERE domaingroup_id = '%s'" % postData[
'domainId']
projects = db.executesql(projects_q, as_dict=True)
if len(projects):
for pj in projects:
if len(add_users):
add_users_l = getSqlInCond(add_users)
add_users_a = []
add_users_q = "SELECT first_name, last_name, email, user_id, username FROM auth_user WHERE user_id IN (%s)" % add_users_l
add_users_tmp = db.executesql(add_users_q, as_dict=True)
add_users_a = [u['user_id'] for u in add_users_tmp]
pj_users_a = []
pj_users_q = "SELECT user_id FROM project_users WHERE project_id = '%s'" % pj[
'project_id']
pj_users = db.executesql(pj_users_q, as_dict=True)
if len(pj_users):
pj_users_a = [u['user_id'] for u in pj_users]
pj_new_users = set(add_users_a) - set(pj_users_a)
# prida uzivatele do tabulky users a user groups
for au in add_users_tmp:
if au['user_id'] in pj_new_users:
db.executesql(
ins_user_q.format(pid=pj['project_id'],
uid=au['user_id']))
mng.executeRemoteSQL(
pj['project_id'],
ins_user_pj_q.format(uid=au['user_id'],
username=au['username'],
f_name=au['first_name'],
l_name=au['last_name'],
email=au['email']), False,
True, False)
db.executesql(ins_user_g_q.format(uid=au['user_id']))
mng.executeRemoteSQL(
pj['project_id'],
ins_user_g_pj_q.format(uid=au['user_id']), False, True,
False)
if len(del_users):
del_users_s = getSqlInCond(del_users)
# vybere uzivatele, kteri pujdou smazat
del_users_q = "SELECT user_id FROM project_users WHERE project_id = '{pid}' \
AND user_id IN ({del_users}) AND user_id NOT IN ( \
SELECT user_id FROM project_users WHERE privilege_id != -1 AND project_id = '{pid}' \
) AND user_id NOT IN ( \
SELECT gu.user_id FROM project_domaingroups pg \
INNER JOIN domaingroup_users gu USING(domaingroup_id) \
WHERE project_id = '{pid}' AND domaingroup_id != '{gid}')\
".format(del_users=del_users_s,
pid=pj['project_id'],
gid=group_id)
del_users = db.executesql(del_users_q, as_dict=True)
db.executesql(
del_user_g_q.format(uids=del_users_s, gid=group_id))
mng.executeRemoteSQL(pj['project_id'],
del_user_g_pj_q.format(uids=del_users_s),
False, True, False)
# uzivatele, kteri pujdou uplne smazat
if len(del_users):
del_users_l = getSqlInCond(
[uid['user_id'] for uid in del_users])
db.executesql(
del_user_q.format(uids=del_users_l,
pid=pj['project_id']))
mng.executeRemoteSQL(
pj['project_id'],
del_user_pj_q.format(uids=del_users_l), False, True,
False)
else:
ins_user_g_q = "INSERT INTO domaingroup_users(user_id, domaingroup_id) VALUES ('{uid}','%s');" % group_id
del_users_q = "DELETE FROM domaingroup_users WHERE domaingroup_id = '%s' AND user_id IN ({del_users_s})" % group_id
if len(add_users):
for u in add_users:
db.executesql(ins_user_g_q.format(uid=u))
if len(del_users):
del_users_s = getSqlInCond(del_users)
db.executesql(del_users_q.format(del_users_s=del_users_s))
return True
def setProjectAdmins():
adminsId = request.args[0]
projectId = request.args[1]
# uzivatele kteri byli admini (except domain admin)
prev_admins_q = "SELECT user_id FROM project_users WHERE project_id='{project_id}' \
AND project_admin= True AND user_id != ( \
SELECT user_id FROM domain_users WHERE domain_id = ( \
SELECT domain_id FROM domain_projects WHERE project_id = '{project_id}' \
) AND domain_admin = True )".format(
project_id=projectId)
prev_admins_a = []
prev_admins = db.executesql(prev_admins_q, as_dict=True)
if len(prev_admins):
for admin in prev_admins:
prev_admins_a.append(admin['user_id'])
upd_admin_q = "UPDATE project_users SET project_admin= {isAmdmin}, privilege_id = 1 \
WHERE project_id='{project_id}' AND user_id IN ({admins_s})"
upd_admin_pj_q = "UPDATE userspace.users SET project_admin= {isAmdmin}, privilege_id = 1 \
WHERE user_id IN ({admins_s})"
new_admins = []
rem_admins = []
if adminsId != 'null':
adminList = adminsId.split('_')
# novy admini update project_admin True
new_admins = list(set(adminList) - set(prev_admins_a))
# uz nejsou admini update project_admin False
rem_admins = list(set(prev_admins_a) - set(adminList))
new_admins_s = getSqlInCond(new_admins)
rem_admins_s = getSqlInCond(rem_admins)
if len(new_admins):
db.executesql(
upd_admin_q.format(project_id=projectId,
admins_s=new_admins_s,
isAmdmin='True'))
mng.executeRemoteSQL(
projectId,
upd_admin_pj_q.format(admins_s=new_admins_s, isAmdmin='True'),
False, True, False)
if len(rem_admins):
db.executesql(
upd_admin_q.format(project_id=projectId,
admins_s=rem_admins_s,
isAmdmin='False'))
mng.executeRemoteSQL(
projectId,
upd_admin_pj_q.format(admins_s=rem_admins_s, isAmdmin='False'),
False, True, False)
else:
if len(prev_admins_a):
rem_admins_s = getSqlInCond(prev_admins_a)
db.executesql(
upd_admin_q.format(project_id=projectId,
admins_s=rem_admins_s,
isAmdmin='False'))
mng.executeRemoteSQL(
projectId,
upd_admin_pj_q.format(admins_s=rem_admins_s, isAmdmin='False'),
False, True, False)
return True
def setGroupPrivileges():
groupId = request.args[0]
projectId = request.args[1]
privilegeId = request.args[2]
new_groups = []
del_groups = []
# vytahne puvodni skupiny
prev_groups_a = []
prev_groups_q = "SELECT domaingroup_id FROM project_domaingroups WHERE project_id = '{project_id}' \
AND privilege_id = {priv_id}".format(project_id=projectId,
priv_id=privilegeId)
prev_groups = db.executesql(prev_groups_q, as_dict=True)
for prev_group in prev_groups:
prev_groups_a.append(prev_group['domaingroup_id'])
prev_groups_all_a = []
prev_groups_all_q = "SELECT domaingroup_id FROM project_domaingroups WHERE project_id = '{project_id}' \
".format(project_id=projectId)
prev_groups_all = db.executesql(prev_groups_all_q, as_dict=True)
for prev_group in prev_groups_all:
prev_groups_all_a.append(prev_group['domaingroup_id'])
if groupId != 'null':
groupList = groupId.split('_')
new_groups = list(set(groupList) - set(prev_groups_a))
del_groups = list(set(prev_groups_a) - set(groupList))
cur_groups = list(set(prev_groups_all_a) - set(del_groups))
#=======================================================================
# MAZANI SKUPIN PROJEKTU
#=======================================================================
if len(del_groups):
del_groups_s = getSqlInCond(del_groups)
cur_groups_s = getSqlInCond(cur_groups)
# smaze uzivatele podle skupiny z tab project users, kteri nemaji svoje priv nebo nejsou v jine skupine
del_users_q = "SELECT user_id FROM project_users WHERE user_id IN ( \
SELECT dgu.user_id FROM domaingroup_users dgu \
INNER JOIN project_domaingroups pdg on pdg.domaingroup_id = dgu.domaingroup_id \
INNER JOIN project_users pu on pu.project_id = pdg.project_id \
WHERE pdg.project_id = '{project_id}' \
AND pdg.domaingroup_id in ({del_groups_s}) AND pu.privilege_id = -1 \
) AND project_id = '{project_id}' \
AND user_id NOT IN ( \
SELECT dgu.user_id FROM domaingroup_users dgu \
INNER JOIN project_domaingroups pdg on pdg.domaingroup_id = dgu.domaingroup_id \
INNER JOIN project_users pu on pu.project_id = pdg.project_id \
WHERE pdg.project_id = '{project_id}' \
AND pdg.domaingroup_id IN ({cur_groups_s})) \
".format(project_id=projectId,
del_groups_s=del_groups_s,
cur_groups_s=cur_groups_s)
del_users = db.executesql(del_users_q, as_dict=True)
if len(del_users):
del_users_a = []
for u in del_users:
del_users_a.append(u['user_id'])
del_users_s = getSqlInCond(del_users_a)
del_users_q = "DELETE FROM project_users WHERE user_id IN ({del_users_s}) \
AND project_id = '{project_id}'".format(
del_users_s=del_users_s, project_id=projectId)
db.executesql(del_users_q)
# vymaze uzvivatele v DB projektu
del_users_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({del_users_s}) \
".format(del_users_s=del_users_s)
mng.executeRemoteSQL(projectId, del_users_pj_q, False, True,
False)
# smaze skupiny
del_groups_q = "DELETE FROM project_domaingroups WHERE project_id = '{project_id}' \
AND domaingroup_id IN ({del_groups_s})".format(
project_id=projectId, del_groups_s=del_groups_s)
db.executesql(del_groups_q)
# smaze skupiny v DB projektu
del_groups_pj_q = "DELETE FROM userspace.usergroups WHERE usergroup_id IN ({del_groups_s}) \
".format(del_groups_s=del_groups_s)
mng.executeRemoteSQL(projectId, del_groups_pj_q, False, True,
False)
#=======================================================================
# PRIDA NOVE SKUPINY DO PROJEKTU, PRIDA UZIVATELE PODLE SKUPIN
#=======================================================================
if len(new_groups):
new_groups_s = getSqlInCond(new_groups)
# prida skupiny
# nove skupiny
new_groups_q = "SELECT domaingroup_id, domaingroup_name, domaingroup_descr FROM domain_groups \
WHERE domaingroup_id IN ({new_groups_s})".format(
new_groups_s=new_groups_s)
new_groups = db.executesql(new_groups_q, as_dict=True)
ins_group_q = "INSERT INTO project_domaingroups(domaingroup_id, project_id, privilege_id) \
VALUES ('{dg_id}', '{project_id}', {priv_id});"
ins_group_pj_q = "INSERT INTO userspace.usergroups(usergroup_id, usergroup_name, usergroup_descr, privilege_id) \
VALUES ('{ug_id}', '{ug_name}', '{ug_descr}', {priv_id});"
for group in new_groups:
db.executesql(
ins_group_q.format(dg_id=group['domaingroup_id'],
project_id=projectId,
priv_id=privilegeId))
mng.executeRemoteSQL(
projectId,
ins_group_pj_q.format(ug_id=group['domaingroup_id'],
ug_name=group['domaingroup_name'],
ug_descr=group['domaingroup_descr'],
priv_id=privilegeId), False, True,
False)
# prida uzivatele do projektu, kteri jsou novy a pridani skupinou
new_users_q = "SELECT DISTINCT au.first_name, au.last_name, au.email, au.user_id, au.username FROM domaingroup_users \
INNER JOIN auth_user au USING(user_id) \
WHERE domaingroup_id IN ({new_groups_s}) AND au.user_id NOT IN ( \
SELECT user_id FROM project_users WHERE project_id = '{project_id}') \
".format(new_groups_s=new_groups_s,
project_id=projectId)
new_users = db.executesql(new_users_q, as_dict=True)
if len(new_users):
ins_user_q = "INSERT INTO project_users(project_id, user_id, privilege_id) \
VALUES ('{project_id}', '{uid}', -1)"
ins_user_pj_q = "INSERT INTO userspace.users(user_id, username) \
VALUES ('{uid}', '{username}');\
INSERT INTO auth_user(first_name, last_name, email, user_id, username) \
VALUES ('{f_name}', '{l_name}', '{email}', '{uid}', '{username}');"
for user in new_users:
db.executesql(
ins_user_q.format(project_id=projectId,
uid=user['user_id']))
mng.executeRemoteSQL(
projectId,
ins_user_pj_q.format(uid=user['user_id'],
username=user['username'],
f_name=user['first_name'],
l_name=user['last_name'],
email=user['email']), False, True,
False)
# prida do DB projektu kombinace group - user
new_users_groups_q = "SELECT user_id, domaingroup_id FROM domaingroup_users \
WHERE domaingroup_id IN ({new_groups_s}) \
".format(new_groups_s=new_groups_s,
project_id=projectId)
new_users_groups = db.executesql(new_users_groups_q, as_dict=True)
if len(new_users_groups):
ins_ug_q = "INSERT INTO userspace.usergroup_users(usergroup_id, user_id) \
VALUES ('{gid}', '{uid}');"
for ug in new_users_groups:
mng.executeRemoteSQL(
projectId,
ins_ug_q.format(uid=ug['user_id'],
gid=ug['domaingroup_id']), False, True,
False)
else:
del_groups_q = "SELECT domaingroup_id FROM project_domaingroups WHERE project_id = '{project_id}' \
AND privilege_id = {priv_id}".format(
project_id=projectId, priv_id=privilegeId)
del_groups = db.executesql(del_groups_q, as_dict=True)
if len(del_groups):
del_groups_a = []
for g in del_groups:
del_groups_a.append(g['domaingroup_id'])
del_groups_s = getSqlInCond(del_groups_a)
# vymaze uzivatele ze skupiny, pokud jsou pouze pridani touto skupinou
# tj. nejsou pridani jako users nebo v jine skupine v projektu
del_users_q = "SELECT user_id FROM project_users WHERE user_id IN ( \
SELECT dgu.user_id FROM domaingroup_users dgu \
INNER JOIN project_domaingroups pdg on pdg.domaingroup_id = dgu.domaingroup_id \
INNER JOIN project_users pu on pu.project_id = pdg.project_id \
WHERE pdg.project_id = '{project_id}' \
AND pdg.domaingroup_id in ({del_groups_s}) AND pu.privilege_id = -1 \
) AND project_id = '{project_id}' AND privilege_id = -1 \
AND user_id NOT IN ( \
SELECT dgu.user_id FROM domaingroup_users dgu \
INNER JOIN project_domaingroups pdg on pdg.domaingroup_id = dgu.domaingroup_id \
INNER JOIN project_users pu on pu.project_id = pdg.project_id \
WHERE pdg.project_id = '{project_id}' \
AND pdg.domaingroup_id IN ( \
SELECT domaingroup_id FROM project_domaingroups WHERE \
domaingroup_id NOT IN ({del_groups_s}) AND project_id = '{project_id}' ) ) \
".format(project_id=projectId,
del_groups_s=del_groups_s)
del_users = db.executesql(del_users_q, as_dict=True)
if len(del_users):
del_users_a = []
for u in del_users:
del_users_a.append(u['user_id'])
del_users_s = getSqlInCond(del_users_a)
del_users_q = "DELETE FROM project_users WHERE user_id IN ({del_users_s}) \
AND project_id = '{project_id}'".format(
del_users_s=del_users_s, project_id=projectId)
db.executesql(del_users_q)
# vymaze uzvivatele v DB projektu
del_users_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({del_users_s}); \
DELETE FROM auth_user WHERE user_id IN ({del_users_s}); \
".format(del_users_s=del_users_s)
mng.executeRemoteSQL(projectId, del_users_pj_q, False, True,
False)
del_group_q = "DELETE FROM project_domaingroups WHERE domaingroup_id IN ({del_groups_s}) \
AND project_id = '{project_id}'".format(
del_groups_s=del_groups_s, project_id=projectId)
db.executesql(del_group_q)
# smaze skupiny v DB projektu
del_groups_pj_q = "DELETE FROM userspace.usergroups WHERE usergroup_id IN ({del_groups_s}) \
".format(del_groups_s=del_groups_s)
mng.executeRemoteSQL(projectId, del_groups_pj_q, False, True,
False)
return True
def setUserPrivileges():
userId = request.args[0]
projectId = request.args[1]
privilegeId = request.args[2]
new_users = []
del_users = []
prev_users_a = []
prev_users_q = "SELECT user_id FROM project_users WHERE project_id = '{project_id}' \
AND project_admin = False".format(project_id=projectId)
prev_users = db.executesql(prev_users_q, as_dict=True)
for prev_user in prev_users:
prev_users_a.append(prev_user['user_id'])
# pokud userId = null -> vymazou se vsichni uzivatele s prislusnym privilege pro dany projekt
# kteri nejsou soucasti skupin, tem se updatne priviliga na -1, rizeno skupinou
if userId != 'null':
userList = userId.split('_')
# vytahne puvodni uzivatele - users
new_users = list(set(userList) - set(prev_users_a))
del_users = list(set(prev_users_a) - set(userList))
#=======================================================================
# MAZANI UZIVATELU Z PROJEKTU
#=======================================================================
# zmeni privilege na -1 pro uzivatele, kteri jsou pridani skupinou
if len(del_users):
del_users_s = getSqlInCond(del_users)
del_users_update_q = "UPDATE project_users SET privilege_id = -1 WHERE user_id IN ( \
SELECT user_id FROM domaingroup_users dgu \
INNER JOIN project_domaingroups pdg on pdg.domaingroup_id = dgu.domaingroup_id \
WHERE project_id = '{project_id}' \
AND user_id IN ({del_users_s}) \
) AND project_id = '{project_id}'".format(
project_id=projectId, del_users_s=del_users_s)
db.executesql(del_users_update_q)
# update uzivatelu v DB projektu - update priv na -1
upd_users_q = "SELECT user_id FROM project_users WHERE project_id = '{project_id}' \
AND privilege_id = -1 ".format(
project_id=projectId)
udp_users = db.executesql(upd_users_q, as_dict=True)
if len(udp_users):
upd_users_pj_q = "UPDATE userspace.users SET privilege_id = -1 WHERE user_id IN ({users})"
tmp_user_list = []
for u in udp_users:
tmp_user_list.append(u['user_id'])
mng.executeRemoteSQL(
projectId,
upd_users_pj_q.format(users=getSqlInCond(tmp_user_list)),
False, True, False)
# users to delete
users_q = "SELECT user_id FROM project_users WHERE user_id IN ({del_users_s}) \
AND privilege_id = {priv_id} AND project_id = '{project_id}' AND project_admin = False \
".format(del_users_s=del_users_s,
priv_id=privilegeId,
project_id=projectId)
users = db.executesql(users_q, as_dict=True)
users_a = []
for u in users:
users_a.append(u['user_id'])
# users and their items (dashs, reps, meas)
u_items_dic = {}
for u in users_a:
tmp_items = users_mod.getUserItems(projectId, u)
if tmp_items != False:
u_items_dic[u] = tmp_items
get_del_users_q = "SELECT user_id FROM project_users WHERE user_id IN ({del_users_s}) \
AND privilege_id = {priv_id} AND project_id = '{project_id}' AND project_admin = False \
".format(del_users_s=del_users_s,
priv_id=privilegeId,
project_id=projectId)
del_users = db.executesql(get_del_users_q, as_dict=True)
if len(del_users):
del_users_s = getSqlInCond([u['user_id'] for u in del_users])
del_users_q = "DELETE FROM project_users WHERE user_id IN ({del_users_s}) \
".format(del_users_s=del_users_s,
priv_id=privilegeId,
project_id=projectId)
db.executesql(del_users_q)
# vymaze uzivatele v DB projektu
del_users_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({del_users_s}) ; \
DELETE FROM auth_user WHERE user_id IN ({del_users_s});\
".format(del_users_s=del_users_s,
priv_id=privilegeId)
mng.executeRemoteSQL(projectId, del_users_pj_q, False, True,
False)
#=======================================================================
# UPDATNE PRIVILEGIA U SOUCASNYCH A PRIDA NOVE DO PROJEKTU
#=======================================================================
# update
upd_users_s = getSqlInCond(userList)
upd_users_q = "UPDATE project_users SET privilege_id = {priv_id} \
WHERE user_id IN ({upd_users_s}) AND project_id = '{project_id}'\
".format(priv_id=privilegeId,
upd_users_s=upd_users_s,
project_id=projectId)
db.executesql(upd_users_q)
upd_users_pj_q = "UPDATE userspace.users SET privilege_id = {priv_id} \
WHERE user_id IN ({upd_users_s})".format(
priv_id=privilegeId, upd_users_s=upd_users_s)
mng.executeRemoteSQL(projectId, upd_users_pj_q, False, True, False)
# pridani
if len(new_users):
ins_user_q = "INSERT INTO project_users(project_id, user_id, privilege_id) \
VALUES ('{project_id}', '{uid}', {priv_id})"
ins_user_pj_q = "INSERT INTO userspace.users(user_id, privilege_id, username) \
VALUES ('{uid}', {priv_id}, '{username}'); \
INSERT INTO auth_user(first_name, last_name, email, user_id, username) \
VALUES ('{f_name}', '{l_name}', '{email}', '{uid}', '{username}');"
new_user_q = "SELECT first_name, last_name, email, user_id, username FROM auth_user \
WHERE user_id IN ({users})".format(
users=getSqlInCond(new_users))
new_users = db.executesql(new_user_q, as_dict=True)
for user in new_users:
db.executesql(
ins_user_q.format(project_id=projectId,
uid=user['user_id'],
priv_id=privilegeId))
mng.executeRemoteSQL(
projectId,
ins_user_pj_q.format(uid=user['user_id'],
priv_id=privilegeId,
username=user['username'],
f_name=user['first_name'],
l_name=user['last_name'],
email=user['email']), False, True,
False)
else:
# oddelan vsichni uzivatele pro dane privilegium
prev_users_q = "SELECT user_id FROM project_users WHERE project_id = '{project_id}' \
AND privilege_id = {priv_id}".format(
project_id=projectId, priv_id=privilegeId)
prev_users = db.executesql(prev_users_q, as_dict=True)
if len(prev_users):
prev_users_a = []
for user in prev_users:
prev_users_a.append(user['user_id'])
prev_users_s = getSqlInCond(prev_users_a)
del_users_update_q = "UPDATE project_users SET privilege_id = -1 WHERE user_id IN ( \
SELECT user_id FROM domaingroup_users dgu \
INNER JOIN project_domaingroups pdg on pdg.domaingroup_id = dgu.domaingroup_id \
WHERE project_id = '{project_id}' \
AND user_id IN ({prev_users_s}) \
) AND project_id = '{project_id}'".format(
project_id=projectId, prev_users_s=prev_users_s)
db.executesql(del_users_update_q)
# update uzivatelu v DB projektu - update priv na -1
upd_users_q = "SELECT user_id FROM project_users WHERE project_id = '{project_id}' \
AND privilege_id = -1 ".format(
project_id=projectId)
udp_users = db.executesql(upd_users_q, as_dict=True)
if len(udp_users):
upd_users_pj_q = "UPDATE userspace.users SET privilege_id = -1 WHERE user_id IN ({users})"
tmp_user_list = []
for u in udp_users:
tmp_user_list.append(u['user_id'])
mng.executeRemoteSQL(
projectId,
upd_users_pj_q.format(users=getSqlInCond(tmp_user_list)),
False, True, False)
get_del_users_q = "SELECT user_id FROM project_users WHERE user_id IN ({prev_users_s}) \
AND project_id = '{project_id}' AND privilege_id = {priv_id} AND project_admin = False\
".format(prev_users_s=prev_users_s,
project_id=projectId,
priv_id=privilegeId)
del_users = db.executesql(get_del_users_q, as_dict=True)
if len(del_users):
del_users_s = getSqlInCond([u['user_id'] for u in del_users])
del_users_q = "DELETE FROM project_users WHERE user_id IN ({del_users_s}) \
".format(del_users_s=del_users_s,
priv_id=privilegeId,
project_id=projectId)
db.executesql(del_users_q)
# vymaze uzivatele v DB projektu
del_users_pj_q = "DELETE FROM userspace.users WHERE user_id IN ({del_users_s}) ; \
DELETE FROM auth_user WHERE user_id IN ({del_users_s});\
".format(del_users_s=del_users_s,
priv_id=privilegeId)
mng.executeRemoteSQL(projectId, del_users_pj_q, False, True,
False)
return True