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