List <CourseTableUniversity> GetAllRecords()
{
DataTable dt1 = DataAccess.GetDataBySql(string.Format(@"select distinct c.course_code, c.course_name from Course c, CourseDetail cd where c.course_code = cd.course_code and c.department = '{0}' and cd.term_id = {1}", currentDep, termID));
List <CourseTableUniversity> list = new List <CourseTableUniversity>();
foreach (DataRow dr in dt1.Rows)
{
CourseTableUniversity record = new CourseTableUniversity();
record.CourseCode = dr["course_code"].ToString();
record.CourseName = dr["course_name"].ToString();
record.Teachers = new List <string>();
record.CouresDetailGroup = new List <CourseDetail>();
DataTable dtTeacher = DataAccess.GetDataBySql(string.Format("select distinct l.short_name from Lecturer l, Course c, CourseDetail cd where l.short_name = cd.lecturer_shortname and cd.course_code = c.course_code and c.department = '{0}' and cd.term_id = {1} and cd.course_code = '{2}'", currentDep, termID, record.CourseCode));
foreach (DataRow dr2 in dtTeacher.Rows)
{
record.Teachers.Add(dr2["short_name"].ToString());
}
DataTable dtCd = DataAccess.GetDataBySql(string.Format("select distinct cd.id, l.short_name, cd.class, COUNT(cs.student_code) as student_num from Lecturer l, Course c, CourseDetail cd, CourseStudent cs where l.short_name = cd.lecturer_shortname and cd.course_code = c.course_code and cd.id = cs.course_detail_id and c.department = '{0}' and cd.term_id = {1} and cd.course_code = '{2}' group by cd.id, l.short_name, cd.class", currentDep, termID, record.CourseCode));
foreach (DataRow dr2 in dtCd.Rows)
{
CourseDetail courseDe = new CourseDetail();
courseDe.CdID = Convert.ToInt32(dr2["id"]);
courseDe.LecturerShort = dr2["short_name"].ToString();
courseDe.Classname = dr2["class"].ToString();
courseDe.StudentNum = Convert.ToInt32(dr2["student_num"]);
record.CouresDetailGroup.Add(courseDe);
}
list.Add(record);
}
return(list);
}
protected void Page_Load(object sender, EventArgs e)
{
IsUserLogedIn();
onlineStudent = (Student)Session["onlineStudent"];
if (!IsPostBack)
{
btnStudent.Text = onlineStudent.StudentShortName.ToLower();
lbStudent.Text = string.Format("{0} ({1})", onlineStudent.StudentShortName, onlineStudent.StudentName);
termID = Request.QueryString["term"];
if (termID == null)
{
termID = DataAccess.GetDataBySql("Select top 1 * from Term order by end_date desc").Rows[0]["term_id"].ToString();
}
string cdaID = Request.QueryString["course"];
if (cdaID == null)
{
cda = GetCourseDetailByTermIDFirst(Convert.ToInt32(termID));
}
else
{
cda = GetCourseDetailByTermIDAndCourseDetaiID(Convert.ToInt32(termID), Convert.ToInt32(cdaID));
}
LoadRpTerm();
LoadRpCourse();
LoadRpAttendance();
}
}
List <CourseDetail> GetCourseDetailByTermID(int termID)
{
DataTable dt = DataAccess.GetDataBySql(string.Format("select distinct cd.*,t.start_date,t.end_date,c.course_name from CourseDetail cd, Term t, Course c, CourseStudent cs where cd.term_id = {0} and cd.term_id = t.term_id and cd.course_code = c.course_code and cs.course_detail_id = cd.id and cs.student_code like '{1}'", termID, onlineStudent.StudentCode));
List <CourseDetail> list = new List <CourseDetail>();
foreach (DataRow dr in dt.Rows)
{
CourseDetail cdat = new CourseDetail();
cdat.CdID = Convert.ToInt32(dr["id"]);
cdat.CourseCode = dr["course_code"].ToString();
cdat.LecturerShort = dr["lecturer_shortname"].ToString();
cdat.Classname = dr["class"].ToString();
cdat.CourseName = dr["course_name"].ToString();
cdat.StartDate = Convert.ToDateTime(dr["start_date"]);
cdat.EndDate = Convert.ToDateTime(dr["end_date"]);
list.Add(cdat);
}
return(list);
}