def getSubscriptions(self, **kwargs):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = " SELECT id, user_id, plan_id, subscription_id, " \
" group_id, sub_timestamp, exp_timestamp, " \
" status, type, count, used, tier, recurring " \
" FROM subscriptions "
if len(kwargs) != 0:
where = "WHERE"
for key, value in kwargs.items():
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(cond=condition)
else:
where += (" AND {cond} ").format(cond=condition)
sql += " "
sql += where
cursor.execute(sql)
subscriptions_data = cursor.fetchall()
return subscriptions_data
except Exception as e:
logger.debug(e)
def deleteData(self, **kwargs):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn) as cursor:
try:
sql = "DELETE FROM %s" % (self.tableName)
if len(kwargs) != 0:
where = "WHERE"
for key, value in kwargs.items():
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(cond=condition)
else:
where += (" AND {cond} ").format(cond=condition)
sql += " "
sql += where
cursor.execute(sql)
devconn.commit()
except Exception as e:
logger.debug(e)
def getActivationToken(self, **kwargs):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = " SELECT * " \
" FROM activation_tokens "
if len(kwargs) != 0:
where = "WHERE"
for key, value in kwargs.items():
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(cond=condition)
else:
where += (" AND {cond} ").format(cond=condition)
sql += " "
sql += where
cursor.execute(sql)
actvtn_tkn = cursor.fetchall()
return actvtn_tkn
except Exception as e:
logger.debug(e)
def getLicenses(self, **kwargs):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = " SELECT id, uuid, group_id, token, " \
" system_serial, manufacturer, model, computer_name " \
" FROM licenses "
if len(kwargs) != 0:
where = "WHERE"
for key, value in kwargs.items():
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(cond=condition)
else:
where += (" AND {cond} ").format(cond=condition)
sql += " "
sql += where
sql += "ORDER BY id "
cursor.execute(sql)
licenses_data = cursor.fetchall()
return licenses_data
except Exception as e:
logger.debug(e)
def getUserInfo(self, **kwargs):
methodName = sys._getframe().f_code.co_name
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = "SELECT id, group_id, username, uuid, stripe_cust_id " \
" FROM users "
if len(kwargs) != 0:
where = "WHERE"
for key, value in kwargs.items():
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(cond=condition)
else:
where += (" AND {cond} ").format(cond=condition)
sql += " "
sql += where
cursor.execute(sql)
userInfo = cursor.fetchall()
return (userInfo)
except Exception as e:
logger.debug(e)
self.response = make_response(constants.USER_INFO_FAIL,
methodName,
constants.BAD_REQUEST_400)
def getGroupInfo(self, **kwargs):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = " SELECT id, name, parent_id, uuid, grplvl " \
" FROM groups "
if len(kwargs) != 0:
where = "WHERE"
for key, value in kwargs.items():
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(cond=condition)
else:
where += (" AND {cond} ").format(cond=condition)
sql += " "
sql += where
cursor.execute(sql)
groups_data = cursor.fetchall() #cursor.fetchone()
return groups_data
except Exception as e:
logger.debug(e)
def deleteTableData(self):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn) as cursor:
try:
sql = "DELETE FROM %s " % (self.tableName)
cursor.execute(sql)
devconn.commit()
except Exception as e:
logger.debug(e)
def deleteActivationTokens(cls):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn) as cursor:
try:
sql = "DELETE FROM activation_tokens"
cursor.execute(sql)
devconn.commit()
except Exception as e:
logger.debug(e)
def getTableData(self, **kwargs):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
col_sql = "SELECT COLUMN_NAME FROM information_schema.columns " \
" WHERE table_schema = \"%s\" AND " \
" table_name = \"%s\" " % (common.mdb, self.tableName)
cursor.execute(col_sql)
field_names = cursor.fetchall()
# field_names is a list of dicts with keys 'COLUMN_NAME' and field names as values.
# convert to field names list
field_names_list = []
for field_dict in field_names:
field_names_list.append(list(field_dict.values()))
sql = " SELECT * " \
" FROM %s" % (self.tableName)
if len(kwargs) != 0:
where = "WHERE"
stmt_line = ""
for key, value in kwargs.items():
for list_item in field_names_list:
if key in list_item:
if type(value) == int:
condition = (" {field} = {_data} ").format(
field=key, _data=value)
else:
condition = (
" {field} = \"{_data}\" ").format(
field=key, _data=value)
if where == "WHERE":
where += (" {cond} ").format(
cond=condition)
else:
where += (" AND {cond} ").format(
cond=condition)
if key == "statement": # statements after WHERE should follow correct SQL style
stmt_line += " "
stmt_line += value
sql += " "
if where != "WHERE":
sql += where
sql += stmt_line
cursor.execute(sql)
tbl_dat = cursor.fetchall()
return tbl_dat
except Exception as e:
logger.debug(e)
def getAssetsInGroup(self):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = (
" SELECT S.id, S.deviceid, S.name, S.group_id, S.uuid "
" FROM sentinels S "
"INNER JOIN groups G "
" WHERE G.id = {grp_id} "
" AND G.id = S.group_id "
" ORDER BY S.id").format(grp_id=self.dvc_grp_dict["id"])
cursor.execute(sql)
asset_dict_list = cursor.fetchall()
return asset_dict_list
except Exception as e:
logger.debug(e)
def getUsersInGroup(self):
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn, dictionary=True) as cursor:
try:
sql = (
" SELECT U.id, U.fname, U.group_id, U.lname, U.username, U.uuid "
" FROM users U "
"INNER JOIN groups G "
" WHERE G.id = {grp_id} "
" AND G.id = U.group_id "
" ORDER BY U.id").format(grp_id=self.dvc_grp_dict["id"])
cursor.execute(sql)
user_dict_list = cursor.fetchall()
return user_dict_list
except Exception as e:
logger.debug(e)
def getUgrInfo(self):
methodName = sys._getframe().f_code.co_name
#logger.debug("\n\n Inside %s" % methodName) #CTO
with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
devconn) as cursor:
#logger.debug("AFT get_db_connection()") #CTO
try:
sql = "SELECT UGR.id, UGR.group_id, UGR.role_tag, " \
" UGR.uuid, G.name, G.parent_id, U.id, U.uuid " \
" FROM groups G, user_group_roles UGR, users U" \
" WHERE UGR.user_id = U.id " \
" AND G.id = UGR.group_id " \
" AND U.username = \"%s\" " \
" ORDER BY UGR.id " % (self.username)
cursor.execute(sql)
rows = cursor.fetchall()
ugrInfo = []
for row in rows:
self.user_id = row[6]
self.user_uuid = row[7]
self.group_id = row[1]
self.groupname = row[4]
self.parent_id = row[5]
self.ugr_id = row[0]
self.role_tag = row[2]
self.ugr_uuid = row[3]
ugrTmp = {}
ugrTmp = {
"user_id": self.user_id,
"user_uuid": self.user_uuid,
"username": self.username,
"group_id": self.group_id,
"groupname": self.groupname,
"parent_id": self.parent_id,
"ugr_id": self.ugr_id,
"role_tag": self.role_tag,
"ugr_uuid": self.ugr_uuid
}
ugrInfo.append(ugrTmp)
return (ugrInfo)
except Exception as e:
logger.debug(e)