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"