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 update_db(self, filename):
db_execute(filename,
'DELETE FROM INCLUDE WHERE SURVEYID = {0}'.format(self._id))
for i in range(len(self._questions)):
db_execute(
filename,
'INSERT INTO INCLUDE (SURVEYID, QUESTIONID, POSITION) VALUES ("{0}", "{1}", "{2}")'
.format(self._id, self._questions[i].get_id(), i + 1))
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 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 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 deny_guest(zID): db_execute(DATABASE_FILENAME, 'DELETE FROM PASSWORDS WHERE ZID = ' + str(zID))
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]
))
def guest_register(zID, password, course):
db_execute(DATABASE_FILENAME, 'INSERT INTO PASSWORDS (ZID, PASSWORD, ROLE) VALUES ("{0}", "{1}", "{2}")'.format(
zID, password, 'pending:'+course.name+':'+course.semester))
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:
db_execute(DATABASE_FILENAME, 'INSERT INTO ENROLMENTS (ZID, COURSE, SEMESTER) VALUES ("{0}", "{1}", "{2}")'.format(
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'):