def init_instruments():
"""Create instruments"""
db = get_db()
instruments = [('Guitar', ), ('Paino', ), ('Drums', )]
db.executemany('INSERT INTO Instrument (Name) VALUES (?)', instruments)
db.commit()
def login():
"""Log in a registered teacher by adding the teacher id to the session."""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
teacher = db.execute(
'SELECT * FROM Teacher WHERE Username = ?', (username,)
).fetchone()
if teacher is None:
error = 'Incorrect username.'
elif not check_password_hash(teacher['Password'], password):
error = 'Incorrect password.'
if error is None:
# store the teacher id in a new session and return to the index
session.clear()
session['teacher_id'] = teacher['teacherId']
return redirect(url_for('teacher.index'))
flash(error)
return render_template('teacher/login.html')
def init_admin():
"""Create admin"""
db = get_db()
admin = ('admin', generate_password_hash('123456'), 'admin')
db.execute('INSERT INTO Admin (Username, Password, Name) VALUES (?, ?, ?)',
admin)
db.commit()
def index():
db = get_db()
courses = db.execute(
'SELECT Course.Name as CouName, Instrument.Name as InsName, Teacher.Nickname, Teacher.FirstName, Teacher.Lastname \
FROM Course JOIN Instrument ON Instrument.InstrumentId = Course.InstrumentId \
JOIN Teach ON Course.CourseId = Teach.CourseId \
JOIN Teacher ON Teacher.TeacherId = Teach.TeacherId').fetchall()
return render_template('course/courses.html', courses=courses)
def load_logged_in_admin():
"""If a admin id is stored in the session, load the admin object from
the database into ''g.admin''."""
admin_id = session.get('admin_id')
if admin_id is None:
g.admin = None
else:
g.admin = get_db().execute('SELECT * FROM Admin WHERE AdminId = ?',
(admin_id, )).fetchone()
def load_logged_in_student():
"""If a student id is stored in the session, load the student object from
the database into ``g.student``."""
student_id = session.get('student_id')
if student_id is None:
g.student = None
else:
g.student = get_db().execute(
'SELECT * FROM Student WHERE StudentId = ?',
(student_id, )).fetchone()
def load_logged_in_teacher():
"""If a teacher id is stored in the session, load the teacher object from
the database into ``g.teacher``."""
teacher_id = session.get('teacher_id')
if teacher_id is None:
g.teacher = None
else:
g.teacher = get_db().execute(
'SELECT * FROM Teacher WHERE TeacherId = ?', (teacher_id,)
).fetchone()
def courses():
db = get_db()
rows = db.execute(
'select Instrument.Name as InsName, Course.Name as CouName, Price, HousPerTime, NumOfTimes \
from Course join Instrument ON Course.CourseId = Instrument.InstrumentId'
).fetchall()
"""
course_list = db.execute(
'select instrument.name, Course.name as level, Course.price, teacher.Nickname, Course.HousPerTime, Course.NumOfTimes from Course \
left join Instrument on Instrument.InstrumentId = Course.InstrumentId \
join Teach on Teach.CourseId = Course.CourseId \
join Teacher on Teacher.TeacherId = teach.TeacherId'
).fetchall()
"""
return render_template('admin/courses.html', datas=rows)
def index():
"""Show teacher schedule by query database """
teacher_id = session.get('teacher_id')
db = get_db()
teacher_profile = db.execute(
'select Nickname, Firstname, Lastname, Birthday, email, telnum from Teacher where teacherid = ?', (teacher_id,)
).fetchone()
teachers = db.execute(
'select Study.Day, Study.Time, Student.Nickname, Course.Name, Instrument.Name, Teacher.Nickname from Student \
join Enroll on Student.StudentId = Enroll.StudentId \
join Course on Enroll.CourseId = Course.CourseId \
join Teach on Teach.CourseId = Course.CourseId \
join Teacher on Teacher.TeacherId = Teach.TeacherId \
join Instrument on Instrument.InstrumentId = Course.InstrumentId \
join Study on Study.StudentId = Enroll.StudentId \
where Teacher.TeacherId = ? order by day, time', (teacher_id,)
).fetchall()
return render_template('teacher/index.html', profile=teacher_profile, teachers=teachers)
def register():
"""Register a new teacher.
Validates that the username is not already taken. Hashes the
password for security.
"""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif db.execute(
'SELECT teacherId FROM Teacher WHERE Username = ?', (username,)
).fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
# the name is available, store it in the database and go to
# the login page
values = ()
for key in request.form:
if key == 'password':
values += (generate_password_hash(request.form[key]),)
else:
values += (request.form[key],)
db.execute(
'INSERT INTO Teacher (Username, Password, Firstname, Lastname, Nickname, Birthday, Email, TelNum) VALUES (?, ?, ?, ?, ?, ?, ?, ?)', values)
db.commit()
return redirect(url_for('teacher.login'))
flash(error)
return render_template('teacher/register.html')
def create_course():
db = get_db()
if request.method == 'POST':
values = ()
for key in request.form:
if key == 'instrument':
values += (int(
db.execute(
'SELECT InstrumentId FROM Instrument WHERE Name = ?',
(request.form[key], )).fetchone()['InstrumentId']), )
elif key == 'price' or key == 'houspertime' or key == 'numoftimes':
values += (int(request.form[key]), )
else:
values += (request.form[key], )
db.execute(
'INSERT INTO Course (Name, Price, HousPerTime, NumOfTimes, InstrumentId) VALUES (?, ?, ?, ?, ?)',
values)
db.commit()
return redirect(url_for('index'))
instruments = db.execute('SELECT Name FROM Instrument').fetchall()
return render_template('admin/course/create.html', instruments=instruments)
def index():
"""Show student schedule by query database """
student_id = session.get('student_id')
db = get_db()
error = None
student_profile = db.execute(
'select Nickname, Firstname, Lastname, Birthday, email, telnum from Student where studentid = ?',
(student_id, )).fetchone()
student = db.execute(
'select Study.Day, Study.Time, Student.Nickname, Course.Name, Instrument.Name, Teacher.Nickname from Student \
join Enroll on Student.StudentId = Enroll.StudentId \
join Course on Enroll.CourseId = Course.CourseId \
join Teach on Teach.CourseId = Course.CourseId \
join Teacher on Teacher.TeacherId = Teach.TeacherId \
join Instrument on Instrument.InstrumentId = Course.InstrumentId \
join Study on Study.StudentId = Enroll.StudentId \
where Student.StudentId = ?',
(student_id, )).fetchall()
return render_template('student/index.html',
data=student,
profile=student_profile)
def login():
"""Log in a admin by adding the admin id to the session."""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
admin = db.execute('SELECT * FROM Admin WHERE Username = ?',
(username, )).fetchone()
if admin is None:
error = 'Incorrect username.'
elif not check_password_hash(admin['Password'], password):
error = 'Incorrect password.'
if error is None:
# store the admin id in a new session and return to the index
session.clear()
session['admin_id'] = admin['AdminId']
return redirect(url_for('admin.index'))
flash(error)
return render_template('admin/login.html')
def login():
"""Log in a registered student by adding the student id to the session."""
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
student = db.execute('SELECT * FROM Student WHERE Username = ?',
(username, )).fetchone()
# print(student.fetchall())
if student is None:
error = 'Incorrect username.'
elif not check_password_hash(student['Password'], password):
error = 'Incorrect password.'
if error is None:
# store the student id in a new session and return to the index
session.clear()
session['student_id'] = student['StudentId']
return redirect(url_for('student.index'))
flash(error)
return render_template('student/login.html')
def teachers():
db = get_db()
rows = db.execute('select * from Teacher').fetchall()
return render_template('admin/teachers.html', datas=rows)
def students():
db = get_db()
rows = db.execute('select * from Student').fetchall()
return render_template('admin/students.html', datas=rows)