def addAttributes(Attr_Args):
try:
with con.cursor() as cur:
cur.callproc('deletePrivateAttribute', Attr_Args)
con.commit()
except Exception as ex:
print(ex)
def getTableAttributesRepresentNameById(result_1, id_category):
try:
with con.cursor() as cur:
attributes_by_Id = {'Attributes': []}
for j, second_layer in enumerate(result_1, 0):
attributeObject = {
'Id': second_layer[0],
'Name': second_layer[1],
'Description': second_layer[2],
'AttributesContainer': {},
'Category': second_layer[5]
}
attributes_by_Id['Attributes'].append(attributeObject)
if second_layer[3] is not None:
cur.execute(
f'SELECT taa.IdTableAttributes, taa.TableAttributesRepresentName, taa.IdTypeAttributes '
f'FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId '
f'LEFT JOIN table_attributes AS taa ON lpa.IdTableAttributes=taa.IdTableAttributes '
f'WHERE lpa.ListCategoryId={id_category} AND taa.IdTableAttributes={second_layer[3]}'
)
result_2 = [list(i) for i in cur.fetchall()]
getTypeAttributesById(attributes_by_Id, result_2,
id_category, j)
except Exception as ex:
abort(description=str(ex))
return attributes_by_Id
def addAttributesInfo():
try:
Attr_Name = str(request.json['Attr_Name'])
Description = str(request.json['Description']).replace('', 'None') \
if str(request.json['Description']) == '' else str(request.json['Description'])
Cont_Name = str(request.json['Cont_Name']).replace('', 'None') \
if str(request.json['Cont_Name']) == '' else str(request.json['Cont_Name'])
# Type must be only List, SVN, FileServer, OnlineSource
Cont_Type = str(request.json['Cont_Type']).replace('', 'None') \
if str(request.json['Cont_Type']) == '' else str(request.json['Cont_Type'])
Attr_Args = [Cont_Type, Cont_Name, Attr_Name, Description]
with con.cursor() as cur:
cur.callproc('addCommonAttribute', Attr_Args)
con.commit()
new_category = {
'Name': Attr_Name,
'Description': Description,
'AttributesContainer': {
'Name': Cont_Name,
'Type': Cont_Type
}
}
except Exception as ex:
print(ex)
return new_category
def displayCategoryById(Id):
with con.cursor() as cur:
cur.execute('SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC')
res_Id = [i[0] for i in cur.fetchall()]
if Id in filter(lambda t: t == Id, res_Id):
return jsonify(getNameById(Id)), 200
else:
return abort(404, description='Resource not found')
def getNameById(Id):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lc.IdListCategory, lc.RepresentName FROM list_category AS lc WHERE lc.IdListCategory={Id}')
result_1 = [list(i) for i in cur.fetchall()]
except Exception as ex:
abort(400, description=str(ex))
return getDescriptionById(result_1, Id)
def addFolder(id, object):
cur = con.cursor()
if id == '0':
request = "INSERT INTO folderscomponent SET FolderName = '{folderName}'"
request = request.format(folderName=object['Name'])
cur.execute(request)
else:
request = "INSERT INTO folderscomponent SET FolderName = '{folderName}', FolderID = {idParent}"
request = request.format(folderName=object['Name'], idParent=id)
cur.execute(request)
def displayCurrentPrivateAttribute(id_category, id_attribute):
with con.cursor() as cur:
cur.execute(
'SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC'
)
res_id_category = [i[0] for i in cur.fetchall()]
if id_category in filter(lambda t: t == id_category, res_id_category):
return jsonify(getDescriptionById(id_category, id_attribute)), 200
else:
return abort(404, description='Resource not found')
def checkCategoryId(id_category):
with con.cursor() as cur:
cur.execute(
'SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC'
)
res_id_category = [i[0] for i in cur.fetchall()]
if id_category in filter(lambda t: t == id_category, res_id_category):
return getCategoryName(id_category)
else:
return abort(404, description='Resource not found')
def getName(categories):
try:
with con.cursor() as cur:
cur.execute('SELECT lc.idListCategory, lc.RepresentName '
'FROM list_category AS lc ORDER BY lc.idListCategory ASC')
result_1 = [list(i) for i in cur.fetchall()]
getDescription(categories, result_1)
except Exception as ex:
print(ex)
return categories
def getLogsInfo(session_logs):
try:
with con.cursor() as cur:
cur.execute(
'SELECT ls.DateTime, u.Login FROM logs_session AS ls LEFT JOIN users AS u ON '
'ls.idUser=u.idUser ORDER BY ls.idLogSession ASC ')
result_1 = [list(i) for i in cur.fetchall()]
collectList(result_1, session_logs)
except Exception as ex:
print(ex)
return session_logs
def checkSpecificCommonAttributeId(id_attribute):
with con.cursor() as cur:
cur.execute(
'SELECT lca.idListCommonAttribute FROM list_common_attribute AS lca '
'ORDER BY lca.idListCommonAttribute ASC')
res_id_attribute = [i[0] for i in cur.fetchall()]
if id_attribute in filter(lambda t: t == id_attribute,
res_id_attribute):
return getCommonAttributeInfo(id_attribute)
else:
return abort(404, description='Resource not found')
def getCommonAttributeInfo(id_attribute):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lca.idListCommonAttribute, lca.RepresentName, lca.Description, lca.idTableAttributes '
f'FROM list_common_attribute AS lca '
f'WHERE lca.idListCommonAttribute={id_attribute} ')
result_1 = [list(i) for i in cur.fetchall()]
except Exception as ex:
print(ex)
return getTableAttributeRepresentName(result_1, id_attribute)
def getAttributesContainersInfo(containers_of_attributes):
try:
with con.cursor() as cur:
cur.execute('SELECT taa.idTableAttributes, taa.TableAttributesRepresentName, '
'tya.TypeAttributes FROM table_attributes AS taa LEFT JOIN type_attributes AS tya ON '
'taa.idTypeAttributes=tya.idTypeAttributes ORDER BY taa.idTableAttributes ASC ')
result_1 = [list(i) for i in cur.fetchall()]
collectList(result_1, containers_of_attributes)
except Exception as ex:
print(ex)
return containers_of_attributes
def displayContainerOfAttributesById(id_container):
with con.cursor() as cur:
cur.execute(
'SELECT taa.idTableAttributes FROM table_attributes AS taa ORDER BY taa.idTableAttributes ASC'
)
res_id_container = [i[0] for i in cur.fetchall()]
if id_container in filter(lambda t: t == id_container,
res_id_container):
return jsonify(getAttributeContainersInfoById(id_container)), 200
else:
return abort(404, description='Resource not found')
def getCategoryName(id_category):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lc.IdListCategory, lc.RepresentName '
f'FROM list_category AS lc WHERE lc.IdListCategory={id_category}'
)
Cater_Name = cur.fetchone()[1]
except Exception as ex:
print(ex)
return Cater_Name
def getAttributesInfo(common_attributes):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lca.idListCommonAttribute, lca.RepresentName, lca.Description, lca.idTableAttributes '
f'FROM list_common_attribute AS lca '
f'ORDER BY lca.idListCommonAttribute ASC ')
result_1 = [list(i) for i in cur.fetchall()]
getTableAttributesRepresentName(result_1, common_attributes)
except Exception as ex:
print(ex)
return common_attributes
def getAttributeContainersInfoById(id_container):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT taa.idTableAttributes, taa.TableAttributesRepresentName, '
f'tya.TypeAttributes FROM table_attributes AS taa LEFT JOIN type_attributes AS tya ON '
f'taa.idTypeAttributes=tya.idTypeAttributes WHERE taa.idTableAttributes={id_container}'
)
result_1 = [list(i) for i in cur.fetchall()]
except Exception as ex:
print(ex)
return collectDict(result_1)
def addCategory():
try:
Cater_Name = str(request.json['Categ_Name'])
Cater_Args = [Cater_Name]
with con.cursor() as cur:
cur.callproc('createCategory', Cater_Args)
con.commit()
new_category = {'Name': Cater_Name, 'Attributes': []}
if 'Attr_Name' in request.json:
addAttributes(new_category, Cater_Name)
except Exception as ex:
print(ex)
return new_category
def getDescriptionById(id_category):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lpa.IdListPrivateAttribute, lpa.RepresentName, lpa.Description, lpa.IdTableAttributes, '
f'lpa.ListCategoryId, lc.RepresentName '
f'FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId '
f'WHERE lpa.ListCategoryId={id_category}')
result_1 = [list(i) for i in cur.fetchall()]
except Exception as ex:
abort(description=str(ex))
return getTableAttributesRepresentNameById(result_1, id_category)
def getDescriptionById(result_1, Id):
try:
categories_by_Id = {'Id': result_1[0][0], 'Name': result_1[0][1], 'Attributes': []}
with con.cursor() as cur:
cur.execute(
f'SELECT lpa.IdListPrivateAttribute, lpa.RepresentName, lpa.Description, lpa.IdTableAttributes, lpa.ListCategoryId '
f'FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId '
f'WHERE lpa.ListCategoryId={Id}')
result_2 = [list(i) for i in cur.fetchall()]
getTableAttributesRepresentNameById(categories_by_Id, result_2, Id)
except Exception as ex:
abort(description=str(ex))
return categories_by_Id
def getTypeAttributes(categories, result_3, i, j, first_layer):
try:
with con.cursor() as cur:
for third_layer in result_3:
cur.execute(f'SELECT tya.TypeAttributes, tya.idTypeAttributes FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.idListCategory=lpa.ListCategoryID '
f'LEFT JOIN table_attributes AS taa ON lpa.idTableAttributes=taa.idTableAttributes '
f'LEFT JOIN type_attributes AS tya ON taa.idTypeAttributes=tya.idTypeAttributes '
f'WHERE lpa.ListCategoryID={first_layer} AND tya.idTypeAttributes={third_layer[2]}')
result_4 = [list(i) for i in cur.fetchall()]
sendTypeAttributes(categories, result_4, i, j, third_layer)
except Exception as ex:
print(ex)
return categories
def getLogsInfo(db_logs):
try:
with con.cursor() as cur:
cur.execute(
'SELECT l.DateTime, tyq.TypeQuery, l.Element, lc.RepresentName, l.Description, u.Login '
'FROM logs AS l LEFT JOIN type_query AS tyq ON l.idTypeQuery=tyq.idTypeQuery '
'LEFT JOIN list_category AS lc ON l.idListCategory=lc.idListCategory '
'LEFT JOIN users AS u ON l.idUser=u.idUser ORDER BY l.idLog ASC '
)
result_1 = [list(i) for i in cur.fetchall()]
collectList(result_1, db_logs)
except Exception as ex:
print(ex)
return db_logs
def getData(id_category, id_attribute):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lpa.RepresentName, tya.TypeAttributes, lc.RepresentName FROM list_category AS lc LEFT '
f'JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId LEFT JOIN '
f'table_attributes AS taa ON lpa.IdTableAttributes=taa.IdTableAttributes LEFT JOIN '
f'type_attributes AS tya ON taa.IdTypeAttributes=tya.IdTypeAttributes WHERE '
f'lc.idListCategory={id_category} AND lpa.idListPrivateAttribute={id_attribute}'
)
data_list = [j for i in cur.fetchall() for j in i]
except Exception as ex:
print(ex)
return data_list
def getTypeAttributesById(categories_by_Id, result_3, Id, j):
try:
with con.cursor() as cur:
for third_layer in result_3:
cur.execute(f'SELECT tya.TypeAttributes, tya.IdTypeAttributes FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId '
f'LEFT JOIN table_attributes AS taa ON lpa.IdTableAttributes=taa.IdTableAttributes '
f'LEFT JOIN type_attributes AS tya ON taa.IdTypeAttributes=tya.IdTypeAttributes '
f'WHERE lpa.ListCategoryId={Id} AND tya.IdTypeAttributes={third_layer[2]}')
result_4 = [list(i) for i in cur.fetchall()]
sendTypeAttributes(categories_by_Id, result_4, j, third_layer)
except Exception as ex:
abort(description=str(ex))
return categories_by_Id
def getTypeAttribute(common_attributes, result_2, id_attribute):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT tya.TypeAttributes FROM list_common_attribute AS lca '
f'LEFT JOIN table_attributes AS taa ON lca.idTableAttributes=taa.idTableAttributes '
f'LEFT JOIN type_attributes AS tya ON taa.idTypeAttributes=tya.idTypeAttributes '
f'WHERE tya.idTypeAttributes={result_2[0][2]} AND lca.idListCommonAttribute={id_attribute}'
)
result_3 = [list(i) for i in cur.fetchone()]
sendTypeAttribute(common_attributes, result_3, result_2)
except Exception as ex:
print(ex)
return common_attributes
def getTypeAttributes(common_attributes, result_2, i):
try:
with con.cursor() as cur:
for second_layer in result_2:
cur.execute(
f'SELECT tya.TypeAttributes FROM list_common_attribute AS lca '
f'LEFT JOIN table_attributes AS taa ON lca.idTableAttributes=taa.idTableAttributes '
f'LEFT JOIN type_attributes AS tya ON taa.idTypeAttributes=tya.idTypeAttributes '
f'WHERE tya.idTypeAttributes={second_layer[2]}')
result_3 = [list(i) for i in cur.fetchall()]
sendTypeAttributes(common_attributes, result_3, second_layer,
i)
except Exception as ex:
print(ex)
return common_attributes
def getDescription(categories, result_1):
try:
with con.cursor() as cur:
for i, first_layer in enumerate(result_1, 0):
categories.append({'Id': first_layer[0], 'Name': first_layer[1], 'Attributes': []})
cur.execute(
f'SELECT lpa.idListPrivateAttribute, lpa.RepresentName, lpa.Description, lpa.idTableAttributes, lpa.ListCategoryID '
f'FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.idListCategory=lpa.ListCategoryID '
f'WHERE lpa.ListCategoryID={first_layer[0]}')
result_2 = [list(i) for i in cur.fetchall()]
getTableAttributesRepresentName(categories, result_2, i, first_layer[0])
except Exception as ex:
print(ex)
return categories
def checkCategoryId(id_category, id_attribute):
try:
with con.cursor() as cur:
cur.execute(
'SELECT lc.IdListCategory FROM list_category AS lc ORDER BY lc.IdListCategory ASC'
)
res_id_category = [i[0] for i in cur.fetchall()]
if id_category in filter(lambda t: t == id_category,
res_id_category):
return checkAttributeId(id_category, id_attribute)
else:
return abort(404,
description='Requested category do not found')
except Exception as ex:
print(ex)
def getTableAttributesRepresentName(categories, result_2, i, first_layer):
try:
with con.cursor() as cur:
for j, second_layer in enumerate(result_2, 0):
attributeObject = {'Id': second_layer[0], 'Name': second_layer[1], 'Description': second_layer[2], 'AttributesContainer': {}}
categories[i]['Attributes'].append(attributeObject)
if second_layer[3] is not None:
cur.execute(f'SELECT taa.idTableAttributes, taa.TableAttributesRepresentName, taa.idTypeAttributes '
f'FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.idListCategory=lpa.ListCategoryID '
f'LEFT JOIN table_attributes AS taa ON lpa.idTableAttributes=taa.idTableAttributes '
f'WHERE lpa.ListCategoryID={first_layer} AND taa.idTableAttributes={second_layer[3]}')
result_3 = [list(i) for i in cur.fetchall()]
getTypeAttributes(categories, result_3, i, j, first_layer)
except Exception as ex:
print(ex)
return categories
def checkAttributeId(id_category, id_attribute):
try:
with con.cursor() as cur:
cur.execute(
f'SELECT lpa.IdListPrivateAttribute, lpa.ListCategoryId '
f'FROM list_category AS lc '
f'LEFT JOIN list_private_attribute AS lpa ON lc.IdListCategory=lpa.ListCategoryId '
f'WHERE lpa.ListCategoryId={id_category} AND lpa.idListPrivateAttribute={id_attribute}'
)
res_id_attribute = [i[0] for i in cur.fetchall()]
if id_attribute in filter(lambda t: t == id_attribute,
res_id_attribute):
return getData(id_category, id_attribute)
else:
return abort(404,
description='Requested attribute do not found')
except Exception as ex:
print(ex)
