public bool AddClassScheduler(ClassScheduler insMod, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "INSERT INTO `studentcoursesschedule`(`moduleID`, `classID`, `schdTitle`, `classSize`, `term`, `academicyear`, `createDate`, `modifyDate`,bgroup,bgsid) VALUES (@moduleID,@classID,@schdTitle,@classSize,@term,@academicyear,@createDate,@modifyDate,@bgroup,@bgsid)"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@moduleID", insMod.ModuleID); cmd.Parameters.AddWithValue("@schdTitle", insMod.Title); cmd.Parameters.AddWithValue("@classID", insMod.ClassID); cmd.Parameters.AddWithValue("@term", insMod.Term); cmd.Parameters.AddWithValue("@academicyear", insMod.AcademicYear); cmd.Parameters.AddWithValue("@createDate", insMod.CreatedDate); cmd.Parameters.AddWithValue("@modifyDate", insMod.ModifyDate); cmd.Parameters.AddWithValue("@classSize", insMod.ClassSize); cmd.Parameters.AddWithValue("@bgroup", insMod.Bgroup); cmd.Parameters.AddWithValue("@bgsid", insMod.BgsId); int affectedrows = cmd.ExecuteNonQuery(); if (affectedrows > 0) { result = true; } con.Close(); return(result); }
public bool UpdateClassScheduler(ClassScheduler upMod, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlUpdate = "UPDATE `studentcoursesschedule` SET `moduleID`=@moduleID,`classID`=@classID,`schdTitle`=@schdTitle,`classSize`=@classSize,`term`=@term,`academicyear`=@academicyear,`modifyDate`=@modifyDate WHERE id = @id"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlUpdate, con); cmd.Parameters.AddWithValue("@id", upMod.ID); cmd.Parameters.AddWithValue("@schdTitle", upMod.Title); cmd.Parameters.AddWithValue("@moduleID", upMod.ModuleID); cmd.Parameters.AddWithValue("@classID", upMod.ClassID); cmd.Parameters.AddWithValue("@classSize", upMod.ClassSize); cmd.Parameters.AddWithValue("@term", upMod.Term); cmd.Parameters.AddWithValue("@academicyear", upMod.AcademicYear); cmd.Parameters.AddWithValue("@modifyDate", upMod.ModifyDate); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }
//TUTORS BATCH SCHEDULES public List <ClassScheduler> GetTutorsBatchSchedules(string tutorId, string userID) { List <ClassScheduler> listMod = new List <ClassScheduler>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT studentcoursesschedule.id,studentcoursesschedule.schdTitle,studentcoursesschedule.classSize,studentcoursesschedule.term,studentcoursesschedule.academicyear,lessontimetable.tutorid,studentlessons.title,studentlessons.`code`,studentcoursesschedule.bgroup,lessontimetable.lesssonid FROM studentcoursesschedule LEFT OUTER JOIN lessontimetable ON studentcoursesschedule.id = lessontimetable.classscheid INNER JOIN studentlessons ON lessontimetable.lesssonid = studentlessons.id WHERE lessontimetable.tutorid =@tutorId AND studentcoursesschedule.ia = 0 AND studentcoursesschedule.active = 1 AND studentcoursesschedule.exams = 0 GROUP BY studentcoursesschedule.id, studentlessons.id"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@tutorId", tutorId); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { ClassScheduler Mod = new ClassScheduler(); Mod.ID = dr.GetInt32(0); Mod.Title = dr.GetString(1); Mod.ClassSize = dr.GetInt32(2); Mod.Term = dr.GetString(3); Mod.AcademicYear = dr.GetString(4); Mod.xTutorId = dr.GetString(5); Mod.xLesson = dr.GetString(6); Mod.xLessonCode = dr.GetString(7); Mod.Bgroup = dr.GetString(8); Mod.xLessonId = dr.GetString(9); listMod.Add(Mod); } con.Close(); return(listMod); }
public List <ClassScheduler> GetAllClassSchedulerByClassIDAvailable(int classID, string userID) { List <ClassScheduler> listMod = new List <ClassScheduler>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT studentcoursesschedule.id,studentcoursesschedule.moduleID,studentcoursesschedule.classID,studentcoursesschedule.schdTitle,studentcoursesschedule.classSize,studentcoursesschedule.term,studentcoursesschedule.academicyear,studentcoursesschedule.createDate,studentcoursesschedule.modifyDate,studentcoursesschedule.active,bgroup FROM studentcoursesschedule LEFT OUTER JOIN tbl_registration ON studentcoursesschedule.id = tbl_registration.classScheduleID WHERE (SELECT Count(tbl_registration.classScheduleID) AS numreg FROM tbl_registration WHERE tbl_registration.classScheduleID = studentcoursesschedule.id ) < studentcoursesschedule.classSize AND studentcoursesschedule.classID = @classID GROUP BY studentcoursesschedule.schdTitle, studentcoursesschedule.classSize"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@classID", classID); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { ClassScheduler Mod = new ClassScheduler(); Mod.ID = dr.GetInt32(0); Mod.ModuleID = dr.GetInt32(1); Mod.ClassID = dr.GetInt32(2); Mod.Title = dr.GetString(3); Mod.ClassSize = dr.GetInt32(4); Mod.Term = dr.GetString(5); Mod.AcademicYear = dr.GetString(6); Mod.CreatedDate = dr.GetDateTime(7); Mod.ModifyDate = dr.GetDateTime(8); Mod.Active = dr.GetInt32(9); Mod.Bgroup = dr.GetString(10); listMod.Add(Mod); } con.Close(); return(listMod); }
public List <ClassScheduler> GetAllClassSchedulerRegistration(string userID) { List <ClassScheduler> listMod = new List <ClassScheduler>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `moduleID`, `classID`, `schdTitle`, `classSize`, `term`, `academicyear`, `createDate`, `modifyDate`, `active`,bgroup FROM `studentcoursesschedule` WHERE `active` = 1 AND registration = 1 "; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { ClassScheduler Mod = new ClassScheduler(); Mod.ID = dr.GetInt32(0); Mod.ModuleID = dr.GetInt32(1); Mod.ClassID = dr.GetInt32(2); Mod.Title = dr.GetString(3); Mod.ClassSize = dr.GetInt32(4); Mod.Term = dr.GetString(5); Mod.AcademicYear = dr.GetString(6); Mod.CreatedDate = dr.GetDateTime(7); Mod.ModifyDate = dr.GetDateTime(8); Mod.Active = dr.GetInt32(9); Mod.Bgroup = dr.GetString(10); listMod.Add(Mod); } con.Close(); return(listMod); }
public string GroupClassScheduler(ClassScheduler upMod, string userID) { string results = ""; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlUpdate = "SELECT bgroup FROM `studentcoursesschedule` WHERE `term` = @term AND `academicyear` = @academicyear"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlUpdate, con); cmd.Parameters.AddWithValue("@term", upMod.Term); cmd.Parameters.AddWithValue("@academicyear", upMod.AcademicYear); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { results = dr.GetString(0); } //switch (result) //{ // case 0: // results = "ONE"; // break; // case 1: // results = "TWO"; // break; // case 2: // results = "THREE"; // break; // case 3: // results = "FOUR"; // break; // case 4: // results = "FIVE"; // break; // case 5: // results = "SIX"; // break; // case 6: // results = "SEVEN"; // break; // case 7: // results = "EIGHT"; // break; // case 8: // results = "NINE"; // break; // case 9: // results = "TEN"; // break; //} con.Close(); return(results); }
public bool UpdateClassSchedulerActivity(ClassScheduler upMod, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlUpdate = "UPDATE `studentcoursesschedule` SET `timetable`=@timetable,`ia`=@ia,`exams`=@exams,`registration`=@registration,`modifyDate`=@modifyDate WHERE id = @id"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlUpdate, con); cmd.Parameters.AddWithValue("@id", upMod.ID); cmd.Parameters.AddWithValue("@exams", upMod.Exams); cmd.Parameters.AddWithValue("@timetable", upMod.Timetable); cmd.Parameters.AddWithValue("@ia", upMod.IA); cmd.Parameters.AddWithValue("@registration", upMod.Registration); cmd.Parameters.AddWithValue("@modifyDate", upMod.ModifyDate); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }
public string NumberingClassScheduler(ClassScheduler upMod, string userID) { string results = ""; int result = 0; string bgroup = ""; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlUpdate = "SELECT COUNT(`id`) FROM `studentcoursesschedule` WHERE `classID` = @classID AND `term` = @term AND `academicyear` = @academicyear"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlUpdate, con); cmd.Parameters.AddWithValue("@classID", upMod.ClassID); cmd.Parameters.AddWithValue("@term", upMod.Term); cmd.Parameters.AddWithValue("@academicyear", upMod.AcademicYear); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { result = dr.GetInt32(0); } switch (result) { case 0: results = "ONE"; break; case 1: results = "TWO"; break; case 2: results = "THREE"; break; case 3: results = "FOUR"; break; case 4: results = "FIVE"; break; case 5: results = "SIX"; break; case 6: results = "SEVEN"; break; case 7: results = "EIGHT"; break; case 8: results = "NINE"; break; case 9: results = "TEN"; break; } con.Close(); return(results); }
public List <ClassScheduler> GetAllClassSchedulerByClassID(int classID, DateTime createDate, string bGroup, string userID) { List <ClassScheduler> listMod = new List <ClassScheduler>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `moduleID`, `classID`, `schdTitle`, `classSize`, `term`, `academicyear`, `createDate`, `modifyDate`, `active`,bgroup,`timetable`,`ia`,`exams`,`registration`,bgsid FROM `studentcoursesschedule` WHERE `active` = 1 AND classID = @classID AND createDate > @createDate AND bgroup != @bgroup"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@classID", classID); cmd.Parameters.AddWithValue("@createDate", createDate); cmd.Parameters.AddWithValue("@bgroup", bGroup); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { ClassScheduler Mod = new ClassScheduler(); Mod.ID = dr.GetInt32(0); Mod.ModuleID = dr.GetInt32(1); Mod.ClassID = dr.GetInt32(2); Mod.Title = dr.GetString(3); Mod.ClassSize = dr.GetInt32(4); Mod.Term = dr.GetString(5); Mod.AcademicYear = dr.GetString(6); Mod.CreatedDate = dr.GetDateTime(7); Mod.ModifyDate = dr.GetDateTime(8); Mod.Active = dr.GetInt32(9); Mod.Bgroup = dr.GetString(10); Mod.Timetable = dr.GetInt32(11); Mod.BgsId = dr.GetInt32(12); if (dr.GetInt32(11) == 0) { Mod.xTimetable = "NOT COMPLETED"; } else if (dr.GetInt32(11) == 1) { Mod.xTimetable = "COMPLETED"; } Mod.IA = dr.GetInt32(12); if (dr.GetInt32(11) == 0) { Mod.xIA = "NOT COMPLETED"; } else if (dr.GetInt32(12) == 1) { Mod.xIA = "COMPLETED"; } Mod.Exams = dr.GetInt32(13); if (dr.GetInt32(13) == 0) { Mod.xExams = "NOT COMPLETED"; } else if (dr.GetInt32(13) == 1) { Mod.xExams = "COMPLETED"; } Mod.Registration = dr.GetInt32(14); if (dr.GetInt32(14) == 0) { Mod.xRegistration = "NOT COMPLETED"; } else if (dr.GetInt32(14) == 1) { Mod.xRegistration = "COMPLETED"; } listMod.Add(Mod); } con.Close(); return(listMod); }
public List <ClassScheduler> GetAllClassScheduler(string userID) { List <ClassScheduler> listMod = new List <ClassScheduler>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `moduleID`, `classID`, `schdTitle`, `classSize`, `term`, `academicyear`, `createDate`, `modifyDate`, `active`,bgroup,`timetable`,`ia`,`exams`,`registration`,bgsid,gstype FROM `studentcoursesschedule` INNER JOIN gradesystem ON studentcoursesschedule.bgsid = gradesystem.gsid WHERE `active` = 1"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { ClassScheduler Mod = new ClassScheduler(); Mod.ID = dr.GetInt32(0); Mod.ModuleID = dr.GetInt32(1); Mod.ClassID = dr.GetInt32(2); Mod.Title = dr.GetString(3); Mod.ClassSize = dr.GetInt32(4); Mod.Term = dr.GetString(5); Mod.AcademicYear = dr.GetString(6); Mod.CreatedDate = dr.GetDateTime(7); Mod.ModifyDate = dr.GetDateTime(8); Mod.Active = dr.GetInt32(9); Mod.Bgroup = dr.GetString(10); Mod.Timetable = dr.GetInt32(11); Mod.IA = dr.GetInt32(12); Mod.Exams = dr.GetInt32(13); Mod.Registration = dr.GetInt32(14); Mod.BgsId = dr.GetInt32(15); Mod.xBgsid = dr.GetString(16); if (dr.GetInt32(11) == 0) { Mod.xTimetable = "Not Completed"; } else if (dr.GetInt32(11) == 1) { Mod.xTimetable = "Completed"; } Mod.IA = dr.GetInt32(12); if (dr.GetInt32(11) == 0) { Mod.xIA = "Not Completed"; } else if (dr.GetInt32(12) == 1) { Mod.xIA = "Completed"; } Mod.Exams = dr.GetInt32(13); if (dr.GetInt32(13) == 0) { Mod.xExams = "Not Completed"; } else if (dr.GetInt32(13) == 1) { Mod.xExams = "Completed"; } Mod.Registration = dr.GetInt32(14); if (dr.GetInt32(14) == 0) { Mod.xRegistration = "Not Completed"; } else if (dr.GetInt32(14) == 1) { Mod.xRegistration = "Completed"; } listMod.Add(Mod); } con.Close(); return(listMod); }