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