def saveEvent(data):
data["event_id"] = 0
dte = str(data["event_date"])
dte = dte[:-9]
data["event_date"] = datetime.strptime(dte, "%d/%m/%Y").date()
cursor = db.cursor()
sql = """INSERT INTO `events`(`event_id`, `event_name`, `event_date`, `event_desc`)
VALUES (%s, %s, %s, %s)"""
try:
cursor.execute(sql, (data["event_id"], data["event_name"],
data["event_date"], data["event_desc"]))
db.commit()
ret = cursor.lastrowid
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
# Rollback in case there is any error
db.rollback()
ret = False
return ret
def saveClass(data):
data["class_id"] = 0
data["deleted"] = "0"
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """
INSERT INTO `classes`(`class_id`, `class_name`, `form_name`, `deleted`)
VALUES (%s, %s, %s, %s)
"""
try:
# Execute the SQL command
cursor.execute(sql, (data["class_id"], data["class_name"],
data["form_name"], data["deleted"]))
# Commit your changes in the database
db.commit()
ret = cursor.lastrowid
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
# Rollback in case there is any error
db.rollback()
ret = False
# # disconnect from server
# db.close()
return ret
def changePassword(data):
m = hashlib.md5()
m.update(data['password'])
data['password'] = m.hexdigest()
cursor = db.cursor()
sql = """ UPDATE users SET password = %s WHERE user_id = %s """
data = (data["password"], data["user_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
# ret = e
ret = False
return ret
def updateFormFoursToAlumnus(formFours):
cursor = db.cursor()
user_ids = ""
no_of_ids = len(formFours) - 1
for key, val in enumerate(formFours):
if key == no_of_ids: # To avoid adding a comma after the last id
user_ids = user_ids + str(val)
else:
user_ids = user_ids + str(val) + ', '
sql = """UPDATE users SET alumnus = 1 WHERE user_id IN (%s) AND deleted = 0""" % user_ids
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def index_temp():
if request.method == "GET":
session.clear()
return render_template("temp/index.html")
if request.method == "POST":
name = request.form['name']
email = request.form['email']
university = request.form['university']
cursor.execute("SELECT file FROM users_temp WHERE email='%s'" %
(email))
data = cursor.fetchall()
if (cursor.rowcount > 0):
session['user_file'] = data[0][0]
session['logged'] = True
return jsonify({"status": "User already exists!"})
session['user_file'] = name.lower().replace(" ", "") + email.replace(
".", "") + ".txt"
cursor.execute(
"INSERT INTO users_temp(name, email, university, file) VALUES('%s', '%s', '%s', '%s')"
% (name, email, university, name.lower().replace(" ", "") +
email.replace(".", "") + ".txt"))
db.commit()
return jsonify({"status": "User added put into db!"})
def editEvent(data):
dte = str(data["event_date"])
dte = dte[:-9]
data["event_date"] = datetime.strptime(dte, "%d/%m/%Y").date()
cursor = db.cursor()
sql = """ UPDATE events SET
event_name = %s,
event_desc = %s,
event_date = %s
WHERE event_id = %s """
data = (data["event_name"], data["event_desc"], data["event_date"],
data["event_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def reset_pass():
if request.method == "GET":
return render_template("reset-pass.html",
navbar=Markup(NAVBAR),
footer=Markup(FOOTER))
if request.method == "POST":
email = request.form['email']
password = request.form['password']
hash = hashlib.sha512(password.encode())
cursor.execute(
"UPDATE users SET password='******' WHERE SHA2(email, 512)='%s'" %
(hash.hexdigest(), email))
db.commit()
return jsonify({
"status": "success",
"title": "Success!",
"message": "Password reset successfully!",
"href": core_str + "/login"
})
def updateTotalledFieldManyRows(result_ids):
result_ids_string = ''
indexes = len(result_ids) - 1 # -1 because first index is 0
for key, val in enumerate(result_ids):
if key == indexes: # To avoid adding + or , after the last subject
result_ids_string = result_ids_string + str(val)
else:
result_ids_string = result_ids_string + str(val) + ', '
cursor = db.cursor()
sql = """ UPDATE `exam_results` SET `totalled`= 1 WHERE exam_result_id IN (%s) """ % result_ids_string
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def editStudent(data):
cursor = db.cursor()
sql = """ UPDATE users SET
reg_no = %s,
first_name = %s,
last_name = %s,
surname = %s,
dob = %s,
gender = %s,
class_id = %s,
kcpe_marks = %s,
birth_cert_no = %s,
next_of_kin_name = %s,
next_of_kin_phone = %s
WHERE user_id = %s """
data = (data["reg_no"], data["first_name"], data["last_name"], data["surname"], data["dob"], data["gender"], data["class_id"],
data["kcpe_marks"], data["birth_cert_no"], data["kin_names"], data["kin_phone"], data["user_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
# ret = e
ret = False
return ret
def saveOneClass(class_name, form_name):
class_id = 0
deleted = "0"
cursor = db.cursor()
sql = """
INSERT INTO `classes`(`class_id`, `class_name`, `form_name`, `deleted`)
VALUES (%s, %s, %s, %s)
"""
try:
cursor.execute(sql, (class_id, class_name, form_name, deleted))
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
# print(e)
# print cursor._last_executed
db.rollback()
ret = False
return ret
def saveOneExam(data):
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d')
cursor = db.cursor()
sql = """
INSERT INTO `exams`(`exam_id`, `exam_name`, `form`, `term`, `year`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
try:
cursor.execute(sql, (0, data["exam_name"], data["form"], data["term"], data["year"], data["created_at"], 0))
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
# db.close()
return ret
def editSubject(data):
alias = data['subject_alias']
# Remove spaces and special characters from the alias
alias = ''.join(e for e in alias if e.isalnum())
data['subject_alias'] = alias
cursor = db.cursor()
sql = """ UPDATE subjects SET
subject_name = %s,
subject_alias = %s,
compulsory = %s,
group_id = %s
WHERE subject_id = %s """
data = (data["subject_name"], data["subject_alias"], data["compulsory"],
data["group"], data["subject_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def editTeacher(data):
cursor = db.cursor()
sql = """ UPDATE users SET
first_name = %s,
last_name = %s,
surname = %s,
email = %s,
username = %s,
dob = %s,
gender = %s,
subject1 = %s,
subject2 = %s
WHERE user_id = %s """
data = (data["first_name"], data["last_name"], data["surname"], data["email"], data["username"], data["dob"], data["gender"], data["subjectOneID"], data["subjectTwoID"], data["user_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
# ret = e
ret = False
return ret
def saveStudent(data):
data["user_id"] = 0
dob = str(data["dob"])
dob = dob[:-9]
data["dob"] = datetime.strptime(dob, "%d/%m/%Y").date()
# student username = their regno@schoolname eg, 7276@kangangu.
# student password = next of kin phone number
data["username"] = str(data['reg_no'])+"@kangangu"
data["password"] = data["kin_phone"]
m = hashlib.md5()
m.update(data["password"])
data["password"] = m.hexdigest()
data["role"] = "student"
data["status"] = "Active"
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d %H:%M:%S')
data["deleted"] = "0"
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """
INSERT INTO `users`(`user_id`, `reg_no`, `first_name`, `last_name`, `surname`, `dob`, `gender`, `username`,
`password`, `role`, `class_id`, `kcpe_marks`, `birth_cert_no`, `next_of_kin_name`, `next_of_kin_phone`,
`address`, `status`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
# Execute the SQL command
cursor.execute(sql, (data["user_id"], data["reg_no"], data["first_name"], data["last_name"], data["surname"], data["dob"], data["gender"],
data["username"], data["password"], data["role"], data["class_id"], data["kcpe_marks"], data["birth_cert_no"],
data["kin_names"], data["kin_phone"], data["address"], data["status"], data["created_at"], data["deleted"] ))
# Commit your changes in the database
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
# Rollback in case there is any error
db.rollback()
ret = False
# # disconnect from server
# db.close()
return ret
def add_model():
if request.method == "GET":
return render_template("add-model.html",
logout=Markup(NAVLOGREG),
navbar=Markup(NAVBARLOGGED),
footer=Markup(FOOTER))
if request.method == "POST":
model_name = request.form['model_name']
model_desc = request.form['model_desc']
dataset = request.form['dataset']
code = request.files['code']
model = request.files['model']
code_filename = secure_filename(code.filename)
model_filename = secure_filename(model.filename)
hash_id = hashlib.sha512(str(session['user_id']).encode())
if code_filename.split(".")[-1] != "zip" or model_filename.split(
".")[-1] != "zip":
return jsonify({
"status": "error",
"title": "Error!",
"message": "Only zip files accepted!",
"href": core_str + "/add-model"
})
code_filename_hashed = ''.join(
code_filename.split(".")
[0:-1]) + hash_id.hexdigest() + "." + code_filename.split(".")[-1]
model_filename_hashed = ''.join(
model_filename.split(".")
[0:-1]) + hash_id.hexdigest() + "." + model_filename.split(".")[-1]
code.save("code/" + code_filename_hashed)
model.save("model/" + model_filename_hashed)
cursor.execute("SELECT name FROM users WHERE id=%d" %
(session['user_id']))
data = cursor.fetchall()
cursor.execute(
"INSERT INTO model(uid, uname, name, des, dataset, code, model, approved) VALUES('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d')"
% (session['user_id'], data[0][0], model_name, model_desc, dataset,
code_filename_hashed, model_filename_hashed, 0))
db.commit()
return jsonify({
"status": "success",
"title": "Success!",
"message": "Model added successfully!",
"href": core_str + "/profile"
})
def verify():
if request.method == "GET":
email = request.args.get('q')
cursor.execute("UPDATE users SET status=1 WHERE email='%s'" % (email))
db.commit()
return render_template("login.html", verified="1")
def model():
if request.method == "GET":
id = int(request.args.get('q'))
cursor.execute("SELECT * FROM model WHERE id=%d" % (id))
data = cursor.fetchall()
dataset_link = "Not given!"
if (data[0][5] != ""):
dataset_link = data[0][5]
this_owner = False
if (session['user_id'] == data[0][1]):
this_owner = True
return render_template("model.html",
name=data[0][3],
des=Markup(data[0][4]),
dataset=dataset_link,
code="code/" + data[0][6],
model="model/" + data[0][7],
owner=this_owner,
logout=Markup(NAVLOGREG),
navbar=Markup(NAVBARLOGGED),
footer=Markup(FOOTER))
if request.method == "POST":
id = int(request.form['id'])
desc = request.form['desc']
cursor.execute("SELECT des FROM model where id=%d" % (id))
data = cursor.fetchone()
if (data[0] == desc):
return jsonify({
"status": "error",
"title": "Error!",
"message": "Nothing to update!",
"href": core_str + "/model?q=" + str(id)
})
cursor.execute("UPDATE model SET des='%s' WHERE id='%d'" % (desc, id))
db.commit()
return jsonify({
"status": "success",
"title": "Success!",
"message": "Description updated successfully!",
"href": core_str + "/model?q=" + str(id)
})
def put():
question = request.form['question'].replace("'", r"\'")
type = request.form['type']
ask_friend = int(request.form['ask_friend'])
cursor.execute(
"INSERT INTO questions(question, type, ask_friend) VALUES('%s', '%s', '%d')"
% (question, type, ask_friend))
db.commit()
return jsonify({"status": "Question successfully put into db!"})
def saveAdminDetails(data):
data["user_id"] = 0
dob = str(data["dob"])
dob = dob[:-9]
data["dob"] = datetime.strptime(dob, "%d/%m/%Y").date()
m = hashlib.md5()
m.update(data['password'])
data['password'] = m.hexdigest()
data["role"] = "admin"
data["status"] = "Active"
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d %H:%M:%S')
data["deleted"] = "0"
cursor = db.cursor()
sql = """
INSERT INTO `users`(`user_id`, `first_name`, `last_name`, `surname`, `email`, `phone_number`, `dob`, `gender`,
`username`, `password`, `role`, `status`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
cursor.execute(
sql,
(data["user_id"], data["first_name"], data["last_name"],
data["surname"], data["email"], data["phone"], data["dob"],
data["gender"], data["username"], data["password"], data["role"],
data["status"], data["created_at"], data["deleted"]))
db.commit()
data['user_id'] = cursor.lastrowid
data['class_id'] = 0
data['reg_no'] = 0
ret = data
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
# disconnect from server
cursor.close()
return ret
def markRowsAsUploaded(table_name):
cursor = db.cursor()
sql = """UPDATE %s SET uploaded = 1""" % table_name
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def add():
if request.method == "GET":
return render_template("add.html")
elif request.method == "POST":
title = request.form['title']
desc = request.form['desc']
cursor.execute(
"INSERT INTO learnt(title, description) VALUES('%s', '%s')" %
(title, desc))
db.commit()
return jsonify({"ret": "success"})
def saveSubject(data):
alias = data['subject_alias']
alias = ''.join(
e for e in alias
if e.isalnum()) # Remove spaces and special characters from the alias
data['subject_alias'] = alias
data["deleted"] = "0"
cursor = db.cursor()
sql = """
INSERT INTO `subjects`(`subject_id`, `subject_name`, `subject_alias`, `compulsory`, `group_id`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s)
"""
try:
cursor.execute(sql,
(0, data["subject_name"], data["subject_alias"],
data["compulsory"], data["group"], data["deleted"]))
db.commit()
# Add subject as column to exam_results table
data["subject_alias"] = data["subject_alias"].lower()
sql = "ALTER TABLE exam_results ADD COLUMN %s DOUBLE DEFAULT NULL" % data[
"subject_alias"]
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
print e
db.rollback()
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
# db.close()
return ret
def updateSubjectsTakenOneStudent(user_id, subjects_taken):
cursor = db.cursor()
sql = """UPDATE users SET subjects_taken = '%s' WHERE user_id = %s """ % (subjects_taken, user_id)
try:
cursor.execute(sql)
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def saveTeacher(data):
data["user_id"] = 0
dob = str(data["dob"])
dob = dob[:-9]
data["dob"] = datetime.strptime(dob, "%d/%m/%Y").date()
m = hashlib.md5()
m.update(data['password'])
data['password'] = m.hexdigest()
data["role"] = "teacher"
data["status"] = "Active"
c = datetime.now()
data["created_at"] = c.strftime('%Y-%m-%d %H:%M:%S')
data["deleted"] = "0"
cursor = db.cursor()
sql = """
INSERT INTO `users`(`user_id`, `first_name`, `last_name`, `surname`, `email`, `phone_number`, `dob`, `gender`, `username`,
`password`, `role`, `subject1`, `subject2`, `address`, `national_id`, `tsc_no`, `status`, `created_at`, `deleted`)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
cursor.execute(sql, (data["user_id"], data["first_name"], data["last_name"], data["surname"], data["email"], data["phone"], data["dob"], data["gender"],
data["username"], data["password"], data["role"], data["subjectOneID"], data["subjectTwoID"], data["address"], data["national_id"],
data["tsc_no"], data["status"], data["created_at"], data["deleted"]))
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
# # disconnect from server
# db.close()
return ret
def deleteEvent(id):
cursor = db.cursor()
sql = """ UPDATE events SET deleted = %s WHERE event_id = %s """
data = (1, id)
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def saveOneForm(form_name, streams):
cursor = db.cursor()
sql = """INSERT INTO `forms`(`form_name`, `no_of_streams`) VALUES (%s, %s)"""
try:
cursor.execute(sql, (form_name, streams))
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
return ret
def updateStudentsToNextClass(current_id, next_id):
cursor = db.cursor()
sql = """UPDATE users SET class_id = %s WHERE class_id = %s AND alumnus = 0 AND deleted = 0 AND role = 'student'""" % (
next_id, current_id)
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def updateTotalForOneRowExamResults(result_id, stats):
cursor = db.cursor()
sql = """ UPDATE `exam_results` SET total = %s, points = %s, mean = %s WHERE exam_result_id = %s """ % (
stats['total'], stats['points'], stats['mean'], result_id)
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
# print e
db.rollback()
ret = False
return ret
def updateClassPosition(data):
cursor = db.cursor()
sql = """ UPDATE `exam_results` SET class_pos = %s WHERE exam_result_id = %s """ % (
data['class_position'], data['exam_result_id'])
try:
cursor.execute(sql)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
db.rollback()
ret = False
return ret
def deleteTeacher(id):
cursor = db.cursor()
sql = """ UPDATE users SET deleted = %s WHERE user_id = %s """
data = (1, id)
try:
cursor.execute(sql, data)
db.commit()
ret = True
except(MySQLdb.Error, MySQLdb.Warning) as e:
# print(e)
db.rollback()
ret = False
return ret
