public static int GetMaxTransRegisID(DBManager db, int branchId)
{
OdbcDataReader reader = db.Query("SELECT MAX(transaction_id) from registration where branch_id="+branchId);
if (reader.Read())
{
if (reader.IsDBNull(0)) return 1;
int maxID = (int)reader.GetInt64(0);
return maxID;
}
return 0;
}
public static Role[] LoadListFromDB(DBManager db, string sqlCriteria)
{
OdbcDataReader reader = db.Query("SELECT * FROM role " + sqlCriteria);
LinkedList<Role> list = new LinkedList<Role>();
while (reader.Read())
{
list.AddLast(Role.CreateForm(reader));
}
Role[] entities = new Role[list.Count];
int i = 0;
foreach (Role t in list)
{
entities[i++] = t;
}
return entities;
}
public PaidGroup[] LoadAssosicatedPaidGroup(DBManager db)
{
if (this._teacherID == 0) return null;
String subQuery = "SELECT paid_group_id FROM paid_group_teacher_mapping WHERE teacher_id='" + this._teacherID + "'";
String sql = "SELECT * FROM paid_group WHERE paid_group_id IN ( " + subQuery + " )";
OdbcDataReader reader = db.Query(sql);
LinkedList<PaidGroup> list = new LinkedList<PaidGroup>();
while (reader.Read())
{
list.AddLast(PaidGroup.CreateForm(reader));
}
PaidGroup[] entities = new PaidGroup[list.Count];
int i = 0;
foreach (PaidGroup pg in list)
{
entities[i++] = pg;
}
return entities;
}
public static Teacher[] LoadListFromDB(DBManager db, string sqlCriteria)
{
OdbcDataReader reader = db.Query("SELECT * FROM teacher " + sqlCriteria);
LinkedList<Teacher> list = new LinkedList<Teacher>();
while (reader.Read())
{
list.AddLast(Teacher.CreateForm(reader));
}
Teacher[] entities = new Teacher[list.Count];
int i = 0;
foreach (Teacher t in list)
{
entities[i++] = t;
}
return entities;
}
public int GetRegistrationCountThisMonth(DBManager db)
{
String thisMonth = DateTime.Today.ToString("yyyy-MM", new System.Globalization.CultureInfo("en-US")) + "-01";
String sql = "select count(*) as cnt from ( SELECT distinct transaction_id FROM registration WHERE username='******' and regis_date >= '" + thisMonth + "') as t1";
OdbcDataReader reader = db.Query(sql);
if (reader.Read())
{
return reader.GetInt32(0);
}
return 0;
}
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;
}
public static Branch[] LoadListFromDBCustom(DBManager db, string sqlAll)
{
OdbcDataReader reader = db.Query(sqlAll);
LinkedList<Branch> list = new LinkedList<Branch>();
while (reader.Read())
{
list.AddLast(Branch.CreateForm(reader));
}
Branch[] entities = new Branch[list.Count];
int i = 0;
foreach (Branch b in list)
{
entities[i++] = b;
}
return entities;
}
public override bool LoadFromDB(DBManager db, string sqlCriteria)
{
OdbcDataReader reader = db.Query("SELECT * FROM registration WHERE " + sqlCriteria);
if (!reader.Read()) return false;
return Registration.CreateForm(reader, this);
}
public bool LoadFromDB(DBManager db)
{
if (this._paidGroupID == 0) return false;
OdbcDataReader reader = db.Query("SELECT * FROM paid_group WHERE paid_group_id=" + this._paidGroupID);
if (!reader.Read()) return false;
return PaidGroup.CreateForm(reader, this);
}
public static Room[] LoadListFromDBCustom(DBManager db, string sqlAll)
{
OdbcDataReader reader = db.Query(sqlAll);
LinkedList<Room> list = new LinkedList<Room>();
while (reader.Read())
{
list.AddLast(Room.CreateForm(reader));
}
Room[] entities = new Room[list.Count];
int i = 0;
foreach (Room r in list)
{
entities[i++] = r;
}
return entities;
}
public Branch LoadBranchInfo(DBManager db)
{
if (_courseID <= 0) return null;
String sql = "SELECT b.* FROM room r, branch b WHERE r.branch_id=b.branch_id AND r.room_id=" + this._roomID;
OdbcDataReader reader = db.Query(sql);
if (reader.Read()) {
return Branch.CreateForm(reader);
}
return null;
}
public static Course[] LoadListFromDBCustom(DBManager db, string sqlAll)
{
OdbcDataReader reader = db.Query(sqlAll);
LinkedList<Course> list = new LinkedList<Course>();
while (reader.Read())
{
list.AddLast(Course.CreateForm(reader));
}
Course[] entities = new Course[list.Count];
int i = 0;
foreach (Course t in list)
{
entities[i++] = t;
}
return entities;
}
public static int GetMaxCourseID(DBManager db)
{
OdbcDataReader reader = db.Query("SELECT MAX(course_id) from course");
if (reader.Read())
{
return reader.GetInt32(0);
}
return -1;
}
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;
}
public static PaidGroup[] LoadListFromDBCustom(DBManager db, string sqlAll)
{
OdbcDataReader reader = db.Query(sqlAll);
LinkedList<PaidGroup> list = new LinkedList<PaidGroup>();
while (reader.Read())
{
list.AddLast(PaidGroup.CreateForm(reader));
}
PaidGroup[] entities = new PaidGroup[list.Count];
int i = 0;
foreach (PaidGroup r in list)
{
entities[i++] = r;
}
return entities;
}
public override bool LoadFromDB(DBManager db, string sqlCriteria)
{
OdbcDataReader reader = db.Query("SELECT * FROM paid_group WHERE " + sqlCriteria);
if (!reader.Read()) return false;
return PaidGroup.CreateForm(reader, this);
}
protected int GetTransationCountThisMonth(DBManager db, int paidMethod)
{
String thisMonth = DateTime.Today.ToString("yyyy-MM", new System.Globalization.CultureInfo("en-US")) + "-01";
String sql = "select count(*) as cnt from ( SELECT distinct transaction_id FROM registration WHERE branch_id='" + this._branchID + "' and paid_method="+ paidMethod +" and regis_date >= '" + thisMonth + "') as t1";
OdbcDataReader reader = db.Query(sql);
if (reader.Read())
{
return reader.GetInt32(0);
}
return 0;
}
// load all teacher NOT in this paidgroup
// paidGroupID must be set before
public Teacher[] LoadNonMemberTeachers(DBManager db)
{
if (this._paidGroupID == 0) return null;
String subQuery = "SELECT teacher_id FROM paid_group_teacher_mapping WHERE paid_group_id='" + this._paidGroupID + "'";
String sql = "SELECT * FROM teacher WHERE is_active=1 AND teacher_id NOT IN ( " + subQuery + " ) ORDER BY teacher_id";
OdbcDataReader reader = db.Query(sql);
LinkedList<Teacher> list = new LinkedList<Teacher>();
while (reader.Read())
{
list.AddLast(Teacher.CreateForm(reader));
}
Teacher[] entities = new Teacher[list.Count];
int i = 0;
foreach (Teacher t in list)
{
entities[i++] = t;
}
return entities;
}
public static int GetMaxRegisID(DBManager db)
{
OdbcDataReader reader = db.Query("SELECT MAX(regis_id) from registration");
if (reader.Read())
{
if (reader.IsDBNull(0)) return 1;
int maxID = (int)reader.GetInt64(0);
return maxID;
}
return 0;
}
