public int GetRegistrationCount(DBManager db) { if (_courseID <= 0) return -1; return db.QueryCount("SELECT count(*) FROM registration WHERE course_id="+ _courseID); }
protected String[,] LoadStudentList(DBManager db) { // Load all information Course c = new Course(); c.LoadFromDB(db); String sqlCount = "SELECT Count(*) FROM student"; String sql = "SELECT s.student_id, s.firstname, s.surname, s.nickname, s.sex, s.birthday, s.citizen_id, s.addr, s.email, s.tel, s.tel2, s.school, s.level, s.create_date, s.is_active"; String sqlWhere = " FROM student s" + " ORDER BY s.student_id"; int num = db.QueryCount(sqlCount); String[,] result = new String[num, 14]; int n = 0; OdbcDataReader reader = db.Query(sql + sqlWhere); while (reader.Read()) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); switch (name) { case "student_id": result[n, 0] = Student.GetStudentID(reader.GetInt32(i).ToString()); break; case "firstname": result[n, 1] = reader.GetString(i); break; case "surname": result[n, 1] = result[n, 1] + " " + reader.GetString(i); break; case "nickname": result[n, 2] = reader.GetString(i); break; case "sex": result[n, 3] = reader.GetString(i); break; case "birthday": result[n, 4] = reader.GetDate(i).ToString(); break; case "citizen_id": result[n, 5] = reader.GetString(i); break; case "addr": result[n, 6] = reader.GetString(i); break; case "email": result[n, 7] = reader.GetString(i); break; case "tel": result[n, 8] = "=\"" + reader.GetString(i) + "\""; break; case "tel2": result[n, 9] = "=\"" + reader.GetString(i) + "\""; break; case "school": result[n, 10] = reader.GetString(i); break; case "level": result[n, 11] = StringUtil.ConvertEducateLevel(reader.GetInt32(i)); break; case "create_date": result[n, 12] = reader.GetDate(i).ToString(); break; case "is_active": result[n, 13] = (reader.GetInt32(i) == 1 ? "ปกติ" : "ลบ"); break; } } n++; } return result; }
protected String[,] LoadStudentList(DBManager db, string courseID) { // Load all information Course c = new Course(); c.LoadFromDB(db); String sqlCount = "SELECT Count(*) "; String sql = "SELECT r.regis_date, s.firstname, s.surname, s.email, s.tel, s.school ,r.discounted_cost as discounted_cost, r.note as note, r.status as status, r.regis_id as regis_id "; String sqlWhere = " FROM student s, registration r " + " WHERE s.student_id=r.student_id AND s.is_active=1 AND r.course_id=" + courseID + " ORDER BY r.status, s.firstname, s.surname"; int num = db.QueryCount(sqlCount + sqlWhere); String[,] result = new String[num,9]; int n = 0; OdbcDataReader reader = db.Query(sql + sqlWhere); while (reader.Read()) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); switch (name) { case "regis_date": result[n, 0] = new DateTime(reader.GetDateTime(i).Ticks).ToString(); break; case "firstname": result[n,1] = reader.GetString(i); break; case "surname": result[n, 1] = result[n, 1] + " " + reader.GetString(i); break; case "school": result[n, 2] = reader.GetString(i); break; case "tel": result[n, 3] = reader.GetString(i); break; case "email": result[n, 4] = reader.GetString(i); break; case "discounted_cost": result[n, 5] = reader.GetInt32(i).ToString(); break; case "status": result[n, 6] = (reader.GetInt32(i)==0?"ปกติ":"ยกเลิก"); break; case "note": result[n, 7] = reader.GetString(i); break; case "regis_id": result[n, 8] = reader.GetInt32(i).ToString(); break; } } n++; } return result; }