For importing data automatically from your own database select the Data | Import from database option from the menu.
Data on the following items can be imported from existing databases:
| • | rooms |
| • | lecturers, |
| • | programs, |
| • | subject areas, |
| • | courses and |
| • | students. |
Create an ODBC data source through which the Wise Timetable connects to the database before importing.
1. Settings
All settings for importing from a database are set in dbsettings.ini file. Example:
[connection]
import_db_conn_str = DSN=Urnik;Uid=root;Pwd=password
export_db_conn_str = DSN=Urnik;Uid=root;Pwd=mmm
[sql]
import_rooms = select r.Room_Id, r.Name, r.Seats_Num from Room r
import_tutors = select t.Tutor_Id, t.First_Name, t.Last_Name, NULL, NULL, NULL from Tutor t order by t.Last_Name, t.First_Name
import_programs = select program_id, name, code, years from Program
import_branches =
import_courses =
import_students =
[logging]
use_logger = 1
[performance]
commit_step=100
Description of parameters which are used for import:
- import_db_conn_str |
here write connection string for connection to database |
- use_logger |
if we want to log sql command 1, otherwise 0 |
- parameters from group [sql] contain sql states |
|
2. Import rooms
The following data are imported:
- Room ID |
(int, null) |
- Room name |
(char, not null) |
- Number of seats |
(int, not null) |
Data example:
Id |
Room name |
Number of seats |
1 |
R – 01 |
282 |
2 |
R – 02 |
80 |
3 |
R – 03 |
80 |
4 |
R – 04 |
36 |
Example of sql state:
select NULL, r.Name, r.Seats_Num from Room r
3. Import lecturers
The following data are imported:
- Lecturer’s ID |
(int, null) |
- Name |
(char, not null) |
- Surname |
(char, not null) |
- Code |
(char, null) |
- Web pages code |
(char, null) |
- Notes |
(char, null) |
Data example:
Id |
Name |
Surname |
Code |
Web pages code |
Notes |
1 |
Nelly |
Scott |
SN |
|
email: someone@somewhere.com |
2 |
Tom |
Rogers |
|
|
|
3 |
Nicole |
Meyers |
MN |
|
|
Example of sql state:
select t.Tutor_Id, t.First_Name, t.Last_Name, NULL, NULL, NULL from Tutor t order by t.Last_Name, t.First_Name
4. Import programs
The following data are imported:
- Program ID |
(int,null) |
- Program name |
(char, not null) |
- Program code |
(char, null) |
- Year of study |
(int, null) |
Data example:
Id |
Program name |
Code |
Year |
1 |
Business sciences |
BS |
4 |
2 |
Economics |
|
4 |
3 |
Business administration |
|
4 |
Example of sql state:
select program_id, name, code, years from Program
5. Import subject areas
The following data are imported:
- Subject area ID |
(int null) |
- Program name |
(char, not null) |
- Subject area name |
(char, not null) |
- Subject area code |
(char, null) |
Data example:
Id |
Program name |
Subject area name |
Code |
1 |
Business sciences |
Management |
MNG |
7 |
Economics |
Economic history |
EH |
10 |
Business administration |
Public sector administration |
PSA |
Example of sql state:
SELECT DISTINCT NULL, p.Name, b.Name, NULL FROM Branch b, Program p WHERE b.Program_Id = p.Program_Id
6. Import courses
The following data are imported:
- Course ID |
(int, null) |
|
- Program name |
(char, not null) |
|
- Subject area name |
(char, not null) |
|
- Year |
(int, not null) |
|
- Course name |
(char, not null) |
|
- Course type |
(char, not null) |
(e.g. tutorial, lectures) |
- Course code |
(char, null) |
|
- Lecturer’s name |
(char, not null) |
|
- Lecturer’s surname |
(char, not null) |
|
- First week of execution |
(int, null) |
(default = 1) |
- Last week of execution |
(int, null) |
(default = 52) |
- No. of hours per week |
(int, null) |
(default = 1) |
Data example:
Id |
Program name |
Subject area name |
Year |
Course name |
7 |
Business sciences |
Management |
1 |
Introduction to Business |
8 |
Business sciences |
Management |
1 |
Introduction to Organization |
8 |
Business sciences |
Management |
1 |
International Business |
10 |
Business sciences |
Management |
1 |
Business Communication |
(continuation)
Id |
Course type |
Code |
Lecturer’s name |
Lecturer’s surname |
First week |
Last week |
Hours per week |
7 |
lecture |
|
Peter |
Smith |
1 |
15 |
3 |
8 |
lecture |
|
Daryl |
Miller |
1 |
15 |
3 |
8 |
tutorial |
|
Jamie |
Cornelius |
1 |
15 |
4 |
10 |
lecture |
|
Jack |
Black |
1 |
15 |
2 |
Example of sql state:
SELECT
c.Course_Id, p.Name, b.Name, b.Year, c.Name, ct.Name, c.Code,
tut.First_Name, tut.Last_Name, 1, 15, 1
FROM
Branch b,
Course c,
Course_Branch cb,
CoursePart cp,
CourseType ct,
Program p,
Turn t,
Turn_Tutor ttut,
Tutor tut
WHERE
cb.Branch_Id = b.Branch_Id AND
cb.Course_Id = c.Course_Id AND
cp.Course_Id = c.Course_Id AND
ct.CourseType_Id = cp.CourseType_Id AND
p.Program_Id = b.Program_Id AND
t.CoursePart_Id = cp.CoursePart_Id AND
ttut.Turn_Id = t.Turn_Id AND
tut.Tutor_Id = ttut.Tutor_Id
7. Import students
The following data are imported:
ID |
(int null) |
Program name |
(char, not null) |
Subject area name |
(char, not null) |
Year |
(int, not null) |
Student’s name |
(char, not null) |
Student’s surname |
(char, not null) |
Student number |
(int, null) |
Data example:
Id |
Program name |
Subject area name |
Year |
Name |
Surname |
Student number |
1 |
Business sciences |
Management |
1 |
ABBIE |
AMMONS |
19375211 |
2 |
Business sciences |
Management |
1 |
KAYLYN |
AUMAN |
19375212 |
3 |
Business sciences |
Management |
1 |
KAROLYN |
BAILEY |
19375213 |
4 |
Business sciences |
Management |
1 |
PHYLLIDA |
BAKER |
19375214 |
Example of sql state:
SELECT
s.Student_Id, p.Name, b.Name, b.Year, s.First_Name,
s.Last_Name, s.Student_Num
FROM
Branch b,
Program p,
Student s
WHERE
p.Program_Id = b.Program_Id AND
s.Branch_Code = b.Code AND
s.Year = b.Year