def load_from_db(self, filename, id):
result = db_select(
filename, """SELECT ID, START, END, COURSE, SEMESTER, STATE
FROM SURVEYS
WHERE ID = """ + str(id))[0]
if not result:
return None
self._id = result[0]
self._start = result[1]
self._end = result[2]
self._course = find_course(result[3], result[4])
self._course.survey = self
self._questions = []
qids = [
x[0] for x in db_select(
filename,
"""SELECT QUESTIONID FROM INCLUDE WHERE SURVEYID = """ +
str(id) + " ORDER BY POSITION")
]
for qid in qids:
newQuestion = Question()
newQuestion.load_from_db(filename, qid)
self._questions.append(newQuestion)
return self
def write_to_db(self, filename):
question_ids = [
i[0] for i in db_select(filename, "SELECT ID FROM QUESTIONS")
]
if question_ids == []:
max_question_id = 0
else:
max_question_id = max(question_ids)
db_execute(
filename,
"""INSERT INTO QUESTIONS (ID, QUESTION_TEXT, TEXT, MULTI, MANDATORY, VISIBLE)
VALUES ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}")
""".format(str(max_question_id + 1), self._question_text,
1 if self._text else 0, 1 if self._multi else 0,
1 if self._mandatory else 0, 1 if self._visible else 0))
option_ids = [
i[0] for i in db_select(filename, "SELECT ID FROM OPTIONS")
]
if option_ids == []:
max_option_id = 0
else:
max_option_id = max(option_ids)
for i, option in enumerate(self._options):
db_execute(
filename,
'INSERT INTO OPTIONS (ID, OPTION, QUESTIONID) VALUES ("{0}", "{1}", "{2}")'
.format(str(i + max_option_id + 1), option.text,
str(max_question_id + 1)))
return max_question_id + 1
def get_surveys(state = None): if state != None: ids = [i[0] for i in db_select(SURVEY_FILENAME, "SELECT ID FROM SURVEYS WHERE STATE = " + str(state))] else: ids = [i[0] for i in db_select(SURVEY_FILENAME, "SELECT ID FROM SURVEYS")] surveys = [] for id in ids: survey = Survey() survey.load_from_db(SURVEY_FILENAME, id) surveys.append(survey) return surveys
def has_responded_to(self, filename, survey):
result = db_select(
filename,
"""SELECT ID FROM RESPONSES WHERE ZID = {0} AND SURVEYID = {1}""".
format(self._zID, survey.id))
if len(result) > 0:
return True
return False
def get_pending_guests():
users = db_select(DATABASE_FILENAME, 'SELECT ZID, ROLE FROM PASSWORDS')
guests = []
for user in users:
if user[1].startswith('pending'):
guests.append([int(user[0]), find_course(user[1].split(':')[1], user[1].split(':')[2])])
return guests
def write_to_db(self, filename):
existing_questions = []
for qid in [
x[0] for x in db_select(filename, "SELECT ID FROM QUESTIONS")
]:
question = Question()
question.load_from_db(filename, qid)
existing_questions.append(question)
survey_ids = [
i[0] for i in db_select(filename, "SELECT ID FROM SURVEYS")
]
if survey_ids == []:
max_survey_id = 0
else:
max_survey_id = max(survey_ids)
db_execute(
filename,
"""INSERT INTO SURVEYS (ID, START, END, COURSE, SEMESTER, STATE)
VALUES ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}")
""".format(str(max_survey_id + 1), str(self._start), str(self._end),
self._course.name, self._course.semester, str(self._state)))
i = 1
for question in self._questions:
exists = False
for existing_question in existing_questions:
if not exists and question.matches(existing_question):
db_execute(
filename,
"""INSERT INTO INCLUDE (SURVEYID, QUESTIONID, POSITION)
VALUES ("{0}", "{1}", "{2}")""".format(
str(max_survey_id + 1),
str(existing_question.get_id()), i + 1))
exists = True
if not exists:
write_id = question.write_to_db(filename)
db_execute(
filename,
"""INSERT INTO INCLUDE (SURVEYID, QUESTIONID, POSITION)
VALUES ("{0}", "{1}", "{2}")""".format(str(max_survey_id + 1),
str(write_id), i + 1))
i += 1
return max_survey_id + 1
def find_course(name, semester):
result = db_select(DATABASE_FILENAME, 'SELECT NAME, SEMESTER FROM COURSES WHERE NAME = "{0}" AND SEMESTER = "{1}"'.format(
name, semester
))
if len(result) == 0:
return None
course = Course(name, semester)
return course
def load_course_from_db(self, filename, name, semester):
result = db_select(
filename, """SELECT ID
FROM SURVEYS
WHERE COURSE = "{0}" AND SEMESTER = "{1}" """.format(name, semester))
if result:
self.load_from_db(filename, result[0][0])
return self
else:
return None
def read_all_questions():
question_list = []
for questionID in [
i[0]
for i in db_select(QUESTIONS_FILENAME, "SELECT ID FROM QUESTIONS")
]:
question = Question()
question.load_from_db(QUESTIONS_FILENAME, questionID)
question_list.append(question)
return question_list
def update_db(self, filename, id):
db_execute(
filename, """UPDATE QUESTIONS
SET QUESTION_TEXT = "{0}", TEXT = "{1}", MULTI = "{2}", MANDATORY = "{3}", VISIBLE = "{4}"
WHERE ID = {5}""".format(self._question_text, 1 if self._text else 0,
1 if self._multi else 0,
1 if self._mandatory else 0,
1 if self._visible else 0, str(id)))
current_options = db_select(
filename, "SELECT ID, OPTION FROM OPTIONS WHERE QUESTIONID = " +
str(id) + " ORDER BY ID")
if len(current_options) < len(self._options):
option_ids = [
i[0] for i in db_select(filename, "SELECT ID FROM OPTIONS")
]
if option_ids == []:
max_option_id = 0
else:
max_option_id = max(option_ids)
for option in range(len(self._options)):
if option < len(current_options):
if current_options[option][1] != self._options[option].text:
db_execute(
filename, """UPDATE OPTIONS
SET OPTION = "{0}"
WHERE ID = {1}""".format(self._options[option].text,
current_options[option][0]))
else:
db_execute(
filename,
'INSERT INTO OPTIONS (ID, OPTION, QUESTIONID) VALUES ("{0}", "{1}", "{2}")'
.format(
str(option - len(current_options) + max_option_id + 1),
self._options[option].text, str(id)))
for i in range(len(self._options), len(current_options)):
db_execute(
filename, """DELETE FROM OPTIONS
WHERE ID = {0}""".format(current_options[i][0]))
return id
def load_from_db(self, filename, id):
result = db_select(
filename,
"""SELECT ID, QUESTION_TEXT, TEXT, MULTI, MANDATORY, VISIBLE
FROM QUESTIONS
WHERE ID = """ + str(id))[0]
self._id = int(result[0])
self._question_text = result[1]
self._text = True if result[2] == 1 else False
self._multi = True if result[3] == 1 else False
self._mandatory = True if result[4] == 1 else False
self._visible = True if result[5] == 1 else False
options = []
if (not self._text):
options = db_select(
filename, """SELECT OPTION
FROM OPTIONS
WHERE QUESTIONID = """ + str(id) + " ORDER BY ID")
for option in range(len(options)):
self._options.append(Option(option, options[option][0]))
def get_all_question_responses(survey, question):
result = db_select(
DATABASE_FILENAME, """SELECT RESPONSE
FROM RESPONSES
WHERE QUESTIONID = {0} AND SURVEYID = {1}""".format(
question.get_id(), survey.id))
responses = []
for item in result:
responses.append(item[0])
return responses
def login_user(zID, password, ip_addr):
zID = zID.replace('z', '')
try:
int(zID)
except:
return None
results = db_select(DATABASE_FILENAME, 'SELECT PASSWORD, ROLE FROM PASSWORDS WHERE ZID = ' + str(zID))
if len(results) == 0:
print('No user found with that zID')
return None
actual_password, role = results[0]
if role == 'admin':
user = Admin(zID, actual_password)
elif role == 'staff':
user = Staff(zID, actual_password)
enrolled_courses = db_select(DATABASE_FILENAME, 'SELECT COURSE, SEMESTER FROM ENROLMENTS WHERE ZID = '+str(zID))
for course, semester in enrolled_courses:
user.enrol(find_course(course, semester))
elif role == 'student':
user = Student(zID, actual_password)
enrolled_courses = db_select(DATABASE_FILENAME, 'SELECT COURSE, SEMESTER FROM ENROLMENTS WHERE ZID = '+str(zID))
for course, semester in enrolled_courses:
user.enrol(find_course(course, semester))
elif role == 'guest':
user = Guest(zID, actual_password)
enrolled_courses = db_select(DATABASE_FILENAME, 'SELECT COURSE, SEMESTER FROM ENROLMENTS WHERE ZID = '+str(zID))
for course, semester in enrolled_courses:
user.enrol(find_course(course, semester))
if user.login(zID, password):
logged_in[ip_addr] = user
print(zID, "logged in", "["+str(datetime.now())+"]",
{Admin: 'Admin', Student: 'Student', Staff: 'Staff', Guest: 'Guest'}[type(user)])
return user
return None
def save_response(filename, survey, request):
user = get_user(request.remote_addr)
try:
write_id = max([
int(x[0]) for x in db_select(filename, "SELECT ID FROM RESPONSES")
]) + 1
except ValueError:
write_id = 1
for question in survey.questions:
if question.get_type() == 'text':
response = request.form.get('TextBox' + str(question.get_id()))
db_execute(
filename,
"""INSERT INTO RESPONSES (ID, ZID, RESPONSE, QUESTIONID, SURVEYID)
VALUES ("{0}", "{1}", "{2}", "{3}", "{4}")""".format(
write_id, user.zID, response, question.get_id(),
survey.id))
write_id += 1
elif question.get_type() == 'single':
response = request.form.get('Q' + str(question.get_id()))
db_execute(
filename,
"""INSERT INTO RESPONSES (ID, ZID, RESPONSE, QUESTIONID, SURVEYID)
VALUES ("{0}", "{1}", "{2}", "{3}", "{4}")""".format(
write_id, user.zID, response, question.get_id(),
survey.id))
write_id += 1
else:
for response in request.form.getlist('Q' + str(question.get_id())):
db_execute(
filename,
"""INSERT INTO RESPONSES (ID, ZID, RESPONSE, QUESTIONID, SURVEYID)
VALUES ("{0}", "{1}", "{2}", "{3}", "{4}")""".format(
write_id, user.zID, response, question.get_id(),
survey.id))
write_id += 1
return redirect('/login')
def approve_guest(zID):
role = db_select(DATABASE_FILENAME, 'SELECT ROLE FROM PASSWORDS WHERE ZID = '+str(zID))[0][0]
db_execute(DATABASE_FILENAME, 'UPDATE PASSWORDS SET ROLE = "guest" WHERE ZID = ' + str(zID))
db_execute(DATABASE_FILENAME, 'INSERT INTO ENROLMENTS (ZID, COURSE, SEMESTER) VALUES ("{0}", "{1}", "{2}")'.format(
zID, role.split(':')[1], role.split(':')[2]
))
from datetime import datetime
from flask import redirect
from courses import find_course
from securityClasses import Student, Staff, Admin, Guest
from databasing import db_select, db_execute
import sqlite3
import csv
DATABASE_FILENAME = "data.db"
ENROLMENTS_FILE = "enrolments.csv"
PASSWORD_FILE = "passwords.csv"
print("Checking accounts data matches database...")
#Loading users - done on startup.
if not db_select(DATABASE_FILENAME, 'SELECT * FROM PASSWORDS WHERE ZID = "1"'):
db_execute(DATABASE_FILENAME, 'INSERT INTO PASSWORDS (ZID, PASSWORD, ROLE) VALUES ("1", "adminPass", "admin")')
with open(PASSWORD_FILE,'r') as csv_in:
existing_values = db_select(DATABASE_FILENAME, 'SELECT ZID, PASSWORD, ROLE FROM PASSWORDS')
reader = csv.reader(csv_in)
for zID, password, user_type in reader:
if (int(zID), password, user_type) not in existing_values:
db_execute(DATABASE_FILENAME, 'INSERT INTO PASSWORDS (ZID, PASSWORD, ROLE) VALUES ("{0}", "{1}", "{2}")'.format(
zID, password, user_type
))
#Enrolling students - done on startup.
with open(ENROLMENTS_FILE,'r') as csv_in:
existing_values = db_select(DATABASE_FILENAME, 'SELECT ZID, COURSE, SEMESTER FROM ENROLMENTS')
reader = csv.reader(csv_in)
for zID, name, semester in reader:
if (int(zID), name, semester) not in existing_values:
def get_all_courses(): courses = [] for name, semester in db_select(DATABASE_FILENAME, 'SELECT NAME, SEMESTER FROM COURSES'): courses.append(find_course(name, semester)) return courses
from courseClass import Course
from databasing import db_select, db_execute
import sqlite3
import csv
COURSE_LISTING = 'courses.csv'
DATABASE_FILENAME = 'data.db'
#Read in courses
print("Checking courses match database...")
with open(COURSE_LISTING,'r') as csv_in:
existing_values = db_select(DATABASE_FILENAME, 'SELECT NAME, SEMESTER FROM COURSES')
reader = csv.reader(csv_in)
for name, semester in reader:
if (name, semester) not in existing_values:
db_execute(DATABASE_FILENAME, 'INSERT INTO COURSES (NAME, SEMESTER) VALUES ("{0}", "{1}")'.format(name, semester))
print("Courses loaded.")
def find_course(name, semester):
result = db_select(DATABASE_FILENAME, 'SELECT NAME, SEMESTER FROM COURSES WHERE NAME = "{0}" AND SEMESTER = "{1}"'.format(
name, semester
))
if len(result) == 0:
return None
course = Course(name, semester)
return course
def get_all_courses():
courses = []
for name, semester in db_select(DATABASE_FILENAME, 'SELECT NAME, SEMESTER FROM COURSES'):