/// <summary>
/// Return een lijst van Lectures op basis van userId
/// </summary>
public List <Models.BU.Lecture> GetLecturesOfTeacher(string query, int userId)
{
Connect();
try
{
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List <Models.BU.Lecture> lectureList = new List <Models.BU.Lecture>();
Models.BU.Teacher tempTeacher = GetSingleTeacher("SELECT * from UserAccount WHERE UserId = '" + userId + "'");
while (reader.Read())
{
Models.BU.Lecture tempLecture = new Models.BU.Lecture(tempTeacher, GetSingleModule("SELECT * FROM Module WHERE Code = '" + reader["ModuleCode"].ToString() + "'"), reader["Classroom"].ToString(), reader["StudentGroup"].ToString(), Convert.ToInt32(reader["Period"]), Convert.ToInt32(reader["Week"]), Convert.ToInt32(reader["Day"]), Convert.ToInt32(reader["StartHour"]), Convert.ToInt32(reader["StartMinute"]), Convert.ToInt32(reader["EndHour"]), Convert.ToInt32(reader["EndMinute"]));
lectureList.Add(tempLecture);
}
Debug.WriteLine("GetLecturesOfTeacher - Succes.");
return(lectureList);
}
catch (Exception)
{
Debug.WriteLine("GetLecturesOfTeacher - Exception!");
return(null);
}
}
/// <summary>
/// Maak een string dat past in een Cell van MainGridView uit een Lecture.
/// </summary>
public string ConstructScheduleString(Models.BU.Lecture lecture)
{
// string in MainGridView is altijd
// [ DAG: START - EIND. ]
// [ VAK, GROEP, LOKAAL.]
return(string.Format("{0}: {1} - {2}. {3}, {4}, {5}.",
DayString(lecture.day), TimeString(lecture.startHour, lecture.startMinute), TimeString(lecture.endHour, lecture.endMinute), lecture.module.moduleCode, lecture.studentGroup, lecture.classroom));
}
public int CheckIfLectureAlreadyExists(Models.BU.Lecture lecture)
{
Models.Database db = new Models.Database();
string query = "SELECT LectureId FROM Lecture WHERE TeacherId = '" + lecture.teacher.UserID + "' AND Period = '" + lecture.period + "' AND Week = '" + lecture.week + "'" +
"AND Day = '" + lecture.day + "' AND StartHour = '" + lecture.startHour + "' AND StartMinute = '" + lecture.startMinute + "'" +
"AND EndHour = '" + lecture.endHour + "' AND EndMinute = '" + lecture.endMinute + "'";
return(db.CheckIfLectureAlreadyExists(query));
}
public void CreateEntry(Models.BU.Lecture lecture)
{
Models.Database db = new Models.Database();
string query = "INSERT INTO Lecture(TeacherId, ModuleCode, Classroom, StudentGroup, Period, Week, Day, StartHour, StartMinute, EndHour, EndMinute) " +
"VALUES ('" + lecture.teacher.UserID + "', '" + lecture.module.moduleCode + "', '" + lecture.classroom + "', '" + lecture.studentGroup + "', '" + lecture.period + "', " +
"'" + lecture.week + "', '" + lecture.day + "', '" + lecture.startHour + "', '" + lecture.startMinute + "', '" + lecture.endHour + "', '" + lecture.endMinute + "')";
db.CreateEntry(query);
}
public void UpdateEntry(Models.BU.Lecture lecture, int lectureId)
{
Models.Database db = new Models.Database();
string query = "UPDATE Lecture SET TeacherId = '" + lecture.teacher.UserID + "', ModuleCode = '" + lecture.module.moduleCode + "', Classroom = '" + lecture.classroom +
"', StudentGroup = '" + lecture.studentGroup + "', Day = '" + lecture.day + "', StartHour = '" + lecture.startHour +
"', StartMinute = '" + lecture.startMinute + "', EndHour = '" + lecture.endHour + "', EndMinute = '" + lecture.endMinute + "'" +
"WHERE LectureId = '" + lectureId + "'";
db.UpdateEntry(query);
}
/// <summary>
/// Bepaal in welke Cell Lecture kan worden geplaatst in MainGridView.
/// </summary>
public int[] DetermineCell(Models.BU.Lecture lecture)
{
int cellRow = -1; // default waarde, buiten de tabel
int cellColumn = lecture.day; // waarde is dagnummer van Lecture
// Bepaal cellRow
switch (lecture.startHour)
{
case 9:
cellRow = 0;
break;
case 10:
cellRow = 2;
break;
case 11:
cellRow = 4;
break;
case 12:
cellRow = 6;
break;
case 13:
cellRow = 8;
break;
case 14:
cellRow = 10;
break;
case 15:
cellRow = 12;
break;
case 16:
cellRow = 14;
break;
case 17:
cellRow = 16;
break;
case 18:
cellRow = 18;
break;
}
if (lecture.startHour != 18 && lecture.startMinute == 30)
{
cellRow += 1;
}
return(new int[] { cellRow, cellColumn });
}
/// <summary>
/// Sla gewijzigde data op in de DB en refresh alle data.
/// </summary>
private void EditData(Models.BU.Lecture lecture)
{
Models.CC.Scheduler_GetData get = new Models.CC.Scheduler_GetData();
int existingLectureId = get.CheckIfLectureAlreadyExists((Models.BU.Lecture)Session["Database_SelectedLectureData"]);
if (existingLectureId != -1)
{
Models.CC.Scheduler_UpdateEntry update = new Models.CC.Scheduler_UpdateEntry();
update.UpdateEntry(lecture, existingLectureId);
}
// Refresh
Session["Database_Changed"] = true;
ImportData();
}
/// <summary>
/// Update een Lecture in de DB.
/// </summary>
private void UpdateLecture()
{
Models.CC.Scheduler_GetData get = new Models.CC.Scheduler_GetData();
// Maak een object om te vullen.
Models.BU.Lecture updatedLecture = new Models.BU.Lecture(
get.GetSingleTeacher(Convert.ToInt32(UserDropdownList.SelectedValue)), // Teacher
get.GetSingleModule(Convert.ToInt32(ModuleDropdownList_E.SelectedValue)), // Module
ClassroomTextBox_E.Text, // Classroom
StudentGroupTextBox_E.Text, // StudentGroup
Convert.ToInt32(PeriodDropdownList.SelectedValue), // Period
Convert.ToInt32(WeekDropdownList.SelectedValue), // Week
DayInt(DayDropdownList_E.SelectedValue), // Day
Convert.ToInt32(TimeStartHourTextBox_E.Text), // StartHour
Convert.ToInt32(TimeStartMinuteTextBox_E.Text), // StartMinute
Convert.ToInt32(TimeEndHourTextBox_E.Text), // EndHour
Convert.ToInt32(TimeEndMinuteTextBox_E.Text)); // EndMinute
// Opslaan
EditData(updatedLecture);
}
/// <summary>
/// Sla nieuwe data op in de DB en refresh alle data.
/// </summary>
private void SaveData(Models.BU.Lecture lecture)
{
Models.CC.Scheduler_GetData get = new Models.CC.Scheduler_GetData();
int existingLectureId = get.CheckIfLectureAlreadyExists(lecture);
if (existingLectureId == -1)
{
Models.CC.Scheduler_CreateEntry create = new Models.CC.Scheduler_CreateEntry();
create.CreateEntry(lecture);
}
else
{
Models.CC.Scheduler_UpdateEntry update = new Models.CC.Scheduler_UpdateEntry();
update.UpdateEntry(lecture, existingLectureId);
}
// Refresh
Session["Database_Changed"] = true;
ImportData();
}
/// <summary>
/// Vul de edit textboxes e.d. met data uit EditGridView.
/// </summary>
private void FillEditElements()
{
// Leeg eerst alles
ModuleDropdownList_E.SelectedValue = null;
ClassroomTextBox_E.Text = "";
StudentGroupTextBox_E.Text = "";
DayDropdownList_E.SelectedValue = null;
TimeStartHourTextBox_E.Text = "";
TimeStartMinuteTextBox_E.Text = "";
TimeEndHourTextBox_E.Text = "";
TimeEndMinuteTextBox_E.Text = "";
if (LectureIdDropdownList != null)
{
if (EditGridView != null)
{
foreach (GridViewRow row in EditGridView.Rows)
{
// Check of de LectureId overeenkomt
if (row.Cells[0].Text == LectureIdDropdownList.SelectedValue.ToString())
{
Models.CC.Scheduler_GetData get = new Models.CC.Scheduler_GetData();
int selectedModuleId = get.GetModuleByModuleCode(row.Cells[2].Text).moduleId;
ModuleDropdownList_E.SelectedValue = selectedModuleId.ToString();
ClassroomTextBox_E.Text = row.Cells[3].Text;
StudentGroupTextBox_E.Text = row.Cells[4].Text;
DayDropdownList_E.SelectedValue = DayString(Convert.ToInt32(row.Cells[7].Text));
TimeStartHourTextBox_E.Text = row.Cells[8].Text;
TimeStartMinuteTextBox_E.Text = row.Cells[9].Text;
TimeEndHourTextBox_E.Text = row.Cells[10].Text;
TimeEndMinuteTextBox_E.Text = row.Cells[11].Text;
// Sla de oude data op naar oldLecture
Session["Database_SelectedLectureData"] = new Models.BU.Lecture(get.GetSingleTeacher(Convert.ToInt32(UserDropdownList.SelectedValue)), get.GetSingleModule(Convert.ToInt32(ModuleDropdownList_E.SelectedValue)), ClassroomTextBox_E.Text, StudentGroupTextBox_E.Text, Convert.ToInt32(PeriodDropdownList.SelectedValue), Convert.ToInt32(WeekDropdownList.SelectedValue), DayInt(DayDropdownList_E.SelectedValue), Convert.ToInt32(TimeStartHourTextBox_E.Text), Convert.ToInt32(TimeStartMinuteTextBox_E.Text), Convert.ToInt32(TimeEndHourTextBox_E.Text), Convert.ToInt32(TimeEndMinuteTextBox_E.Text));
}
}
}
}
}
/// <summary>
/// Maak een entry aan in de database.
/// </summary>
public void CreateEntry(Models.BU.Lecture lecture)
{
Models.BU.Scheduler s = new Models.BU.Scheduler();
s.CreateEntry(lecture);
}
/// <summary>
/// Update een entry in de database.
/// </summary>
public void UpdateEntry(Models.BU.Lecture lecture, int lectureId)
{
Models.BU.Scheduler s = new Models.BU.Scheduler();
s.UpdateEntry(lecture, lectureId);
}
public int CheckIfLectureAlreadyExists(Models.BU.Lecture lecture)
{
return(s.CheckIfLectureAlreadyExists(lecture));
}
