def get_group_id_by_name(name, db_config):
select_query = "select id from %s where name = '%s'" % (db_config.group_table, name)
group_id = db_utils.return_select_results_as_list(select_query, db_config)
if len(group_id) > 0:
return group_id[0][0]
else:
return None
def get_group_object_by_name(name, db_config):
select_query = "select json_build_object('name', name) from %s" % db_config.group_table
select_query += " where name = '%s'" % name
group_json = db_utils.return_select_results_as_list(select_query, db_config)
if len(group_json) > 0:
group_json = group_json[0][0]
return create_group_object(group_json)
else:
return None
def get_user_object_by_userid(userid, db_config):
select_query = "select json_build_object('first_name', first_name, " \
"'last_name', last_name, 'userid', userid)" \
" from %s" % db_config.user_table
select_query += " where userid = '%s'" % userid
user_json = db_utils.return_select_results_as_list(select_query, db_config)
if len(user_json) > 0:
user_json = user_json[0][0]
return create_user_object(user_json)
else:
return None
def GET(self, userid):
user = get_user_object_by_userid(userid, self.db_config)
if user is not None:
user_id = get_user_id_by_userid(user['userid'], self.db_config)
select_query = "select name from %s" % self.db_config.group_table
select_query += " g inner join %s ug on g.id = ug.group_id where ug.user_id = %s " \
% (self.db_config.user_group_table, user_id)
groups = db_utils.return_select_results_as_list(select_query, self.db_config)
groups = [i[0] for i in groups] # flatten
cherrypy.response.body = dict()
cherrypy.response.body['userid'] = user['userid']
cherrypy.response.body['last_name'] = user['last_name']
cherrypy.response.body['first_name'] = user['first_name']
cherrypy.response.body['groups'] = groups
return json.dumps(cherrypy.response.body)
else:
raise cherrypy.HTTPError(404, 'No records found for userid: %s' % userid)
def GET(self, name):
#get group_id
select_query = "select id from %s" % self.db_config.group_table
select_query += " where name = '%s'" % name
group_id = get_group_id_by_name(name, self.db_config)
if group_id is None:
raise cherrypy.HTTPError(404, 'No records found for name: %s' % name)
# get userids for group
select_query = "select userid from %s" % self.db_config.user_table
select_query += " u inner join %s ug on u.id = ug.user_id where ug.group_id = %s" \
% (self.db_config.user_group_table, group_id)
userids = db_utils.return_select_results_as_list(select_query, self.db_config)
userids = [i[0] for i in userids] # flatten
cherrypy.response.body = dict()
cherrypy.response.body['userids'] = userids
return json.dumps(cherrypy.response.body)
def get_user_id_by_userid(userid, db_config):
select_query = "select id from %s where userid = '%s'" % (db_config.user_table, userid)
user_id = db_utils.return_select_results_as_list(select_query, db_config)[0][0]
return user_id
