private static void LoadClassrooms(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from classrooms";
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var classroom = new Classroom {Name = (string) reader["name"], Address = (string) reader["address"]};
schedule.Classrooms.Add(classroom);
}
reader.Close();
}
public static void Save(Schedule schedule, string path)
{
SQLiteConnection.CreateFile(path);
var connection = new SQLiteConnection("data source=" + path);
var command = new SQLiteCommand(connection);
connection.Open();
const string dropAllTables = "select 'drop table ' || name || ';' from sqlite_master where type = 'table';";
command.CommandText = dropAllTables;
command.ExecuteNonQuery();
SaveSchedule(schedule, command);
connection.Close();
}
private static void LoadGroups(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from groups";
var reader = command.ExecuteReader();
while (reader.Read())
{
var grp = new Group
{
Name = (string) reader["name"],
Specialization = schedule.Specializations[(int) reader["spec"]],
YearOfStudy = schedule.YearsOfStudy[(int) reader["year"]]
};
schedule.Groups.Add(grp);
}
reader.Close();
}
private static void LoadLecturers(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from lecturers";
var reader = command.ExecuteReader();
while (reader.Read())
{
var lecturer = new Lecturer
{
Name = (string) reader["name"],
Degree = (string) reader["degree"],
Department = (string) reader["department"]
};
schedule.Lecturers.Add(lecturer);
}
reader.Close();
}
public TimeLineMarkup(Schedule schedule)
{
int currectRow = 0;
var days =
(from t in schedule.TimeLine
select t.Day).Distinct();
foreach (var day in days)
{
var ls =
(from t in schedule.TimeLine
where t.Day == day
select t).ToList();
int d = ls.Count();
if (d == 0) continue;
Days.Add(new TableItem<Weekdays>(day){Row = currectRow, Column = 0, RowSpan = d});
foreach (var timeInterval in ls)
{
ClassesIntervals.Add(new TableItem<ClassTime>(timeInterval){Row = currectRow, Column = 1});
currectRow++;
}
}
}
private static void SaveGroups(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table groups
(
id integer primary key autoincrement,
name text not null,
spec int not null,
year int not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var grp in schedule.Groups)
{
string insert = "insert into groups (name, spec, year) values ('" +
grp.Name + "', " +
schedule.Specializations.IndexOf(grp.Specialization) + ", " +
schedule.YearsOfStudy.IndexOf(grp.YearOfStudy) + ");";
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
private static void SaveYearsOfStudy(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table years_of_study
(
id integer primary key autoincrement,
name text not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var year in schedule.YearsOfStudy)
{
string insert = "insert into years_of_study (name) values ('" +
year.Name + "');";
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
private static void SaveTimeLine(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table time_line
(
id integer primary key autoincrement,
day int not null,
number int not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var insert in schedule.TimeLine.Select(classtime => "insert into time_line (day, number) values (" +
(int)classtime.Day + ", " +
classtime.Number + ");"))
{
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
private static void SaveSubjects(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table subjects
(
id integer primary key autoincrement,
name text not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var subject in schedule.Subjects)
{
string insert = "insert into subjects (name) values ('" +
subject.Name + "');";
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
private static void SaveSchedule(Schedule schedule, SQLiteCommand command)
{
SaveSubjects(schedule, command);
SaveSpecializations(schedule, command);
SaveYearsOfStudy(schedule, command);
SaveLecturers(schedule, command);
SaveClassrooms(schedule, command);
SaveTimeLine(schedule, command);
SaveGroups(schedule, command);
SaveRecords(schedule, command);
}
private static void SaveRecords(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table records
(
id integer primary key autoincrement,
sub int not null,
grp int not null,
lect int not null,
room int not null,
time int not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var insert in schedule.ClassRecords.Select(record => "insert into records (sub, grp, lect, room, time) values (" +
schedule.Subjects.IndexOf(record.Subject) + ", " +
schedule.Groups.IndexOf(record.Group) + ", " +
schedule.Lecturers.IndexOf(record.Lecturer) + ", " +
schedule.Classrooms.IndexOf(record.Classroom) + ", " +
schedule.TimeLine.IndexOf(record.ClassTime) + ");"))
{
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
private static void SaveLecturers(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table lecturers
(
id integer primary key autoincrement,
name text not null,
degree text not null,
department text not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var insert in schedule.Lecturers.Select(lecturer => "insert into lecturers (name, degree, department) values ('" +
lecturer.Name + "', '" +
lecturer.Degree + "', '" +
lecturer.Department + "');"))
{
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
public static void Export(Schedule schedule, string path)
{
Exporter.Export(path, schedule);
}
private static void SaveClassrooms(Schedule schedule, SQLiteCommand command)
{
const string createTable = @"
create table classrooms
(
id integer primary key autoincrement,
name text not null,
address text not null
);";
command.CommandText = createTable;
command.ExecuteNonQuery();
foreach (var insert in schedule.Classrooms.Select(classroom => "insert into classrooms (name, address) values ('" +
classroom.Name + "', '" +
classroom.Address + "');"))
{
command.CommandText = insert;
command.ExecuteNonQuery();
}
}
private static void LoadYearsOfStudy(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from years_of_study";
var reader = command.ExecuteReader();
while (reader.Read())
{
var year = new YearOfStudy {Name = (string) reader["name"]};
schedule.YearsOfStudy.Add(year);
}
reader.Close();
}
private static void LoadTimeLine(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from time_line";
var reader = command.ExecuteReader();
while (reader.Read())
{
var classtime = new ClassTime {Day = (Weekdays) reader["day"], Number = (int) reader["number"]};
schedule.TimeLine.Add(classtime);
}
reader.Close();
}
private static void LoadSubjects(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from subjects";
var reader = command.ExecuteReader();
while (reader.Read())
{
var subject = new Subject {Name = (string) reader["name"]};
schedule.Subjects.Add(subject);
}
reader.Close();
}
private static void LoadSpecializations(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from specializations";
var reader = command.ExecuteReader();
while (reader.Read())
{
var specialization = new Specialization {Name = (string) reader["name"]};
schedule.Specializations.Add(specialization);
}
reader.Close();
}
private static Schedule LoadSchedule(SQLiteCommand command)
{
var schedule = new Schedule();
LoadSubjects(schedule, command);
LoadSpecializations(schedule, command);
LoadYearsOfStudy(schedule, command);
LoadLecturers(schedule, command);
LoadClassrooms(schedule, command);
LoadTimeLine(schedule, command);
LoadGroups(schedule, command);
LoadRecords(schedule, command);
return schedule;
}
private static void LoadRecords(Schedule schedule, SQLiteCommand command)
{
command.CommandText = "select * from records";
var reader = command.ExecuteReader();
while (reader.Read())
{
var record = new ClassRecord
{
Subject = schedule.Subjects[(int) reader["sub"]],
Group = schedule.Groups[(int) reader["grp"]],
Lecturer = schedule.Lecturers[(int) reader["lect"]],
Classroom = schedule.Classrooms[(int) reader["room"]],
ClassTime = schedule.TimeLine[(int) reader["time"]]
};
schedule.ClassRecords.Add(record);
}
reader.Close();
}
private void OnNewProject()
{
var schedule = new Schedule();
schedule.InitStdTimeLine();
schedule.AddYSG();
SetNewProject(new ScheduleProject { Schedule = schedule });
}
