public bool AddLessonTimetable(LessonTimetable insMod, string dbtableName, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "INSERT INTO `" + dbtableName + "`(`lesssonid`, `lessday`, `lesstime`, `venueroomid`, `datecreated`, `lastmodified`,classscheid,moduleid,classid,tutorid,bgroup) VALUES (@lesssonid,@lessday,@lesstime,@venueroomid,@datecreated,@lastmodified,@classscheid,@moduleid,@classid,@tutorID,@bgroup)";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@lesssonid", insMod.LessonID);
cmd.Parameters.AddWithValue("@lessday", insMod.LessDay);
cmd.Parameters.AddWithValue("@lesstime", insMod.LessTime);
cmd.Parameters.AddWithValue("@datecreated", insMod.CreatedDate);
cmd.Parameters.AddWithValue("@lastmodified", insMod.ModifyDate);
cmd.Parameters.AddWithValue("@venueroomid", insMod.VenuroomID);
cmd.Parameters.AddWithValue("@classscheid", insMod.Classscheid);
cmd.Parameters.AddWithValue("@moduleid", insMod.ModuleID);
cmd.Parameters.AddWithValue("@classid", insMod.ClassID);
cmd.Parameters.AddWithValue("@tutorID", insMod.TutorID);
cmd.Parameters.AddWithValue("@bgroup", insMod.Bgroup);
int affectedrows = cmd.ExecuteNonQuery();
if (affectedrows > 0)
{
result = true;
}
con.Close();
return(result);
}
public bool ExitTutorDayTimeGroup4th(LessonTimetable lessTime, string dbtableName, string userID)
{
bool result = false;
int rows = 0;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlDataReader dr = null;
string sqlUpdate = "SELECT COUNT(`id`) FROM `" + dbtableName + "` WHERE `lessday` = @lessday AND bgroup = @bgroup AND`lesstime` = @lesstime AND tutorid = @tutorid";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlUpdate, con);
cmd.Parameters.AddWithValue("@bgroup", lessTime.Bgroup);
cmd.Parameters.AddWithValue("@tutorid", lessTime.TutorID);
cmd.Parameters.AddWithValue("@lesstime", lessTime.LessTime);
cmd.Parameters.AddWithValue("@lessday", lessTime.LessDay);
cmd.Parameters.AddWithValue("@classscheid", lessTime.Classscheid);
cmd.Parameters.AddWithValue("@venueroomid", lessTime.VenuroomID);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
rows = dr.GetInt32(0);
}
if (rows > 0)
{
result = true;
}
con.Close();
return(result);
}
public bool UpdateLessonTimetable(LessonTimetable insMod, string dbtableName, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlUpdate = "UPDATE `" + dbtableName + "` SET `lesssonid`=@lesssonid,`lessday`=@lessday,`lesstime`=@lesstime,`venueroomid`=@venueroomid,`lastmodified`=@lastmodified,`classscheid`=@classscheid, moduleid =@moduleid, classid =@classid, tutorid =@tutorid WHERE id = @id";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlUpdate, con);
cmd.Parameters.AddWithValue("@lesssonid", insMod.LessonID);
cmd.Parameters.AddWithValue("@lessday", insMod.LessDay);
cmd.Parameters.AddWithValue("@lesstime", insMod.LessTime);
cmd.Parameters.AddWithValue("@lastmodified", insMod.ModifyDate);
cmd.Parameters.AddWithValue("@venueroomid", insMod.VenuroomID);
cmd.Parameters.AddWithValue("@classscheid", insMod.Classscheid);
cmd.Parameters.AddWithValue("@moduleid", insMod.ModuleID);
cmd.Parameters.AddWithValue("@classid", insMod.ClassID);
cmd.Parameters.AddWithValue("@tutorid", insMod.TutorID);
cmd.Parameters.AddWithValue("@id", insMod.ID);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
public bool ExitClassSheLessonDay1st(LessonTimetable lessTime, string dbtableName, string userID)
{
bool result = false;
int rows = 0;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
MySqlDataReader dr = null;
MySqlCommand cmd = null;
string sqlUpdate = "SELECT COUNT(`id`) FROM `" + dbtableName + "` WHERE `lessday` = @lessday AND classscheid = @classscheid ";
con.Open();
cmd = new MySqlCommand(sqlUpdate, con);
cmd.Parameters.AddWithValue("@lessday", lessTime.LessDay);
cmd.Parameters.AddWithValue("@classscheid", lessTime.Classscheid);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
rows = dr.GetInt32(0);
}
con.Close();
con.Open();
int numtimep = 0;
string q = "SELECT COUNT(id) FROM timeperiods";
cmd = new MySqlCommand(q, con);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
numtimep = dr.GetInt32(0);
}
if (rows >= numtimep)
{
result = true;
}
con.Close();
return(result);
}
//TUTORS TIMETABLE
public List <LessonTimetable> GetTutorLessonTimetableByClassSchedID(string tutorId, string dbtableName, int classSchedID, string userID)
{
List <LessonTimetable> listMod = new List <LessonTimetable>();
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT lessontimetable.id,lessontimetable.lesssonid,lessontimetable.lessday,lessontimetable.lesstime,lessontimetable.venueroomid,lessontimetable.datecreated,lessontimetable.lastmodified,lessontimetable.classscheid,lessontimetable.moduleid,lessontimetable.classid,studentlessons.title,venurooms.roomname,studentcoursesschedule.schdTitle,lessontimetable.tutorid,studentlessons.`code`,lessontimetable.bgroup,tutors.fname,tutors.sname FROM lessontimetable LEFT OUTER JOIN studentlessons ON lessontimetable.lesssonid = studentlessons.id LEFT OUTER JOIN venurooms ON lessontimetable.venueroomid = venurooms.id LEFT OUTER JOIN studentcoursesschedule ON lessontimetable.classscheid = studentcoursesschedule.id LEFT OUTER JOIN timedays ON lessontimetable.lessday = timedays.tday INNER JOIN tutors ON lessontimetable.tutorid = tutors.userId WHERE lessontimetable.classscheid = @classScheduleID AND lessontimetable.tutorid = @tutorId ORDER BY timedays.id,lessontimetable.lesstime ";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@classScheduleID", classSchedID);
cmd.Parameters.AddWithValue("@tutorId", tutorId);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
LessonTimetable Mod = new LessonTimetable();
Mod.ID = dr.GetInt32(0);
Mod.LessonID = dr.GetInt32(1);
Mod.LessDay = dr.GetString(2);
Mod.LessTime = dr.GetString(3);
Mod.VenuroomID = dr.GetInt32(4);
Mod.CreatedDate = dr.GetDateTime(5);
Mod.ModifyDate = dr.GetDateTime(6);
Mod.Classscheid = dr.GetInt32(7);
Mod.ModuleID = dr.GetInt32(8);
Mod.ClassID = dr.GetInt32(9);
Mod.xLessonTitle = dr.GetString(10);
Mod.xVenueName = dr.GetString(11);
Mod.xClassSche = dr.GetString(12);
Mod.TutorID = dr.GetString(13);
Mod.xLessonCode = dr.GetString(14);
Mod.Bgroup = dr.GetString(15);
Mod.xTutorName = dr.GetString(16) + " " + dr.GetString(17);
listMod.Add(Mod);
}
con.Close();
return(listMod);
}