public List <StudentLessonsRegistration> GetDistinctLessons(int batchId, string userID)
{
List <StudentLessonsRegistration> retVal = new List <StudentLessonsRegistration>();
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT DISTINCT studentlessonsregistration.lessonid,studentlessons.title,studentlessons.`code` FROM studentlessonsregistration INNER JOIN studentlessons ON studentlessonsregistration.lessonid = studentlessons.id WHERE studentlessonsregistration.batchid = @batchId";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@batchId", batchId);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
StudentLessonsRegistration Mod = new StudentLessonsRegistration();
Mod.LessonId = dr.GetInt32(0);
Mod.xLesson = dr.GetString(1);
Mod.xLessCode = dr.GetString(2);
retVal.Add(Mod);
}
con.Close();
return(retVal);
}
public bool UpdateStudentLessonsRegistration(StudentLessonsRegistration fp, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "UPDATE `studentlessonsregistration` SET `studuserid`=@studuserid,`batchid`=@batchid,`bgroup`=@bgroup,`lessonid`=@lessonid,`subjectid`=@subjectid,`programid`=@programid,`testmark`=@testmark,`examsmark`=@examsmark,`remarks`=@remarks,`modifydate`=@modifydate WHERE id = @id";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@studuserid", fp.StuduserId);
cmd.Parameters.AddWithValue("@batchid", fp.BatchId);
cmd.Parameters.AddWithValue("@createdate", fp.DateCreted);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup);
cmd.Parameters.AddWithValue("@lessonid", fp.LessonId);
cmd.Parameters.AddWithValue("@subjectid", fp.SubjectId);
cmd.Parameters.AddWithValue("@programid", fp.ProgramId);
cmd.Parameters.AddWithValue("@testmark", fp.TestMarks);
cmd.Parameters.AddWithValue("@examsmark", fp.ExamsMarks);
cmd.Parameters.AddWithValue("@remarks", fp.Remarks);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
cmd.Parameters.AddWithValue("@id", fp.ID);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
public bool AddStudentLessonsRegistration(StudentLessonsRegistration fp, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "INSERT INTO `studentlessonsregistration`( `studuserid`, `batchid`, `bgroup`, `lessonid`, `subjectid`, `programid`, `testmark`, `examsmark`, `remarks`, `createdate`, `modifydate`) VALUES (@studregid,@studuserid,@batchid,@bgroup,@lessonid,@subjectid,@programid,@testmark,@examsmark,@remarks, @createdate, @modifydate)";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@studuserid", fp.StuduserId);
cmd.Parameters.AddWithValue("@batchid", fp.BatchId);
cmd.Parameters.AddWithValue("@createdate", fp.DateCreted);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup);
cmd.Parameters.AddWithValue("@lessonid", fp.LessonId);
cmd.Parameters.AddWithValue("@subjectid", fp.SubjectId);
cmd.Parameters.AddWithValue("@programid", fp.ProgramId);
cmd.Parameters.AddWithValue("@testmark", fp.TestMarks);
cmd.Parameters.AddWithValue("@examsmark", fp.ExamsMarks);
cmd.Parameters.AddWithValue("@remarks", fp.Remarks);
cmd.Parameters.AddWithValue("@createdate", fp.DateCreted);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
public List <StudentLessonsRegistration> GetStudentExamsTranscripts(int batchId, int gradsysId, string userID)
{
List <StudentLessonsRegistration> retVal = new List <StudentLessonsRegistration>();
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "SELECT studentlessonsregistration.id,studentlessonsregistration.studuserid,studentlessonsregistration.batchid,studentlessonsregistration.bgroup,studentlessonsregistration.lessonid,studentlessonsregistration.subjectid,studentlessonsregistration.programid,studentlessonsregistration.testmark,studentlessonsregistration.examsmark,studentlessonsregistration.remarks,studentlessonsregistration.createdate,studentlessonsregistration.modifydate,students.indexNo,students.fname,students.onames,students.sname,studentcoursesschedule.schdTitle,studentlessons.title,studentlessons.`code`,studentlessons.credit,studentprograms.moduleName,studentcourses.title FROM studentlessonsregistration INNER JOIN students ON studentlessonsregistration.studuserid = students.userId INNER JOIN studentcoursesschedule ON studentlessonsregistration.batchid = studentcoursesschedule.id INNER JOIN studentlessons ON studentlessonsregistration.lessonid = studentlessons.id INNER JOIN studentprograms ON studentlessonsregistration.programid = studentprograms.id INNER JOIN studentcourses ON studentlessonsregistration.subjectid = studentcourses.id WHERE studentlessonsregistration.batchid = @batchId";
MySqlDataReader dr = null;
MySqlCommand cmd;
con.Open();
cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@batchId", batchId);
//cmd.Parameters.AddWithValue("@studuserid", studuserid);
dr = cmd.ExecuteReader();
while (dr.Read()) //iterate through the records in the result dataset
{
StudentLessonsRegistration Mod = new StudentLessonsRegistration();
Mod.ID = dr.GetInt32(0);
Mod.StuduserId = dr.GetString(1);
Mod.BatchId = dr.GetInt32(2);
Mod.Bgroup = dr.GetString(3);
Mod.LessonId = dr.GetInt32(4);
Mod.SubjectId = dr.GetInt32(5);
Mod.ProgramId = dr.GetInt32(6);
Mod.TestMarks = dr.GetDecimal(7);
Mod.ExamsMarks = dr.GetDecimal(8);
Mod.Remarks = dr.GetString(9);
Mod.DateCreted = dr.GetDateTime(10);
Mod.LastModified = dr.GetDateTime(11);
Mod.xIndexNo = dr.GetString(12);
Mod.xFullName = dr.GetString(13) + " " + dr.GetString(14) + " " + dr.GetString(15);
Mod.xBatchTitle = dr.GetString(16);
Mod.xLesson = dr.GetString(17);
Mod.xLessCode = dr.GetString(18);
Mod.xCredit = dr.GetInt32(19);
Mod.xProgram = dr.GetString(20);
Mod.xCourse = dr.GetString(21);
Mod.xTotalMarks = dr.GetDecimal(7) + dr.GetDecimal(8);
GradingSystem gs = new GradingSystemService().GetGradingSystem(gradsysId, Mod.xTotalMarks);
Mod.xGrade = gs.GsGrade;
Mod.xGradeDesc = gs.GSDescription;
Mod.xGradepiont = gs.GsPoint;
retVal.Add(Mod);
}
con.Close();
return(retVal);
}
public bool UpdateStudentLessonsRegistrationExams(StudentLessonsRegistration fp, string userID)
{
bool result = false;
MySqlConnection con = new MySqlConnection(DbCon.connectionString);
string sqlInsert = "UPDATE `studentlessonsregistration` SET `examsmark`=@examsmark,`modifydate`=@modifydate WHERE id = @id";
con.Open();
MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
cmd.Parameters.AddWithValue("@examsmark", fp.ExamsMarks);
cmd.Parameters.AddWithValue("@modifydate", fp.LastModified);
cmd.Parameters.AddWithValue("@id", fp.ID);
if (cmd.ExecuteNonQuery() > 0)
{
result = true;
}
con.Close();
return(result);
}
