def getAllMarksFromCourse(idcourses):
cursor = mydb.cursor()
query_string = "SELECT idusers,marks FROM " + idcourses
cursor.execute(query_string)
idusersmarks = cursor.fetchall()
print('All marks for ', idcourses, ' are ', idusersmarks)
return idusersmarks
def removeUserFromCourse(idcourses, idusers):
cursor = mydb.cursor()
print(idcourses, ' -- ', idusers)
for iduser in idusers:
query_string = "DELETE FROM userscourses WHERE idusers = %s AND idcourses = %s"
# "INSERT INTO userscourses(idusers, idcourses) VALUES (%s ,%s)"
# try:
cursor.execute(query_string, (str(iduser), str(idcourses)))
# accept the change
# cursor.commit()
# except Error as error:
# print(error)
# finally:
# cursor.close()
# conn.close()
# cursor.execute(query_string, (str(iduser),str(idcourses)))
mydb.commit()
print('Removed TA Success!')
# query_string = "INSERT INTO "+idcourses+"(idusers) VALUES (%s)"
# cursor.execute(query_string, (str(iduser),))
mydb.commit()
cursor.execute('SELECT * FROM userscourses')
records = cursor.fetchall()
for record in records:
print(record)
def getPermissions(idroles, resourcename):
idresources = getResourceId(resourcename)
cursor = mydb.cursor()
# print('idres ',idresources,' idrole',idroles)
query_string = "SELECT idroleresource FROM roleresource WHERE idroles = %s AND idresources = %s"
cursor.execute(query_string, (
idroles,
idresources,
))
idroleresources = cursor.fetchall()
if (len(idroleresources) > 0):
print(idroleresources[0][0])
query_string = "SELECT idoperations, constraints FROM permissions WHERE idroleresource = %s"
cursor.execute(query_string, (idroleresources[0][0], ))
data = cursor.fetchall()
else:
data = []
operationsconstraints = []
if len(data) == 0:
operationsconstraints.append(['none', 'none'])
for i in data:
constraints = i[1]
query_string = "SELECT operationname FROM operations WHERE idoperations = %s"
cursor.execute(query_string, (i[0], ))
operationname = cursor.fetchall()
operationsconstraints.append([operationname[0][0], constraints])
return operationsconstraints
def addUserInCourse(idcourses, idusers):
cursor = mydb.cursor()
for iduser in idusers:
query_string = "INSERT INTO userscourses(idusers, idcourses) VALUES (%s ,%s)"
cursor.execute(query_string, (str(iduser), str(idcourses)))
mydb.commit()
print('Adding TA Success!')
def getRoleIdByName(rolename):
cursor = mydb.cursor()
query_string = "SELECT idroles FROM roles WHERE rolename = %s"
cursor.execute(query_string, (rolename, ))
data = cursor.fetchall()
idroles = data[0]
return idroles[0]
def getUserCourseIDs(idusers):
cursor = mydb.cursor()
# print('idres ',idresources,' idrole',idroles)
query_string = "SELECT idcourses FROM userscourses WHERE idusers = %s"
cursor.execute(query_string, (idusers, ))
idcourses = cursor.fetchall()
print('ID coursesss ', idcourses)
return idcourses
def getAllExistingCourses():
# print(idcourses)
cursor = mydb.cursor()
query_string = "SELECT * FROM courses"
cursor.execute(query_string)
courses = cursor.fetchall()
print('Courses Existing are ', courses)
return courses
def getAllUnassignedUsers():
cursor = mydb.cursor()
idroles = getRoleIdByName('none')
query_string = "SELECT * FROM users WHERE idroles = %s"
cursor.execute(query_string, (idroles, ))
users = cursor.fetchall()
print('Courses Existing are ', users)
return users
def addStudentInCourse(idcourses, idusers):
cursor = mydb.cursor()
for iduser in idusers:
query_string = "INSERT INTO userscourses(idusers, idcourses) VALUES (%s ,%s)"
cursor.execute(query_string, (str(iduser), str(idcourses)))
query_string = "INSERT INTO " + idcourses + "(idusers,marks) VALUES (%s,-1)"
cursor.execute(query_string, (str(iduser), ))
mydb.commit()
print('Adding Student Success!')
def getSalt(emailid):
cursor = mydb.cursor()
query_string = "SELECT salt FROM users WHERE emailid = %s"
cursor.execute(query_string, (emailid, ))
data = cursor.fetchall()
print(data)
if len(data) == 0:
return 'fake'
return data[0][0]
def getNotAssignedUsersNoCourses(rolename):
idroles = getRoleIdByName(rolename)
cursor = mydb.cursor()
query_string1 = "SELECT a.idusers FROM users as a WHERE idroles = " + str(
idroles)
cursor.execute(query_string1)
data = cursor.fetchall()
return data
def getUserCoursesMarks(idusers, idcourses):
# idcourses = getUserCourseIDs(idusers = idusers)
cursor = mydb.cursor()
query_string = "SELECT marks FROM " + '`' + idcourses + '`' + " WHERE idusers = %s"
cursor.execute(query_string, (idusers, ))
coursemarks = cursor.fetchall()
# print(float(coursemarks[0][0]))
return float(coursemarks[0][0])
def deleteStudentMarks(idcourses, idusers):
cursor = mydb.cursor()
for i in range(0, len(idusers)):
query_string = "UPDATE " + idcourses + ' SET marks = %s WHERE idusers = %s'
cursor.execute(query_string, (
'-1',
str(idusers[i]),
))
mydb.commit()
print('Marks Successfully Updated!')
def getStudentInCourse(idcourses):
cursor = mydb.cursor()
# coursesmarks = []
# print('idres ',idresources,' idrole',idroles)
# print(idcourses)
# coursetable = str('`'+idcourses+'`')
# print(coursetable)
query_string = "SELECT idusers FROM " + '`' + idcourses + '`'
cursor.execute(query_string, ())
students = cursor.fetchall()
return students
def getUserCoursesIdName(idusers):
idcourses = getUserCourseIDs(idusers=idusers)
cursor = mydb.cursor()
print('ID courses ', idcourses)
coursesidname = []
# print('idres ',idresources,' idrole',idroles)
for i in idcourses:
query_string = "SELECT coursesname FROM courses WHERE idcourses = %s"
cursor.execute(query_string, (i[0], ))
data = cursor.fetchall()
coursesidname.append([i[0], data[0][0]])
return coursesidname
def getNotAssignedUsers(idcourses, rolename):
print(idcourses)
idroles = getRoleIdByName(rolename)
cursor = mydb.cursor()
query_string1 = "SELECT a.idusers FROM users as a WHERE idroles = " + str(
idroles)
query_string2 = "SELECT b.idusers FROM userscourses as b WHERE idcourses = '" + str(
idcourses) + "'"
query_string = query_string1 + " AND a.idusers NOT IN (" + query_string2 + ")"
cursor.execute(query_string)
data = cursor.fetchall()
return data
def getTAInCourse(idcourses, rolename):
# print(idcourses)
idroles = getRoleIdByName(rolename)
cursor = mydb.cursor()
query_string1 = "SELECT a.idusers FROM users as a WHERE idroles = " + str(
idroles)
query_string2 = "SELECT b.idusers FROM userscourses as b WHERE idcourses = '" + str(
idcourses) + "'"
query_string = query_string1 + " AND a.idusers IN (" + query_string2 + ")"
cursor.execute(query_string)
ta = cursor.fetchall()
print('Ta in ', idcourses, ' are ', ta)
return ta
def insertEditedMarksInCourse(idcourses, idusers, marks):
cursor = mydb.cursor()
for i in range(0, len(idusers)):
try:
marks[i] = float(marks[i])
query_string = "UPDATE " + idcourses + ' SET marks = %s WHERE idusers = %s'
cursor.execute(query_string, (
str(marks[i]),
str(idusers[i]),
))
mydb.commit()
except ValueError:
print('Non Int Input, not updated!')
print('Marks Successfully Updated!')
def getAllMarksOneCourse(idcourses):
# idcourses = getUserCourseIDs(idusers = idusers)
cursor = mydb.cursor()
# coursesmarks = []
# print('idres ',idresources,' idrole',idroles)
# print(idcourses)
# coursetable = str('`'+idcourses+'`')
# print(coursetable)
query_string = "SELECT * FROM " + '`' + idcourses + '`'
cursor.execute(query_string, ())
coursemarks = cursor.fetchall()
# print(float(coursemarks[0][0]))
return coursemarks
def login2():
form = LoginForm()
if form.validate_on_submit():
return render_template('student.html', title="Student")
flash('Login requested for user {}, remember_me={}'.format(form.username.data, form.remember_me.data))
# print('here')
mycursor = mydb.cursor()
# mycursor.execute("SHOW TABLES")
mycursor.execute("SELECT * FROM user")
data = []
for x in mycursor:
data.append(x)
return render_template('student.html', title="Student", data=data)
else:
return render_template('index.html', title='Sign-Up/Login Form', form=form)
def checkUserCourse(idusers, idcourses):
cursor = mydb.cursor()
# coursesmarks = []
# print('idres ',idresources,' idrole',idroles)
# print(idcourses)
# coursetable = str('`'+idcourses+'`')
# print(coursetable)
query_string = "SELECT * FROM userscourses WHERE idusers = %s and idcourses = %s"
cursor.execute(query_string, (
idusers,
idcourses,
))
data = cursor.fetchall()
print('Check User Course: ', data)
if (len(data) == 0):
return False
return True
def createNewUser(name, emailid, password, salt):
res = 'Email-id already exists!'
cursor = mydb.cursor()
query_string = "SELECT * FROM users WHERE emailid = %s"
cursor.execute(query_string, (emailid, ))
emchk = cursor.fetchall()
if (len(emchk) == 0):
query_string = "INSERT INTO users(name,emailid,password,salt) VALUES (%s ,%s, %s, %s)"
try:
cursor.execute(query_string,
(str(name), str(emailid), str(password), str(salt)))
res = 'Adding user Success!'
mydb.commit()
except:
res = 'Some Database Error'
print(res)
return res
def accountRoleApproval(notnonedata):
cursor = mydb.cursor()
for i in range(0, len(notnonedata)):
idroles = getRoleIdByName(notnonedata[i][1])
try:
query_string = "UPDATE users SET idroles = %s WHERE idusers = %s"
cursor.execute(query_string, (
str(idroles),
str(notnonedata[i][0]),
))
mydb.commit()
except ValueError:
print('Database error, not updated!')
return 'Some Database Error'
res = 'Roles Assigned Success!'
print(res)
return res
def addNewCourse(idcourses, coursesname):
courses = getAllExistingCourses()
flag = True
for i in courses:
if (i[0] == idcourses):
flag = False
res = 'Course ID already existing!'
if (flag):
cursor = mydb.cursor()
query_string = "INSERT INTO courses(idcourses,coursesname) VALUES (%s ,%s)"
try:
cursor.execute(query_string, (str(idcourses), str(coursesname)))
res = 'Adding Courses Success!'
mydb.commit()
except:
res = 'Some Database Error'
print(res)
return res
return res
def getUnassignedCourses():
# idcourses = getUserCourseIDs(idusers = idusers)
cursor = mydb.cursor()
idfaculty = getRoleIdByName("faculty")
query_string1 = "SELECT a.idcourses FROM courses as a " #NOT IN
query_string3 = "SELECT idusers FROM users WHERE idroles = '" + str(
idfaculty) + "'"
query_string2 = "SELECT b.idcourses FROM userscourses as b WHERE idusers IN (" + query_string3 + ")"
cursor.execute(query_string1)
unassignedcourseids = cursor.fetchall()
print('All Faculty idusers: ', unassignedcourseids)
query_string = query_string1 + " WHERE a.idcourses NOT IN (" + query_string2 + ")"
cursor.execute(query_string)
unassignedcourseids = cursor.fetchall()
# print('ID courses ',idcourses)
coursesidname = []
# print('idres ',idresources,' idrole',idroles)
for i in unassignedcourseids:
query_string = "SELECT coursesname FROM courses WHERE idcourses = %s"
cursor.execute(query_string, (i[0], ))
data = cursor.fetchall()
coursesidname.append([i[0], data[0][0]])
return coursesidname
def login():
print('login')
form2 = SignupForm()
form = LoginForm()
error = ''
if request.method == 'POST':
print('form1: ',form.validate())
if form.submit.data and form.validate_on_submit():
# print('here3')
error = None
cursor = mydb.cursor()
emailid = form.username.data
password = form.password.data
# password = password.encode('utf-8')
import hashlib, uuid
# salt = uuid.uuid4().hex
# print('salt: ',salt)
salt = getSalt(emailid)
# print(salt)
# salt = salt.encode('utf-8')
hashed_password = hashlib.sha256((salt+password).encode('utf-8')).hexdigest()
# print(salt)
print(emailid, hashed_password)
query_string = "SELECT idusers, name, emailid, idroles FROM users WHERE emailid = %s AND password = %s"
cursor.execute(query_string, (emailid, hashed_password,))
data = cursor.fetchall()
print('data len: ', len(data))
if len(data) == 1:
userdata = {}
idusers = userdata['idusers'] = data[0][0]
userdata['name'] = data[0][1]
userdata['emailid'] = data[0][2]
idroles = data[0][3]
session['userdata'] = userdata
session['idusers'] = idusers
# DONE not assigned users role manage
role = getRole(idroles)
role = role[0]
session['role'] = role
# specify resource name
# specify resource name
resourcename = 'courses'
resourcename1 = 'faculty'
resourcename2 = 'ta'
resourcename3 = 'student'
if (role == 'none'):
permissionon = {}
permissionon['idresources'] = 0
permissionon['resourcename'] = resourcename
permissionon['permissions'] = 'none'
session['permissionon' + resourcename] = permissionon
error = 'You are not an assigned user :). Contact Admin!'
return render_template('index.html', title="Login Page!", form=form, form2=form2, error=error)
operationsconstraints = getPermissions(idroles=idroles, resourcename=resourcename)
operationsconstraints1 = getPermissions(idroles=idroles, resourcename=resourcename1)
operationsconstraints2 = getPermissions(idroles=idroles, resourcename=resourcename2)
operationsconstraints3 = getPermissions(idroles=idroles, resourcename=resourcename3)
allpermissionon = {}
permissionon = {}
permissionon['idresources'] = getResourceId(resourcename)
permissionon['permissions'] = operationsconstraints
allpermissionon[resourcename] = permissionon
permissionon = {}
permissionon['idresources'] = getResourceId(resourcename1)
permissionon['permissions'] = operationsconstraints1
allpermissionon[resourcename1] = permissionon
permissionon = {}
permissionon['idresources'] = getResourceId(resourcename2)
permissionon['permissions'] = operationsconstraints2
allpermissionon[resourcename2] = permissionon
permissionon = {}
permissionon['idresources'] = getResourceId(resourcename3)
permissionon['permissions'] = operationsconstraints3
allpermissionon[resourcename3] = permissionon
# permissionon = [getResourceId(resourcename), resourcename, operationsconstraints]
session['allpermissionon'] = allpermissionon
print(allpermissionon)
# return redirect(url_for('onepage'))
return render_template('onepage.html', title="One Page")
else:
error = 'Invalid Credentials! Try Again!'
# return render_template('index.html', form2=form2, error=error, form=form)
else:
print('here3')
print(form.password.data)
flash('All fields are required.')
error = 'Invalid Credentials! Try Again!'
return render_template('index.html', error=error, form=form, form2=form2 )
def loginseperate():
form = LoginForm()
# print('here'+request.method)
if request.method == 'POST':
print(form.validate())
if form.validate_on_submit():
print('here3')
cursor = mydb.cursor()
emailid = form.username.data
password = form.password.data
password = password.encode('utf-8')
import hashlib, uuid
salt = uuid.uuid4().hex
salt = salt.encode('utf-8')
hashed_password = hashlib.sha512(password + salt).hexdigest()
print(emailid, hashed_password)
query_string = "SELECT idusers, name, emailid, idroles FROM users WHERE emailid = %s AND password = %s"
cursor.execute(query_string, (emailid, hashed_password,))
data = cursor.fetchall()
print('data len: ', len(data))
if len(data) == 1:
idusers = data[0][0]
name = data[0][1]
emailid = data[0][2]
idroles = data[0][3]
session['idusers'] = idusers
# TODO not assigned users role manage
role = getRole(idroles)
role = role[0]
session['role'] = role
# specify resource name
resourcename = 'courses'
resourcename1 = 'faculty'
resourcename2 = 'ta'
resourcename3 = 'student'
operationsconstraints = getPermissions(idroles=idroles, resourcename=resourcename)
operationsconstraints1 = getPermissions(idroles=idroles, resourcename=resourcename1)
operationsconstraints2 = getPermissions(idroles=idroles, resourcename=resourcename2)
operationsconstraints3 = getPermissions(idroles=idroles, resourcename=resourcename3)
allpermissionon = {}
permissionon = {}
permissionon['resourcename'] = resourcename
permissionon['permissions'] = operationsconstraints
allpermissionon[getResourceId(resourcename)] = [permissionon]
permissionon = {}
permissionon['resourcename'] = resourcename1
permissionon['permissions'] = operationsconstraints1
allpermissionon[getResourceId(resourcename1)] = [permissionon]
permissionon = {}
permissionon['resourcename'] = resourcename2
permissionon['permissions'] = operationsconstraints2
allpermissionon[getResourceId(resourcename2)] = [permissionon]
permissionon = {}
permissionon['resourcename'] = resourcename3
permissionon['permissions'] = operationsconstraints3
allpermissionon[getResourceId(resourcename3)] = [permissionon]
# permissionon = [getResourceId(resourcename), resourcename, operationsconstraints]
session['allpermissionon'] = allpermissionon
print(allpermissionon)
if (role == 'student'):
query_string = "SELECT idusers, name, emailid, idroles FROM users WHERE emailid = %s AND password = %s"
cursor.execute(query_string, (emailid, hashed_password,))
data = cursor.fetchall()
courses = getUserCoursesIdName(idusers=idusers)
print(courses[0])
for i in courses:
data.append([i[0], i[1], getUserCoursesMarks(idusers, i[0])])
return render_template('student.html', title="Student", data=data, form=form)
elif role == 'ta':
return render_template('student.html', title="Student", data=data, form=form)
# cursor.execute("SELECT * FROM users")
#
# data = []
# for x in cursor:
# data.append(x)
else:
print('here2')
print(form.password.data)
flash('All fields are required.')
return render_template('index.html', form=form)
return render_template('index.html', form=form)
def getRole(idroles):
cursor = mydb.cursor()
query_string = "SELECT rolename FROM roles WHERE idroles = %s"
cursor.execute(query_string, (idroles, ))
data = cursor.fetchall()
return data[0]
def getResourceId(resourcename):
cursor = mydb.cursor()
query_string = "SELECT idresources FROM resources WHERE name = %s"
cursor.execute(query_string, (resourcename, ))
data = cursor.fetchall()
return str(data[0][0])
