public static List <string> GetStudyYears(int teacherID, int gradeNo, int yearEndMonth)
{
SchoolJournalEntities context = new SchoolJournalEntities();
HashSet <string> studyYears = new HashSet <string>();
var records = from j in context.attendanceJournal
where j.TeacherID == teacherID && j.Grade == gradeNo
select j;
var dates = (from rec in records select rec.Date).Distinct();
foreach (var date in dates)
{
//semester begining - 01.09; semester ending - 31.07;
if (date.Month < yearEndMonth) //period from New Year to summer - semester beginning - the previous year
{
string studyYear = string.Format("{0}-{1}", date.Year - 1, date.Year);
studyYears.Add(studyYear);
}
else
{
string studyYear = string.Format("{0}-{1}", date.Year, date.Year + 1);
studyYears.Add(studyYear);
}
}
context.Dispose();
return(studyYears.ToList());
}
public static DataTable BuildDataTable(int teacherID, int gradeNo, string section, string subjectTitle,
DateTime studyYearStart, DateTime studyYearEnd)
{
SchoolJournalEntities context = new SchoolJournalEntities();
DataTable dt = new DataTable();
var records = from j in context.attendanceJournal
where j.TeacherID == teacherID && j.Grade == gradeNo && j.Section == section &&
j.Date <= studyYearEnd && j.Date >= studyYearStart &&
j.Subject.Equals(subjectTitle)
select j;
var dates = (from rec in records select rec.Date).Distinct();
dt.Columns.Add(new DataColumn("ID", typeof(int)));
dt.Columns.Add(new DataColumn("Last Name, First Name", typeof(string)));
dt.PrimaryKey = new DataColumn[] { dt.Columns[0] };
foreach (var date in dates)
{
//string dateString = date.ToString("dd.MM.yy");
string dateString = string.Format("{0}\n{1}", date.ToString("dd.MM.yy"), date.ToShortTimeString());
if (dt.Columns.Contains(dateString))
{
continue;
}
DataColumn dc = new DataColumn(dateString, typeof(string));
dt.Columns.Add(dc);
}
foreach (var rec in records)
{
DataRow dr;
string nameSurname = string.Format("{0} {1}", rec.Last_Name, rec.First_Name);
if (!dt.Rows.Contains(rec.StudentID))
{
dr = dt.NewRow();
dr[0] = rec.StudentID;
dr[1] = string.Format("{0} {1}", rec.Last_Name, rec.First_Name);
}
else
{
dr = dt.Rows.Find(rec.StudentID);
}
string dateString = string.Format("{0}\n{1}", rec.Date.ToString("dd.MM.yy"), rec.Date.ToShortTimeString());
dr[dateString] = rec.Mark == null ? (rec.IsPresent.GetValueOrDefault() ? "." : "a") : rec.Mark.ToString();
if (!dt.Rows.Contains(rec.StudentID))
{
dt.Rows.Add(dr);
}
}
context.Dispose();
return(dt);
}
public static DataTable BuildDataTable(int teacherID, int gradeNo, string section, string subjectTitle,
DateTime studyYearStart, DateTime studyYearEnd)
{
SchoolJournalEntities context = new SchoolJournalEntities();
DataTable dt = new DataTable();
var records = from j in context.attendanceJournal
where j.TeacherID == teacherID && j.Grade == gradeNo && j.Section==section
&& j.Date <= studyYearEnd && j.Date >= studyYearStart
&& j.Subject.Equals(subjectTitle)
select j;
var dates = (from rec in records select rec.Date).Distinct();
dt.Columns.Add(new DataColumn("ID", typeof(int)));
dt.Columns.Add(new DataColumn("Last Name, First Name", typeof(string)));
dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};
foreach (var date in dates)
{
//string dateString = date.ToString("dd.MM.yy");
string dateString = string.Format("{0}\n{1}", date.ToString("dd.MM.yy"), date.ToShortTimeString());
if (dt.Columns.Contains(dateString))
continue;
DataColumn dc = new DataColumn(dateString, typeof(string));
dt.Columns.Add(dc);
}
foreach (var rec in records)
{
DataRow dr;
string nameSurname = string.Format("{0} {1}", rec.Last_Name, rec.First_Name);
if (!dt.Rows.Contains(rec.StudentID))
{
dr = dt.NewRow();
dr[0] = rec.StudentID;
dr[1] = string.Format("{0} {1}", rec.Last_Name, rec.First_Name);
}
else dr = dt.Rows.Find(rec.StudentID);
string dateString = string.Format("{0}\n{1}", rec.Date.ToString("dd.MM.yy"), rec.Date.ToShortTimeString());
dr[dateString] = rec.Mark == null ? (rec.IsPresent.GetValueOrDefault() ? "." : "a") : rec.Mark.ToString();
if(!dt.Rows.Contains(rec.StudentID))
dt.Rows.Add(dr);
}
context.Dispose();
return dt;
}
public static List<string> GetStudyYears(int teacherID, int gradeNo, int yearEndMonth)
{
SchoolJournalEntities context = new SchoolJournalEntities();
HashSet<string> studyYears = new HashSet<string>();
var records = from j in context.attendanceJournal
where j.TeacherID == teacherID && j.Grade == gradeNo
select j;
var dates = (from rec in records select rec.Date).Distinct();
foreach (var date in dates)
{
//semester begining - 01.09; semester ending - 31.07;
if (date.Month < yearEndMonth) //period from New Year to summer - semester beginning - the previous year
{
string studyYear = string.Format("{0}-{1}", date.Year - 1, date.Year);
studyYears.Add(studyYear);
}
else
{
string studyYear = string.Format("{0}-{1}", date.Year, date.Year+1);
studyYears.Add(studyYear);
}
}
context.Dispose();
return studyYears.ToList();
}
private void toolStripButtonSaveChanges_Click(object sender, EventArgs e)
{
SchoolJournalEntities context = null;
try
{
context = new SchoolJournalEntities();
var teacherIDs = from teacher in context.Teachers select teacher.TeacherID;
foreach (DataGridViewRow r in teachersListDataGridView.Rows)
{
if (r.IsNewRow)
continue;
int teacherID = -1;
try
{
teacherID = int.Parse(teachersListDataGridView["teacherID", r.Index].Value.ToString());
}
catch (ArgumentException)
{
MessageBox.Show(
string.Format("Incorrect teacherID in row {0}!\nMaybe you've created empty rows?",r.Index),
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
if (teacherIDs.Contains(teacherID)) //update record
{
DateTime? dateOfBirth;
try
{
dateOfBirth = DateTime.Parse(teachersListDataGridView["dateOfBirth", r.Index].Value.ToString());
}
catch
{
dateOfBirth = null;
}
TeacherDAL.UpdateTeacher(new TeacherInfo(
teacherID,
teachersListDataGridView["firstName", r.Index].Value.ToString(),
teachersListDataGridView["lastName", r.Index].Value.ToString(),
teachersListDataGridView["patronymic", r.Index].Value.ToString(),
dateOfBirth,
teachersListDataGridView["email", r.Index].Value.ToString(),
teachersListDataGridView["password", r.Index].Value.ToString(),
teachersListDataGridView["phone", r.Index].Value.ToString(),
teachersListDataGridView["specialization", r.Index].Value.ToString(),
teachersListDataGridView["category", r.Index].Value.ToString())
);
}
else //add new record
{
DateTime? dateOfBirth;
try
{
dateOfBirth = DateTime.Parse(teachersListDataGridView["dateOfBirth", r.Index].Value.ToString());
}
catch
{
dateOfBirth = null;
}
teachersListDataGridView["teacherID", r.Index].Value =
TeacherDAL.AddNewTeacher(new TeacherInfo(
-1, //заглушка
teachersListDataGridView["firstName", r.Index].Value.ToString(),
teachersListDataGridView["lastName", r.Index].Value.ToString(),
teachersListDataGridView["patronymic", r.Index].Value.ToString(),
dateOfBirth,
teachersListDataGridView["email", r.Index].Value.ToString(),
teachersListDataGridView["password", r.Index].Value.ToString(),
teachersListDataGridView["phone", r.Index].Value.ToString(),
teachersListDataGridView["specialization", r.Index].Value.ToString(),
teachersListDataGridView["category", r.Index].Value.ToString())
);
}
}
this.toolStripSavedStatus.Text = "Changes saved!";
MessageBox.Show("Changes successfully changed!", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
string innerException = string.Empty;
if (ex.InnerException != null)
innerException = ex.InnerException.Message;
MessageBox.Show(ex.Message+"\n"+innerException, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if(context!=null)
context.Dispose();
}
}
private void saveGroups()
{
SchoolJournalEntities context = new SchoolJournalEntities();
var groupIDs = from g in context.Groups select g.GroupID;
try
{
foreach (DataGridViewRow r in GroupsDataGridView.Rows)
{
if (r.IsNewRow)
continue;
int groupID = int.Parse(GroupsDataGridView["GroupID", r.Index].Value.ToString());
if (groupIDs.Contains(groupID)) //update
{
GroupDAL.UpdateGroup(groupID,
int.Parse(GroupsDataGridView["TeacherID", r.Index].Value.ToString()),
GroupsDataGridView["Subject", r.Index].EditedFormattedValue.ToString(),
GroupsDataGridView["Grade", r.Index].EditedFormattedValue.ToString(),
GroupsDataGridView["Description", r.Index].Value.ToString());
}
else //add new
{
GroupsDataGridView["GroupID", r.Index].Value =
GroupDAL.NewGroup(int.Parse(GroupsDataGridView["TeacherID", r.Index].Value.ToString()),
GroupsDataGridView["Subject", r.Index].EditedFormattedValue.ToString(),
GroupsDataGridView["Grade", r.Index].EditedFormattedValue.ToString(),
GroupsDataGridView["Description", r.Index].Value.ToString());
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
context.Dispose();
}
}
