public DataTable ReadWithIdByClass(DataTable table, Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select Id,LessonHour From vwTimeTableWithIdByClass_tblTimeTable Where ClassId = @ClassId And DayId=@DayId", con);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
cmd.Parameters.AddWithValue("@DayId", programme.DayId);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataRow row = table.NewRow();
row["Id"] = rdr["Id"];
row["LessonHour"] = rdr["LessonHour"];
table.Rows.Add(row);
}
}
con.Close();
return(table);
}
}
public DataTable ReadFull(DataTable table, Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select Id,ClassName,ClassId,NameDay,DayId,LessonHour,HourId,SubjectName,SubjectId,RoomId,TeacherFullName,TeacherEGN From vwProgrammeFull_tblProgramme Where ClassId = @ClassId", con);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataRow row = table.NewRow();
row["Id"] = rdr["Id"];
row["ClassName"] = rdr["ClassName"];
row["ClassId"] = rdr["ClassId"];
row["NameDay"] = rdr["NameDay"];
row["DayId"] = rdr["DayId"];
row["LessonHour"] = rdr["LessonHour"];
row["HourId"] = rdr["HourId"];
row["SubjectName"] = rdr["SubjectName"];
row["SubjectId"] = rdr["SubjectId"];
row["RoomId"] = rdr["RoomId"];
row["TeacherFullName"] = rdr["TeacherFullName"];
row["TeacherEGN"] = rdr["TeacherEGN"];
table.Rows.Add(row);
}
}
con.Close();
return(table);
}
}
public int Update(Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("spUpdateProgramme_tblProgramme", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", programme.Id);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
cmd.Parameters.AddWithValue("@DayId", programme.DayId);
cmd.Parameters.AddWithValue("@HourId", programme.HourId);
cmd.Parameters.AddWithValue("@SubjectId", programme.SubjectId);
cmd.Parameters.AddWithValue("@RoomId", programme.RoomId);
cmd.Parameters.AddWithValue("@TeacherEGN", programme.TeacherEGN);
SqlParameter Result = new SqlParameter("@ResultNumber", DbType.Int32);
Result.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Result);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return((int)Result.Value);
}
}
public DataTable ReadFull(int ClassId)
{
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
DataTable table = new DataTable();
table.Columns.Add("Id");
table.Columns.Add("ClassName");
table.Columns.Add("ClassId");
table.Columns.Add("NameDay");
table.Columns.Add("DayId");
table.Columns.Add("LessonHour");
table.Columns.Add("HourId");
table.Columns.Add("SubjectName");
table.Columns.Add("SubjectId");
table.Columns.Add("RoomId");
table.Columns.Add("TeacherFullName");
table.Columns.Add("TeacherEGN");
if (programme.ClassId == 0)
{
return(crud.ReadFullWithoutClass(table));
}
else
{
return(crud.ReadFull(table, programme));
}
}
public DataTable ReadFull(int ClassId)
{
DataTable table = new DataTable();
table.Columns.Add("Id");
table.Columns.Add("LessonName");
table.Columns.Add("OnDate");
table.Columns.Add("TeacherEGN");
table.Columns.Add("MissingStudentId");
table.Columns.Add("LateStudentId");
table.Columns.Add("ProgrammeId");
table.Columns.Add("ClassName");
table.Columns.Add("ClassId");
table.Columns.Add("NameDay");
table.Columns.Add("LessonHour");
table.Columns.Add("SubjectName");
table.Columns.Add("SubjectId");
table.Columns.Add("TeacherFullName");
if (ClassId == 0)
{
return(crud.ReadFullWithoutClass(table));
}
else
{
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
return(crud.ReadFull(table, programme));
}
}
public int Create(Object.Lesson lesson, Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("spAddLesson_tblLesson", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProgrammeId", lesson.ProgrammeId);
cmd.Parameters.AddWithValue("@LessonName", lesson.LessonName);
cmd.Parameters.AddWithValue("@OnDate", lesson.OnDate);
cmd.Parameters.AddWithValue("@TeacherEGN", lesson.TeacherEGN);
cmd.Parameters.AddWithValue("@MissingStudentId", lesson.MissingStudentId);
cmd.Parameters.AddWithValue("@LateStudentId", lesson.LateStudentId);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
SqlParameter Result = new SqlParameter("@ResultNumber", DbType.Int32);
Result.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Result);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return((int)Result.Value);
}
}
public DataTable ReadTodayLesson(DataTable table, Object.Lesson lesson, Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select a.Id,LessonName from tblLesson a Left Join tblProgramme b On a.ProgrammeId = b.Id Where DateDiff(day,OnDate,@OnDate) = 0 And ClassId = @ClassId", con);
cmd.Parameters.AddWithValue("@OnDate", lesson.OnDate);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataRow row = table.NewRow();
row["Id"] = rdr["Id"];
row["LessonName"] = rdr["LessonName"];
table.Rows.Add(row);
}
}
con.Close();
return(table);
}
}
public void Delete(int Id, out string Message, out System.Drawing.Color Color)
{
Object.Programme programme = new Object.Programme();
programme.Id = Id;
int ResultNumber = crud.Delete(programme);
message.Delete(ResultNumber, out Message, out Color);
}
public int GetProgrammeId(int ClassId, int DayId, int HourId)
{
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
programme.DayId = DayId;
programme.HourId = HourId;
return(crud.GetProgrammeId(programme));
}
public void Delete(int Id, int ClassId, out string Message, out System.Drawing.Color Color)
{
Object.Lesson lesson = new Object.Lesson();
lesson.Id = Id;
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
int ResultNumber = crud.Delete(lesson, programme);
message.Delete(ResultNumber, out Message, out Color);
}
public int Delete(Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Delete From tblProgramme Where Id = @Id", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Id", programme.Id);
con.Open();
return((int)cmd.ExecuteNonQuery());
}
}
public DataTable ReadWithIdByClass(int ClassId)
{
DataTable table = new DataTable();
table.Columns.Add("Id");
table.Columns.Add("LessonHour");
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
programme.DayId = (int)DateTime.Now.DayOfWeek;
return(crud.ReadWithIdByClass(table, programme));
}
public int GetProgrammeId(Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select Id From tblProgramme Where ClassId = @ClassId And DayId = @DayId And HourId = @HourId", con);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
cmd.Parameters.AddWithValue("@DayId", programme.DayId);
cmd.Parameters.AddWithValue("@HourId", programme.HourId);
con.Open();
return((int)cmd.ExecuteScalar());
}
}
public void Create(int ClassId, int DayId, int HourId, int SubjectId, int RoomId, string TeacherEGN,
out string Message, out System.Drawing.Color Color)
{
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
programme.DayId = DayId;
programme.HourId = HourId;
programme.SubjectId = SubjectId;
programme.RoomId = RoomId;
programme.TeacherEGN = TeacherEGN;
int ResultNumber = crud.Create(programme);
message.Create(ResultNumber, out Message, out Color);
}
public DataTable ReadTodayLesson(int ClassId)
{
DataTable table = new DataTable();
table.Columns.Add("Id");
table.Columns.Add("LessonName");
Object.Lesson lesson = new Object.Lesson();
lesson.OnDate = DateTime.Now;
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
return(crud.ReadTodayLesson(table, lesson, programme));
}
public void Create(int ProgrammeId, int ClassId, string LessonName, DateTime OnDate, string TeacherEGN, string MissingStudentId
, string LateStudentId, out string Message, out System.Drawing.Color Color)
{
Object.Lesson lesson = new Object.Lesson();
lesson.ProgrammeId = ProgrammeId;
lesson.LessonName = LessonName;
lesson.OnDate = OnDate;
lesson.TeacherEGN = TeacherEGN;
lesson.MissingStudentId = MissingStudentId;
lesson.LateStudentId = LateStudentId;
Object.Programme programme = new Object.Programme();
programme.ClassId = ClassId;
int ResultNumber = crud.Create(lesson, programme);
message.Create(ResultNumber, out Message, out Color);
}
public int Delete(Object.Lesson lesson, Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("spDeleteLesson_tblLesson", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", lesson.Id);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
SqlParameter Result = new SqlParameter("@ResultNumber", DbType.Int32);
Result.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Result);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
return((int)Result.Value);
}
}
public DataTable ReadFull(DataTable table, Object.Programme programme)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select * from vwLessonFull_tblLesson Where ClassId = @ClassId", con);
cmd.Parameters.AddWithValue("@ClassId", programme.ClassId);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataRow row = table.NewRow();
row["Id"] = rdr["Id"];
row["LessonName"] = rdr["LessonName"];
row["OnDate"] = rdr["OnDate"];
row["TeacherEGN"] = rdr["TeacherEGN"];
row["MissingStudentId"] = rdr["MissingStudentId"];
row["LateStudentId"] = rdr["LateStudentId"];
row["ProgrammeId"] = rdr["ProgrammeId"];
row["ClassName"] = rdr["ClassName"];
row["ClassId"] = rdr["ClassId"];
row["NameDay"] = rdr["NameDay"];
row["LessonHour"] = rdr["LessonHour"];
row["SubjectName"] = rdr["SubjectName"];
row["SubjectId"] = rdr["SubjectId"];
row["TeacherFullName"] = rdr["TeacherFullName"];
table.Rows.Add(row);
}
}
con.Close();
return(table);
}
}