def updateGroupSize(itemSize, idgroup):
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "SELECT totalSize FROM groups " \
"WHERE idgroup = %s"
cursor.execute(sql, (idgroup))
result = cursor.fetchall()
totalSizeCurrent = float(result[0]['totalSize'])
totalSizeNew = totalSizeCurrent - float(itemSize)
if totalSizeNew < 0:
totalSizeNew = 0
totalSizeNew = str(totalSizeNew)
totalSizeNew = totalSizeNew[0:4]
sql = "UPDATE groups " \
"SET totalSize = %s " \
"WHERE idgroup = %s"
cursor.execute(sql, (totalSizeNew, idgroup))
connection.commit()
finally:
connection.close()
def deleteItem(deleteItemId):
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "DELETE FROM group_items " \
"WHERE idgroup_items = %s"
cursor.execute(sql, (deleteItemId))
connection.commit()
finally:
connection.close()
def deleteGroup():
idgroup = request.form['deleteGroupID']
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "DELETE FROM groups WHERE idgroup=%s"
cursor.execute(sql, (idgroup))
connection.commit()
finally:
connection.close()
return redirect('/admin/groups')
def addUser(params):
hashedPw = bcrypt.generate_password_hash(params['password'])
connection = establishConnection()
try:
with connection.cursor() as cursor:
sql = "INSERT INTO Users (name, userID, password) VALUES (%s, %s, %s)"
cursor.execute(sql,
(params['userName'], params['user_id'], hashedPw))
connection.commit()
finally:
connection.close()
def joinGroup():
idgroup = request.form['groupID']
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "INSERT INTO group_members (idgroup, member)VALUES (%s, %s)"
cursor.execute(sql, (idgroup, session['userID']))
connection.commit()
finally:
connection.close()
return redirect('/groups')
def groups():
if 'isAdmin' in session:
return "Bad Page"
isActive = 1
if 'isActive' in session:
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "SELECT groups.name, groups.owner, groups.idgroup " \
"FROM group_members " \
"INNER JOIN groups ON group_members.idgroup = groups.idgroup " \
"WHERE group_members.member = %s AND " \
"group_members.accepted = %s AND " \
"groups.isActive = %s"
cursor.execute(sql, (session['userID'], 1, 1))
joined = cursor.fetchall()
sql = "SELECT groups.idgroup, groups.name, groups.owner FROM groups " \
"WHERE groups.isActive = %s AND " \
"groups.idgroup NOT IN " \
"(" \
"SELECT group_members.idgroup FROM group_members " \
"WHERE group_members.member = %s" \
")"
cursor.execute(sql, (1, session['userID']))
unjoined = cursor.fetchall()
sql = "SELECT groups.name, groups.idgroup, group_members.member FROM groups " \
"LEFT JOIN group_members ON " \
"groups.idgroup = group_members.idgroup " \
"WHERE (group_members.member = %s AND " \
"group_members.accepted = %s) AND" \
" groups.isActive = %s"
cursor.execute(sql, (session['userID'], 0, 1))
requested = cursor.fetchall()
finally:
connection.close()
return render_template('/nonAdmin/groups.html',
joinedGroups=joined,
unjoinedGroups=unjoined,
requestedGroups=requested)
else:
return redirect('login')
def createGroup():
name = request.form['name']
owner = session['userID']
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "INSERT INTO `groups` (`owner`, `name`) VALUES (%s, %s)"
cursor.execute(sql, (owner, name))
connection.commit()
finally:
connection.close()
return redirect('/groups')
def fetchDeleteItemSize(deleteItemId):
result = 0
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "SELECT size FROM group_items " \
"WHERE idgroup_items = %s"
cursor.execute(sql, (deleteItemId))
result = cursor.fetchall()
result = result[0]['size']
finally:
connection.close()
return result
def checkIdAvailable(userId):
connection = establishConnection()
try:
with connection.cursor() as cursor:
sql = "SELECT COUNT(*) FROM Users " \
"WHERE userID = %s"
cursor.execute(sql, (userId))
result = cursor.fetchall()
count = result[0]['COUNT(*)']
if count == 1:
return 1
finally:
connection.close()
return 0
def acceptGroupJoin():
idRequest = request.form['requestID']
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "UPDATE group_members SET accepted = '%s' " \
"WHERE id = %s"
cursor.execute(sql, (1, idRequest))
connection.commit()
finally:
connection.close()
return redirect('/admin/groups')
def downloadFile():
idgroup_items = request.form['idgroup_items']
now = datetime.datetime.now()
time = now.time()
date = now.date()
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "UPDATE group_items SET " \
"date_access= %s, time_access = %s " \
"WHERE idgroup_items = %s"
cursor.execute(sql, (date, time, idgroup_items))
connection.commit()
finally:
connection.close()
return "success"
def selectedGroup():
if request.method == 'POST':
idgroup = request.form['idgroup']
print(idgroup)
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "SELECT groups.*, group_items.* FROM groups " \
"LEFT JOIN group_items " \
"ON groups.idgroup = group_items.idgroup " \
"WHERE groups.idgroup = %s"
cursor.execute(sql, (idgroup))
result = cursor.fetchall()
finally:
connection.close()
return render_template('common/selected_group.html', groupData=result)
def activateGroup():
idgroup = request.form['activateGroupID']
groupOwner = request.form['groupOwner']
print("idgroup: " + idgroup)
print("groupOwner: " + groupOwner)
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "UPDATE `groups` SET isActive = '%s' WHERE idgroup=%s"
cursor.execute(sql, (1, idgroup))
connection.commit()
sql = "INSERT INTO group_members (idgroup, member, accepted) VALUES (%s,%s,%s)"
cursor.execute(sql,(idgroup,groupOwner,1))
connection.commit()
finally:
connection.close()
return redirect('/admin/groups')
def dbQueries(params):
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "SELECT totalSize FROM groups " \
"WHERE idGroup = %s"
cursor.execute(sql, (params['idgroup']))
result = cursor.fetchall()
groupTotalSize = float(result[0]['totalSize'])
params['fileSize'] = getFileSize(params['fileName'])
newGroupTotalSize = groupTotalSize + params['fileSize']
if newGroupTotalSize > 100:
print(
"file cannot be uploaded, group already using allocated memory"
)
return "failed"
else:
filePath = '/tmp/' + params['fileName']
upload_blob(bucket_name, filePath, params['fileName'])
params['full_file_url'] = bucket_base + params['fileName']
sql = "UPDATE groups SET totalSize = %s " \
"WHERE idgroup = %s"
cursor.execute(sql, (newGroupTotalSize, params['idgroup']))
sql = "INSERT INTO group_items " \
"(idgroup, file_url, uploader_id, name, description, " \
"date, time, date_access, time_access, size) " \
"VALUES " \
"(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(
sql,
(params['idgroup'], params['full_file_url'],
session['userID'], params['name'], params['description'],
params['date'], params['time'], params['date'],
params['time'], params['fileSize']))
connection.commit()
finally:
connection.close()
def login():
params = {}
if request.method == 'POST':
params['user_id'] = request.form['user_id']
params['password'] = request.form['password']
if secureLogin(params):
try:
connection = establishConnection()
with connection.cursor() as cursor:
sql = "SELECT * FROM `Users` WHERE userID=%s"
cursor.execute(sql, (params['user_id']))
result = cursor.fetchall()
if (len(result) == 0):
flash("userid does not exist")
return render_template('common/login.html')
else:
hasPw = result[0]['password']
if bcrypt.check_password_hash(hasPw,
params['password']):
if result[0]['isActive'] == 1:
session['userID'] = params['user_id']
session['isActive'] = 'true'
session['name'] = result[0]['name']
if result[0]['isAdmin'] == 1:
session['isAdmin'] = 'true'
return redirect('/admin/users')
else:
return redirect('groups')
else:
flash("You are not yet activated by Admin")
return render_template('common/login.html')
else:
flash("wrong credentials")
return render_template('common/login.html')
finally:
connection.close()
return render_template('common/login.html', title='login')
def adminGroups():
if 'isAdmin' in session:
try:
connection = establishConnection()
with connection.cursor() as cursor:
isAdmin = 0;
sql = "SELECT * FROM `groups`"
cursor.execute(sql)
result = cursor.fetchall()
sql = "SELECT groups.idgroup, groups.name, group_members.member, group_members.id " \
"FROM group_members " \
"INNER JOIN groups ON " \
"group_members.idgroup = groups.idgroup " \
"WHERE group_members.accepted = %s"
cursor.execute(sql, (0))
acceptRequests = cursor.fetchall()
print(acceptRequests)
finally:
connection.close()
return render_template('admin/groups.html', groups = result, acceptRequests = acceptRequests);
else:
return "NO ACCESS SORRY"
