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); }