Personal work hour tracking tool.
Timetracker is a personal work hour tracking tool for teachers and students who want to record and monitor the progress of a course or a project. Students can keep track of their daily work hours and update logs on several courses and projects. Teachers can create courses and monitor student specific course progress.
The application can be used either locally or in Heroku. Below can be found instructions on how view the application demo in Heroku and how to install and use the application locally or in Heroku.
Credentials:
| Password | Role | |
|---|---|---|
student@email.com |
student123 |
Student |
teacher@email.com |
teacher123 |
Teacher |
Navigate to login and use the credentials above.
Link to the installation guide
3.1.1. Student
Any person who want's to log their progress on a course or a project that exists within the tool.
3.1.2. Teacher
Any person who wants to supervise a course or a project.
With user story related SQL queries I'm indicating query parameters both with Table(param1, param2...) VALUES(?, ?) syntax as well as with :example_id depending on the query type. In addition, by default all INSERT queries also include id, date_created and date_modified parameters with corresponding values.
3.2.1. Student
"As a student I want to be able to enroll in different courses and projects. I want to be able to write detailed descriptions about what I have done during a specific task and also log the duration of that task. I want to see my progress within each course. I want be able to see the list of the latest logs I have created for a specific course and modify these logs. I also want to see the complete list of all the courses I have enrolled in."
As a student I can
-
Log in to the tool
-
Enroll/unenroll myself in a course
INSERT INTO Usercourse(user_id, course_id) VAlUES(?, ?);
- See all courses within the tool
SELECT * FROM Course;
- See all the courses I have enrolled in
SELECT * FROM Course JOIN Usercourse ON Course.id = Usercourse.course_id WHERE Usercourse.user_id = :user_id GROUP BY Course.id;
- See five courses with most recent activity (i.e. new/updated logs)
SELECT Course.id, Course.course_id, Course.title, COUNT(*) FROM Log LEFT JOIN Course ON Log.course_id = Course.id GROUP BY Course.id HAVING Log.user_id = :user_id ORDER BY Log.date_created DESC, Log.date_modified DESC LIMIT 5
- See all my course specific logs, total work hours and progress
Course specific logs:
SELECT * FROM Log WHERE Log.user_id = :user_id AND Log.course_id = :course_id;
Total work hours for a course:
SELECT SUM(duration) FROM Log WHERE Log.course_id = :course_id AND Log.user_id = :user_id;
Course progress:
SELECT Course.id, Course.course_id, Course.title, Course.duration, Course.deadline, SUM(Log.duration) as progress FROM Log LEFT JOIN Course ON Log.course_id = Course.id WHERE Log.user_id = :user_id GROUP BY Course.id;
- Create a log that contains the description of the tasks I have done today, the duration it took to complete those tasks and today's date
INSERT INTO Log(user_id, course_id, description, duration) VALUES(?, ?, ?, ?);
- Add hours to the duration of the specific day's log I have created
UPDATE Log SET duration = :duration WHERE id = :log_id;
- Update any logs I have created by modifying the description and duration
UPDATE Log SET description = :description, duration = :duration WHERE id = :log_id;
- Delete any log I have created
DELETE FROM Log WHERE id = :log_id;
3.2.2. Teacher
"As a teacher I want to be able to create courses. I want to be able see students who are enrolled in a specific course and the total number of students in each course. I also want to be able to follow the progress of an individual student within a specific course, read the logs and see the student's personal information."
As a teacher I can
-
Log in to the tool
-
Create new courses so that students can log their progress
INSERT INTO Course(user_id, course_id, title, description, duration, deadline) VALUES(?, ?, ?, ?, ?, ?)
- See all the courses I have created
SELECT * FROM Course JOIN Usercourse ON Course.id = Usercourse.course_id WHERE Usercourse.user_id = :user_id;
- See an overview on any of my courses
SELECT * FROM Course JOIN Usercourse ON Course.id = Usercourse.course_id WHERE Usercourse.user_id = :user_id AND Usercourse.course_id = :course_id;
Number of students:
SELECT COUNT(*) FROM Account LEFT JOIN Userrole ON Userrole.user_id = Account.id LEFT JOIN Role ON Role.id = Userrole.role_id LEFT JOIN Usercourse ON Usercourse.user_id = Account.id LEFT JOIN Course ON Course.id = Usercourse.course_id WHERE Course.id = :course_id AND Role.name = 'STUDENT';
- See who's participating in any course I'm responsible for
SELECT * FROM Account LEFT JOIN Userrole ON Userrole.user_id = Account.id LEFT JOIN Role ON Role.id = Userrole.role_id LEFT JOIN Usercourse ON Usercourse.user_id = Account.id LEFT JOIN Course ON Course.id = Usercourse.course_id WHERE Course.id = :course_id AND Role.name = 'STUDENT' ORDER BY Account.firstname, Account.lastname;
- See course progress and logs of a specific student within a specific course
Student and course specific logs:
SELECT * FROM Log WHERE Log.user_id = :user_id AND Log.course_id = :course_id;
Course progress:
SELECT Course.id, Course.course_id, Course.title, Course.duration, Course.deadline, SUM(Log.duration) as progress FROM Log LEFT JOIN Course ON Log.course_id = Course.id WHERE Log.user_id = :user_id GROUP BY Course.id;
- Update any of my course's details
UPDATE Course SET course_id = :course_id, title = :title, description = :description, duration = :duration, deadline = :deadline WHERE id = :course_id;
- Delete any of my courses
DELETE FROM Course WHERE id = :course_id;
Structure of the tool and features of each page for unauthorized users, and student and teacher accounts:
Unauthorized
Homepage: /
- Tool's description and navigation bar for registration and login
/auth/signup/
- Select which account type to create
/auth/signup/student/
- Create a new student account
/auth/signup/teacher/
- Create a new teacher account
/auth/login/
- Log in to the tool
Student
/courses/
- View all the courses
- Enroll/unenroll in a course
/courses/:courseId
- View course details
- Enroll/unenroll in a course
/logs/
- View all the courses the student is registered for
/:courseId/logs/
- Track progress on a specific course or project
- View all the logs and increase the duration of a log
/:courseId/logs/:logId/
- Update or delete a log
/:courseId/logs/new/
- Create a new log for a specific course
Teacher
/courses/mycourses/
- View all the courses the teacher is responsible for
/courses/new/
- Create a new course
/courses/:courseId/
- View an overview of a specific course, such as number of participants
- Delete the course
- View a list of students who participate to this course
/courses/:courseId/edit/
- Edit the course details
/:courseId/logs/:studentId/log
- View course specific logs of a student
For development: SQLite
For production: PostgreSQL
Additional information about the database
Below can be found the SQL queries for creating the database and the most important aggregate queries.
- Flask
- Jinja2
- SQLite/PostgreSQL
Front-end framework in use is Foundation by Zurb. In addition to that the application uses some custom CSS. Icons are from FontAwesome.
Colors:
Main:
#1a1a1a
Main background:
#969696
Student:
#ffb400
Teacher:
#1e0064