def login_user():
"""The purpose of this view function is to check if the enetered registration number and password exists in a database.
If the details match and the user is an admin then he is redirected to the update_page where he can make changes.
If the details match and the user is not an admin,the user is logged in and redirected to the user profile page.
Else it will display the login form again and the user is not allowed to visit rest of the website.
"""
form = LoginForm()
if request.method == 'POST' and form.validate_on_submit():
name1 = form.user_na.data
pass1 = form.user_psw.data
_sql1 = "SELECT password FROM main_table WHERE reg=%s;"
_sql2 = "SELECT reg FROM admin_table WHERE reg=%s;"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql1, (form.user_na.data, ))
user_data1 = cursor.fetchone()
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql2, (form.user_na.data, ))
user_data2 = cursor.fetchone()
if check_password_hash(user_data1[0],
form.user_psw.data) and user_data2:
session['name'] = name1
session['admin'] = True
return redirect(url_for('main.profile_page'))
elif check_password_hash(user_data1[0], form.user_psw.data):
session['name'] = name1
session['admin'] = False
return redirect(url_for('main.profile_page'))
else:
flash("Please enter the correct password", category="danger")
return render_template('login.html', form=form, disp=True)
return render_template('login.html', form=form, disp=True)
def update_db_details():
"""Updates the database with the given details.
After entering the details in 'update_details.html', the form is checked if all selected
fields are filled. And then the database is updated.
Later the admin is taken back to 'update_details.html' again with the same fields.This is
due to the case that the admin may be required to change the same details for each person
(for eg : While registering students for a new sem).
"""
if request.method == 'POST':
base_sql = "UPDATE main_table SET "
for i in request.form.to_dict().keys():
base_sql += i + "=%s ,"
base_sql = base_sql[:-1] + "WHERE reg=" + request.form['reg'] + ";"
detail_values = []
detail_keys = []
for i, j in request.form.to_dict().items():
detail_values.append(j)
detail_keys.append(i)
detail_keys.remove('reg')
if "" in detail_values:
flash("All fields are required.", category="warning")
return render_template('update_details.html', fields=detail_keys)
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql, tuple(detail_values))
flash("Updated Database Successfully.", category="success")
return render_template('update_details.html', fields=detail_keys)
return render_template('admin_update_select.html')
def update_marks_excel():
"""Updates the marks_table from the data in the excel sheet uploaded.
This view functions takes the uploaded excel sheet and adds the values to the marks_table of the database.
So that the admin is not required to enter each of the records into the database explicitly.
Note:The uploaded excel sheet should of the format (reg,sem,subject_code,grade,credits).
-----------------------------------
"""
form = MarksExcel()
if request.method == 'POST' and form.validate_on_submit():
sheet = form.excel
temp_path = os.path.join(current_app.config['UPLOAD_FOLDER'],
secure_filename(sheet.data.filename))
sheet.data.save(temp_path)
dbu = pd.read_excel(temp_path)
base_sql = "INSERT INTO marks_table VALUES "
for x in dbu.values:
base_sql += str(tuple(x)) + ','
base_sql = base_sql[:-1] + ";"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql)
os.remove(temp_path)
flash("Updated database successfully.", category="success")
return render_template('admin_update.html', form=form)
return render_template('admin_update.html', form=form)
def view_user_profile():
"""Admin can view user profiles.
This function is used by the admin to view user profiles.
And an update button is displayed below the user profiles, so the admin can make changes to profile.
"""
if request.method == 'POST':
if (request.form['reg'] == ""):
flash("Registration number field is mandatory", category="warning")
return render_template('remove.html', title="View an User profile")
base_sql = "SELECT * FROM main_table WHERE reg=%s;"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql, (request.form['reg'], ))
user_data = cursor.fetchone()
if not user_data:
flash("Enter a valid registration number.", category="warning")
return render_template('remove.html', title="View an User profile")
user_img = os.path.join(current_app.config['RELATIVE_FOLDER'],
request.form['reg'] + '.jpg')
return render_template(
'profile.html',
user_image=user_img,
name=user_data[2],
reg=user_data[0],
branch=user_data[13],
sem=(user_data[5] if int(user_data[5]) != 9 else "Passed Out"),
DOB=user_data[4],
father=user_data[3],
mail=user_data[6],
phone=user_data[7])
return render_template('remove.html', title="View an User profile")
def update_db_admin():
"""Makes an user admin or removes an admin.
This function is used to remove admin status of an admin.
And this is also used to make an user an admin.
"""
if request.method == 'POST':
if request.form['option'] == 'ADD' and request.form["reg"]:
base_sql = "INSERT INTO admin_table VALUES (%s);"
elif request.form['option'] == 'DELETE' and request.form['reg']:
base_sql = "DELETE FROM admin_table WHERE reg=%s;"
else:
flash(
"Atleast one option should be selected, enter a valid Registration number",
category="warning")
return render_template('add_remove_admin.html')
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql, (request.form['reg'], ))
flash("Updated Database successfully", category="success")
flash("Make sure that the admin being added is alredy an user.",
category="info")
return render_template('add_remove_admin.html')
flash("Make sure that the admin being added is alredy an user.",
category="info")
return render_template('add_remove_admin.html')
def add_db_student():
"""Adds a new user to the database.
This function is used to add a new user to database.
Initially the registration number is the password of the users.
But they are allowed to change their passwords by logging into website with their registration number as their password.
"""
fields = [
'name', 'father_name', 'DOB', 'current_sem', 'mail', 'phone',
'nationality', 'category', 'religion', 'caste', 'blood_group', 'branch'
]
if request.method == 'POST':
base_sql = "INSERT INTO main_table (reg,password,name,father_name,DOB,current_sem,mail,phone,nationality,category,religion,caste,blood_group,branch) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(
base_sql,
(request.form['reg'],
generate_password_hash(request.form['reg']),
request.form['name'], request.form['father_name'],
request.form['DOB'], request.form['current_sem'],
request.form['mail'], request.form['phone'],
request.form['nationality'], request.form['category'],
request.form['religion'], request.form['caste'],
request.form['blood_group'], request.form['branch']))
flash("Inserted User successfully into Database.", category="success")
return render_template('insert_user.html', fields=fields)
flash("Make sure the user is not already present in the database.",
category="info")
return render_template('insert_user.html', fields=fields)
def profile_page():
"""This view function is for the Profile page of the user.
This function loads the details of the user from the database, sends the details to the profile.html template .So that it can display the user profile.
This function also sets some session variables that can be used by other view functions.
"""
_sql = "SELECT * FROM main_table WHERE reg=%s;"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql, (session['name'], ))
user_data = cursor.fetchone()
session['mail'] = user_data[6] #we are setting session variables.
if not session['admin']:
session['current_sem'] = int(user_data[5])
session['password'] = user_data[1]
user_img = os.path.join(current_app.config['RELATIVE_FOLDER'],
session['name'] + '.jpg')
return render_template('profile.html',
user_image=user_img,
name=user_data[2],
reg=user_data[0],
branch=user_data[13],
sem=(user_data[5] if
(user_data is not None
or int(user_data[5]) != 9) else "Passed Out"),
DOB=user_data[4],
father=user_data[3],
mail=user_data[6],
phone=user_data[7])
def update_password():
"""Updates user login password.
In this the user is required to enter the new password of string length 8 to 20 . And the user is asked to reenter the same password to make sure that he entered what he was typing.
Then a mail is sent to registered mail of the user.
"""
form = PasswordForm()
if request.method == "POST" and form.validate_on_submit():
if check_password_hash(session['password'], form.user_psw.data):
_sql = "UPDATE main_table SET password=%s WHERE reg=%s;"
with DBcursor(
**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql, (generate_password_hash(
form.new_pass.data), session['name']))
sendmail_asynchroously(
to=[session['mail']],
subject="password has been updated",
message=
"Your Password has been changed , if you didn't do it please contact adminstration."
)
flash("Updated Password successfully", category="success")
return redirect(url_for('main.logout'))
else:
flash(
"Please enter your current password,The password entered doesn't match",
category="danger")
return render_template('update.html', form=form, disp=True)
def update_phone():
"""Updates user phone number.
If the password entered matches then the phone number is updated.
Then a mail is sent to the mail id of the user describing the phone number has been changed along with the new phone number.
"""
form = PhoneForm()
if request.method == "POST" and form.validate_on_submit():
if check_password_hash(session['password'], form.user_psw.data):
_sql = "UPDATE main_table SET phone=%s WHERE reg=%s;"
with DBcursor(
**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql, (form.new_phone.data, session['name']))
sendmail_asynchroously(
to=[session['mail']],
subject="Phone Number has been updated",
message="Your phone number has been changed to " +
form.new_phone.data +
" if you didn't do it please contact adminstration.")
flash("Updated phone number successfully", category="success")
return redirect(url_for('main.update_details'))
else:
flash(
"Please enter your current password,The password entered doesn't match",
category="danger")
return render_template('update.html', form=form, disp=True)
def forgot_password():
"""This function is used when a user forgets his password.
This function sends a new temporary password to user via mail.
And the user can change the password later.
"""
form = ForgotPasword()
session.clear()
if request.method == "POST":
_sql2 = "SELECT mail FROM main_table WHERE reg=%s;"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql2, (form.user_na.data, ))
user_data2 = cursor.fetchone()
if not user_data2:
flash(
"Please enter a valid Registration number and then press Forgot Password",
category="info")
return render_template('login.html', form=form, disp=False)
characters = string.ascii_letters + string.digits + "#@$~!_%"
pass_string = "".join(
choice(characters) for i in range(randint(8, 19)))
_sql = "UPDATE main_table SET password=%s WHERE reg=%s;"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(
_sql, (generate_password_hash(pass_string), form.user_na.data))
sendmail_asynchroously(
to=[user_data2[0]],
subject="password has been updated",
message="",
template="Your temporary password is :<strong>" + pass_string +
"</strong>")
flash(
"A temporary password has been sent to your registererd Mail address. Login with the password.",
category="success")
flash("Incase of any trouble contact adminstration.", category="info")
return render_template('login.html', form=form, disp=False)
return render_template('login.html', form=form, disp=False)
def remove_db_student():
"""Removes an user completly from the database.
This function is used to delete all the records of the user from both the main table as well as the marks table.
A warning is also displayed to first remove the user as admin, in case if the user who is going to be deleted is an admin.
"""
if request.method == 'POST':
base_sql = "DELETE FROM main_table WHERE reg=%s"
base_sql2 = "DELETE from marks_table WHERE reg=%s"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql2, (request.form['reg'], ))
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql, (request.form['reg'], ))
flash("Removed user successfully.", category="success")
flash(
"Make sure the user you want to remove from database exists in database.",
category="warning")
return render_template('remove.html', title="Remove an User")
flash(
"Make sure the user you want to remove from database exists in database.",
category="warning")
flash("Before you delete admin details first remove him/her as admin.",
category="info")
return render_template('remove.html', title="Remove an User")
def update_db_marks():
"""Updates students marks, if there was a mistake.
The admin is required to enter the registration number of the student,subject code, correct grade and credits.
"""
form = MarksUpdate()
if request.method == 'POST' and form.validate_on_submit():
base_sql = "UPDATE marks_table SET grade=%s,credits=%s WHERE reg=%s AND sem=%s AND subject_code=%s"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(base_sql,
(request.form['grade'], request.form['credit'],
request.form['reg'], request.form['sem'],
request.form['sub_code']))
flash("Updated Database Successfully.", category="success")
return render_template('update.html', form=form)
return render_template('update.html', form=form)
def results(sem):
"""This is used to display the selected semster's CPI,SPI,grades of that particular sem."""
_sql = "SELECT sem,subject_code,grade,credits FROM marks_table WHERE reg=%s AND sem < %s"
with DBcursor(**current_app.config['DATABASE_CONNECTIVITY']) as cursor:
cursor.execute(_sql, (session['name'], session['current_sem']))
user_data = cursor.fetchall()
current_sem_list = []
num1, num, den, den1, cpi, spi = [0.0, 0.0, 0.0, 0.0, 0.0, 0.0]
for row in user_data:
if row[0] <= int(sem):
num += row[2] * row[3]
den += row[3]
if int(sem) == row[0]:
num1 += row[2] * row[3]
den1 += row[3]
current_sem_list.append((row[1], row[2], row[3]))
spi = round(num1 / den1, 2)
cpi = round(num / den, 2)
return render_template('display_results.html',
sem_list=current_sem_list,
cpi=cpi,
spi=spi,
sem=sem)