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 checkOldPassword(user_id, old_password):
m = hashlib.md5()
m.update(old_password)
old_password = m.hexdigest()
cursor = db.cursor()
# binary keyword to perform case sensitive search
sql = """SELECT user_id, first_name, last_name, surname, dob, gender, username, password
FROM `users`
WHERE user_id = %s AND binary password = %s"""
try:
cursor.execute(sql, (
user_id,
old_password,
))
count = cursor.rowcount
if count < 1:
ret = False
else:
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
ret = False
db.rollback()
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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
def saveSchDetails(school_name, no_of_subjects):
cursor = db.cursor()
sql = """INSERT INTO `system_setup`(`id`, `school_name`, `subjects_lower_forms`, `setup_complete`)
VALUES (%s, %s, %s, %s)"""
try:
cursor.execute(sql, (0, school_name, no_of_subjects, 1))
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
print(e)
db.rollback()
ret = False
return ret
def updateMarksOneRecord(exam_result_id, mark, subject):
cursor = db.cursor()
sql = """UPDATE `exam_results` SET %s = '%s' WHERE exam_result_id = '%s'""" % (
subject, mark, 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 saveMarksOneRecord(exam_id, subject, student_id, mark):
cursor = db.cursor()
sql = """INSERT INTO `exam_results`(`exam_result_id`, `exam_id`, `student_id`, %s)
VALUES (%s, %s, %s, %s)""" % (subject, 0, exam_id, student_id,
mark)
try:
cursor.execute(sql)
db.commit()
ret = cursor.lastrowid
except (MySQLdb.Error, MySQLdb.Warning) as e:
# print(e)
db.rollback()
ret = False
return ret
def editClass(data):
cursor = db.cursor()
sql = """ UPDATE classes SET
class_name = %s,
form_name = %s
WHERE class_id = %s """
data = (data["class_name"], data["form_name"], data["class_id"])
try:
cursor.execute(sql, data)
db.commit()
ret = True
except (MySQLdb.Error, MySQLdb.Warning) as e:
db.rollback()
ret = False
return ret
def getSubjectMean(data):
cursor = db.cursor()
# if class_id == 0 means all classes in form, exam_id == 0 to make sure there's data
if data['class_id'] == 0 and data["exam_id"] > 0:
sql = """SELECT AVG(%s)
FROM exam_results er
JOIN exams e ON e.exam_id = er.exam_id
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d
JOIN classes c ON c.class_id = u.class_id AND c.form_name = %d AND u.deleted = %d
WHERE e.exam_id = %d """ % (
data['subject_alias'], 0, int(data['form']), 0, data['exam_id'])
else:
sql = "SELECT AVG(%s) \
FROM exam_results er \
JOIN exams e ON e.exam_id = er.exam_id \
JOIN users u ON u.user_id = er.student_id AND u.deleted = %d \
JOIN classes c ON c.class_id = u.class_id AND c.class_id = %d AND u.deleted = %d \
WHERE e.exam_id = %d" % (
data['subject_alias'], 0, data['class_id'], 0, data['exam_id'])
try:
cursor.execute(sql)
mean = [item[0] for item in cursor.fetchall()]
mean = mean[0]
if mean is None:
mean = 0
ret = round(mean, 3)
except (MySQLdb.Error, MySQLdb.Warning) as e:
print e
ret = False
db.rollback()
return ret
def editExam(data):
cursor = db.cursor()
sql = """ UPDATE exams SET
exam_name = %s,
form = %s,
term = %s,
year = %s
WHERE exam_id = %s """
data = (data["exam_name"], data["form"], data["term"], data["year"], data["exam_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
