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 override bool UpdateToDB(DBManager db)
{
if (_paidGroupID <= 0) return false;
String[] key = { "paid_group_id", "name", "current_round", "rate_info" };
String[] val = { _paidGroupID.ToString(), _name, _currentRound.ToString(), PaidRateInfo.ToString(_rateInfo) };
return (db.Update("paid_group", key, val, "paid_group_id=" + _paidGroupID) > 0) ? true : false;
}
// 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 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);
}
public override bool AddToDB(DBManager db)
{
String[] key = { "paid_group_id", "name", "current_round", "rate_info" };
String[] val = { _paidGroupID.ToString(), _name, _currentRound.ToString(), PaidRateInfo.ToString(_rateInfo) };
return (db.Insert("paid_group", key, val) > 0) ? true : false;
}
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 bool LoadHistory(DBManager db)
{
if (_courseID <=0 ) return false;
_historyList.Clear();
PaymentHistory[] ph = PaymentHistory.LoadListFromDB(db, " WHERE course_id=" + this._courseID + " ORDER BY paid_date");
for (int i = 0; i < ph.Length; i++) _historyList.AddLast(ph[i]);
return true;
}
public override bool DeleteToDB(DBManager db)
{
if (_teacherID <= 0) return false;
// not realy delete in DB. just mark flag
return (db.Execute("UPDATE teacher SET is_active=0 WHERE teacher_id=" + _teacherID) > 0) ? true : false;
// return (db.Delete("teacher", "teacher_id=" + _teacherID) > 0) ? true : false;
}
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 abstract bool UpdateToDB(DBManager db);
public abstract bool LoadFromDB(DBManager db, string sqlCriteria);
public abstract bool DeleteToDB(DBManager db);
public abstract bool AddToDB(DBManager db);
public override bool UpdateToDB(DBManager db)
{
if (String.IsNullOrEmpty(_username)) return false;
String[] key = { "passwd", "firstname", "surname", "role_id", "branch_id" };
String[] val = { _passwd, _firstname, _surname, _roleId.ToString(), _branchID.ToString() };
return (db.Update("user", key, val, "username='******'") > 0) ? true : false;
}
public override bool DeleteToDB(DBManager db)
{
if (_courseID <= 0) return false;
return (db.Delete("payment", "course_id=" + _courseID) > 0) ? true : false;
}
public bool LoadCourse(DBManager db)
{
if (_courseID <= 0) return false;
_course = new Course();
_course.LoadFromDB(db, " course_id=" + _courseID);
// Assign loaded data to helper
this._btsCourseID = _course._btsCourseID;
return true;
}
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 override bool UpdateToDB(DBManager db)
{
if (_courseID <= 0) return false;
String[] key = { "course_id", "sum_all_cost", "sum_max_payable", "sum_paid_cost", "last_paid_date", "paid_round", "status" };
String[] val = { _courseID.ToString(), _sumAllCost.ToString(), _sumMaxPayable.ToString(), _sumPaidCost.ToString()
,StringUtil.ConvertYearToEng( _lastPaidDate, "yyyy/MM/dd HH:mm:ss"), _paidRound.ToString(), _status.ToString() };
return (db.Update("payment", key, val, "course_id=" + _courseID) > 0) ? true : false;
}
public override bool UpdateToDB(DBManager db)
{
if (_teacherID <= 0) return false;
String[] key = { "firstname", "surname", "citizen_id", "tel", "email", "sex", "birthday", "addr", "image", "subject" };
String[] val = { _firstname, _surname, _citizenID, _tel, _email, _sex, StringUtil.ConvertYearToEng(_birthday, "yyyy/MM/dd"), _addr, _img, _subject };
return (db.Update("teacher", key, val, "teacher_id="+_teacherID) > 0) ? true : false;
}
public bool AddTeacherToDB(DBManager db, String teacherID)
{
String[] key = { "paid_group_id", "teacher_id" };
String[] val = { _paidGroupID.ToString(), teacherID };
return (db.Insert("paid_group_teacher_mapping", key, val) > 0) ? true : false;
}
public static Payment[] LoadListFromDB(DBManager db, string sqlCriteria)
{
return LoadListFromDBCustom(db,"SELECT * FROM payment " + sqlCriteria);
}
public override bool DeleteToDB(DBManager db)
{
if (_paidGroupID <= 0) return false;
return (db.Delete("paid_group", "paid_group_id=" + _paidGroupID) > 0) ? true : false;
}
public static Payment[] LoadListFromDBbyTeacherID(DBManager db, string teacherID)
{
string sql = "SELECT p.*, c.bts_course_id as bts_course_id, c.course_name as course_name, c.start_date as course_start_date, c.end_date as course_end_date "
+" FROM payment p, course c, teacher t WHERE "
+" t.teacher_id=" + teacherID
+" AND p.course_id=c.course_id "
+" AND c.teacher_id=t.teacher_id";
return LoadListFromDBCustom(db, sql);
}
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 Payment[] LoadListFromDBbyTeacherIDInPaidGroup(DBManager db, string teacherID)
{
string sql = "SELECT p.*, c.bts_course_id as bts_course_id, c.course_name as course_name, c.start_date as course_start_date, c.end_date as course_end_date "
+ " FROM payment p, course c, teacher t, paid_group pg WHERE "
+ " pg.paid_group_id = (select paid_group_id from teacher where teacher_id="+teacherID+")"
+ " AND p.course_id=c.course_id "
+ " AND c.teacher_id=t.teacher_id "
+ " AND t.paid_group_id=pg.paid_group_id ";
return LoadListFromDBCustom(db, sql);
}
public bool RemoveTeacherToDB(DBManager db, String teacherID)
{
String[] key = { "paid_group_id", "teacher_id" };
String[] val = { _paidGroupID.ToString(), teacherID };
return (db.Delete("paid_group_teacher_mapping", "paid_group_id=" + _paidGroupID + " AND teacher_id=" + teacherID) > 0) ? true : false;
}
public static int UpdatePaymentByCourse(DBManager db, int courseID)
{
Course c = new Course();
c.LoadFromDB(db, " course_id="+courseID);
return UpdatePaymentByCourse(db, c);
}
public static PaidGroup[] LoadListFromDB(DBManager db, string sqlCriteria)
{
return LoadListFromDBCustom(db, "SELECT * FROM paid_group " + sqlCriteria);
}
// refresh latest payment data
// this table data is sensitive with high update rate env
// TODO: ensure that high transaction rate will not affect this table
public static int UpdatePaymentByCourse(DBManager db, Course course)
{
// load all courses in the same group
Course[] coursesSameGroup = Course.LoadListFromDB(db, " WHERE paid_group_id="+course._paidGroupID + " ORDER BY " + ORDER_BY);
int allIncome = 0; // in the same group
foreach (Course c in coursesSameGroup)
{
c.LoadPaidGroup(db);
int thisIncome = 0;
Registration[] reg = Registration.LoadListFromDB(db, " WHERE course_id=" + c._courseID);
for (int i = 0; i < reg.Length; i++)
{
thisIncome += reg[i]._discountedCost;
}
Payment payment = new Payment();
if (!payment.LoadFromDB(db, " course_id=" + c._courseID)) // not found, add new
{
payment._courseID = c._courseID;
payment._sumAllCost = thisIncome;
payment._sumMaxPayable = c._paidGroup.GetMaxPayableByRate(allIncome, thisIncome);
payment._sumPaidCost = 0;
payment._lastPaidDate = DateTime.Now;
payment._paidRound = c._paidGroup._currentRound;
payment._status = Payment.STATUS_OK;
payment.AddToDB(db);
}
else
{
// collect historical data
payment.LoadHistory(db);
payment._sumAllCost = thisIncome;
payment._sumMaxPayable = c._paidGroup.GetMaxPayableByRate(allIncome, thisIncome);
payment._sumPaidCost = payment.GetHistoricalSumPaidCost();
payment._lastPaidDate = payment.GetLatestPaidDate();
payment._paidRound = c._paidGroup._currentRound;
payment._status = Payment.STATUS_OK;
payment.UpdateToDB(db);
}
allIncome += thisIncome;
}
return 0;
}