public static bool CreateForm(OdbcDataReader reader, PaidGroup paidGroup)
{
int fCount = reader.FieldCount;
for (int i = 0; i < fCount; i++)
{
string name = reader.GetName(i);
// Map to DB field. Need to change if db changed
switch (name)
{
case "paid_group_id": paidGroup._paidGroupID = reader.GetInt32(i);
break;
case "name": paidGroup._name = reader.GetString(i);
break;
case "current_round": paidGroup._currentRound = reader.GetInt32(i);
break;
case "rate_info": paidGroup._rawRateInfo = reader.GetString(i);
paidGroup._rateInfo = PaidRateInfo.Parse(paidGroup._rawRateInfo);
break;
// helper info
}
}
return(reader.HasRows);
}
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 PaidGroup CreateForm(OdbcDataReader reader)
{
PaidGroup paidGroup = new PaidGroup();
PaidGroup.CreateForm(reader, paidGroup);
return(paidGroup);
}
protected void DoAddSubmitPaidGroup()
{
PaidGroup group = new PaidGroup();
group._paidGroupID = Int32.Parse(Request["groupID"]);
group._name = Request["name"];
StringBuilder sb = new StringBuilder();
sb.Append(Request["bound1"]); sb.Append(":"); sb.Append(Request["rate1"]); sb.Append(";");
sb.Append(Request["bound2"]); sb.Append(":"); sb.Append(Request["rate2"]); sb.Append(";");
sb.Append(Request["bound3"]); sb.Append(":"); sb.Append(Request["rate3"]); sb.Append(";");
sb.Append(Request["bound4"]); sb.Append(":"); sb.Append(Request["rate4"]); sb.Append(";");
sb.Append(Request["bound5"]); sb.Append(":"); sb.Append(Request["rate5"]); sb.Append(";");
sb.Append(Request["bound6"]); sb.Append(":"); sb.Append(Request["rate6"]); sb.Append(";");
sb.Append(Request["bound7"]); sb.Append(":"); sb.Append(Request["rate7"]); sb.Append(";");
sb.Append(Request["bound8"]); sb.Append(":"); sb.Append(Request["rate8"]); sb.Append(";");
sb.Append(Request["bound9"]); sb.Append(":"); sb.Append(Request["rate9"]); sb.Append(";");
sb.Append(Request["bound10"]); sb.Append(":"); sb.Append(Request["rate10"]);
group._rawRateInfo = group.BuildRateInfoString(sb.ToString());
group._rateInfo = PaidRateInfo.Parse(group._rawRateInfo);
// Save to DB
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
group.AddToDB(db);
db.Close();
}
public bool LoadPaidGroup(DBManager db)
{
if (_paidGroupID <= 0)
{
return(false);
}
_paidGroup = new PaidGroup();
_paidGroup.LoadFromDB(db, " paid_group_id=" + _paidGroupID);
return(true);
}
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 void DoEditPaidGroup(string groupID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
thePaidGroup = new PaidGroup();
if (!thePaidGroup.LoadFromDB(db, "paid_group_id='" + groupID + "'")) thePaidGroup = null;
teacherList = thePaidGroup.LoadNonMemberTeachers(db);
memberList = thePaidGroup.LoadMemberTeachers(db);
db.Close();
}
public PaymentHistory(Payment p, PaidGroup pg,int paidCost, int receiverId, AppUser user)
{
this._courseID = p._courseID;
this._paidDate = DateTime.Now;
this._paidCost = paidCost;
this._sumAllCost = p._sumAllCost;
this._sumMaxPayable = p._sumMaxPayable;
this._sumPaidCost = p._sumPaidCost;
this._paidRound = pg._currentRound;
this._costInfo = pg._rawRateInfo;
this._receiverTeacherID = receiverId;
this._username = user._username;
this._branchID = user._branchID;
}
public PaymentHistory(Payment p, PaidGroup pg, int paidCost, int receiverId, AppUser user)
{
this._courseID = p._courseID;
this._paidDate = DateTime.Now;
this._paidCost = paidCost;
this._sumAllCost = p._sumAllCost;
this._sumMaxPayable = p._sumMaxPayable;
this._sumPaidCost = p._sumPaidCost;
this._paidRound = pg._currentRound;
this._costInfo = pg._rawRateInfo;
this._receiverTeacherID = receiverId;
this._username = user._username;
this._branchID = user._branchID;
}
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 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 static bool CreateForm(OdbcDataReader reader, PaidGroup paidGroup)
{
int fCount = reader.FieldCount;
for (int i = 0; i < fCount; i++)
{
string name = reader.GetName(i);
// Map to DB field. Need to change if db changed
switch (name) {
case "paid_group_id": paidGroup._paidGroupID = reader.GetInt32(i);
break;
case "name": paidGroup._name = reader.GetString(i);
break;
case "current_round": paidGroup._currentRound = reader.GetInt32(i);
break;
case "rate_info": paidGroup._rawRateInfo = reader.GetString(i);
paidGroup._rateInfo = PaidRateInfo.Parse(paidGroup._rawRateInfo);
break;
// helper info
}
}
return reader.HasRows;
}
protected void DoListPayment(string searchStr, bool isNewSearch)
{
// get Page
int pg = 1;
if ((!isNewSearch) && (Request["pg"] != null)) pg = Int32.Parse(Request["pg"]);
string[,] bgclass = new string[,] { { "class=\"spec\"", "class=\"td1\"", "class=\"td1_grey\"", "class=\"td1_red\"" }, { "class=\"specalt\"", "class=\"alt\"", "class=\"td1_grey\"", "class=\"td1_red\"" } };
string grey = "class=\"thspec_grey\"";
string red = "class=\"thspec_red\"";
listPayment = new List<Payment>();
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
AppUser user = (AppUser)Session[SessionVar.USER];
filterPayment = Request["filter_payment"];
if (filterPayment == null) filterPayment = "0"; // by default
string whereSQL = " pm.course_id=c.course_id AND c.paid_group_id=pg.paid_group_id AND c.room_id = r.room_id ";
// only admin can view all payment
if (!user.IsAdmin())
{
whereSQL = whereSQL + " AND r.branch_id=" + user._branchID;
}
// filter only payable courses
if (filterPayment.Equals("0"))
{
whereSQL = whereSQL + " AND sum_max_payable<>sum_paid_cost ";
}
int numRec = db.QueryCount("SELECT Count(*) FROM payment pm, course c, paid_group pg, room r WHERE " + whereSQL);
OdbcDataReader reader = db.Query("SELECT pm.course_id as course_id ,c.bts_course_id as bts_course_id ,c.course_name as course_name, c.paid_group_id as paid_group_id"
+ ", pm.sum_all_cost as sum_all_cost, pm.sum_max_payable as sum_max_payable, pm.sum_paid_cost as sum_paid_cost "
+ "FROM payment pm, course c, paid_group pg, room r WHERE "
+ whereSQL
+ " ORDER BY c.paid_group_id, c.course_id "
+ " LIMIT " + Config.TBRECORD_PER_PAGE + " OFFSET " + (((pg - 1) * Config.TBRECORD_PER_PAGE)));
int i = 0;
PaidGroup currentPaidGroup = new PaidGroup();
while (reader.Read())
{
Payment payment = Payment.CreateForm(reader);
string chbname = "course" + payment._courseID;
int sumPayable = payment._sumMaxPayable - payment._sumPaidCost;
int n = 1;
if (sumPayable == 0) n = 2;
else if (sumPayable < 0) n = 3;
string bgcolor = (sumPayable ==0) ? grey : red;
// print paidgroup info
if (payment._paidGroupID != currentPaidGroup._paidGroupID)
{
currentPaidGroup._paidGroupID = payment._paidGroupID;
currentPaidGroup.LoadFromDB(db);
outBuf.Append("<tr><td colspan=6 " + bgclass[1, n] + "><b>");
outBuf.Append(PaidGroup.GetPaidGroupID(currentPaidGroup._paidGroupID)+" "+currentPaidGroup._name);
outBuf.Append("</b></td></tr>");
}
outBuf.Append("<tr>");
//outBuf.Append("<th scope=\"row\" abbr=\"Model\" " + (sumPayable > 0 ? bgclass[i % 2, 0] : bgcolor) + ">" + payment._btsCourseID + "</th>");
//outBuf.Append("<td " + bgclass[0, n] + " align=center ><input type=\"checkbox\" name=\"" + chbname + "\" id=\"" + chbname + "\" />" + "</td>");
outBuf.Append("<td " + bgclass[0, n] + " ><a href=\"CourseManage.aspx?actPage=view&targetID=" + payment._courseID + "\" >" + payment._btsCourseID + " " + payment._courseName + "</a></td>");
outBuf.Append("<td " + bgclass[0, n] + " align=center ><b>" + StringUtil.Int2StrComma(payment._sumAllCost) + "</b></td>");
outBuf.Append("<td " + bgclass[0, n] + " align=center ><b><font color=blue>" + StringUtil.Int2StrComma(payment._sumMaxPayable) + "</font></b></td>");
outBuf.Append("<td " + bgclass[0, n] + " align=center ><b><font color=red>" + StringUtil.Int2StrComma(payment._sumPaidCost) + "</font></b></td>");
outBuf.Append("<td " + bgclass[0, n] + " align=center ><b><font color=green>" + StringUtil.Int2StrComma(sumPayable) + "</font></b></td>");
/* outBuf.Append("<td " + bgclass[i % 2, n] + " align=center>"
+ payment._lastPaidDate.ToString("dd/MM/yyyy HH:mm", ci) + " </td>");
*/
outBuf.Append("<td " + bgclass[0, n] + " align=center> ");
outBuf.Append("<a href=\"javascript:setVal('actPage','view');setVal('targetID','" + payment._courseID + "');doSubmit()\"><img src=\"img/sys/view.gif\" border=0 alt=\"View detail\"></a> ");
// outBuf.Append("<a href=\"javascript:if (confirm('Delete this payment?')) { setVal('actPage','delete');setVal('targetID','" + payment._paymentID + "');doSubmit(); }\"><img src=\"img/sys/delete.gif\" border=0 alt=\"Delete\"></a> ");
outBuf.Append("</td>");
outBuf.Append("</tr>\n");
i++;
}
db.Close();
// calculate max page
int maxpg = numRec / Config.TBRECORD_PER_PAGE;
if (maxpg < 1) { maxpg = 1; }
else if (numRec % Config.TBRECORD_PER_PAGE > 0) { maxpg++; }
// Generate Page Navi HTML
outBuf2.Append("<b>Page</b> ");
for (i = 1; i <= maxpg; i++)
{
if (i == pg) { outBuf2.Append("<b>"+i+"</b> "); }
else {
outBuf2.Append(String.Format("<a href=\"PaymentManage.aspx?pg={0}&filter_payment={1}&qsearch={1}\">{0}</a> ", i.ToString(), filterPayment, searchStr));
}
}
// <a href="#">1</a> <b>2</b> <a href="#">3</a> <a href="#">4</a>
}
public static PaidGroup CreateForm(OdbcDataReader reader)
{
PaidGroup paidGroup = new PaidGroup();
PaidGroup.CreateForm(reader, paidGroup);
return paidGroup;
}
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 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 bool LoadPaidGroup(DBManager db)
{
if (_paidGroupID <= 0) return false;
_paidGroup = new PaidGroup();
_paidGroup.LoadFromDB(db, " paid_group_id=" + _paidGroupID);
return true;
}
protected void DoDeletePaidGroup(string groupID)
{
PaidGroup group = new PaidGroup();
group._paidGroupID = Int32.Parse(groupID);
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
group.DeleteToDB(db);
db.Close();
}
protected int DoPaidSubmitPayment(string courseID)
{
string paidCost = Request["paid_cost"];
string receiverTeacherID = Request["receiver_teacher_id"];
AppUser user = (AppUser)Session[SessionVar.USER];
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
db.BeginTransaction(IsolationLevel.ReadCommitted);
Payment pay = new Payment();
pay.LoadFromDB(db, " course_id="+courseID);
pay.LoadCourse(db);
PaidGroup pg = new PaidGroup();
pg.LoadFromDB(db, " paid_group_id="+pay._course._paidGroupID);
// Add history
PaymentHistory ph = new PaymentHistory(pay, pg, Int32.Parse(paidCost), Int32.Parse(receiverTeacherID), user);
ph.AddToDB(db);
// refresh Payment record
Payment.UpdatePaymentByCourse(db, Int32.Parse(courseID));
db.Commit();
// find latest payment
pay.LoadHistory(db);
int latestPaymentID = pay._historyList.Last.Value._paymentID;
db.Close();
return latestPaymentID;
}
protected void DoViewPayment(string courseID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
thePayment = new Payment();
thePayment.LoadFromDB(db, " course_id=" + courseID);
thePayment.LoadHistory(db);
thePayment.LoadCourse(db);
Course c = thePayment._course;
c.LoadTeacher(db);
c.LoadPaidGroup(db);
// preload all branches
Dictionary<int, Branch> branches = Branch.LoadListFromDBAsMap(db, "");
PaidGroup pg = new PaidGroup();
pg.LoadFromDB(db, " paid_group_id="+c._paidGroupID);
// Load teachers in this group
listTeacher = pg.LoadMemberTeachers(db);
// Generate HTML content
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\payment_view.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
// Construct Teacher List
StringBuilder teachTxt = new StringBuilder();
for (int i=0;i<listTeacher.Length;i++){
string link = "TeacherManage.aspx?actPage=edit&targetID=" + listTeacher[i]._teacherID;
teachTxt.Append("<a href=\"" + link + "\" >" + listTeacher[i]._firstname + " " + listTeacher[i]._surname + "</a><br>");
}
// Construct RaitInfo List
StringBuilder rinfoTxt = new StringBuilder();
for (int i = 0; i < pg._rateInfo.Length; i++)
{
rinfoTxt.Append("มากกว่า " + StringUtil.Int2StrComma(pg._rateInfo[i]._bound) + " บาท ได้รับ " + pg._rateInfo[i]._percent + " %<br>");
}
// Construct Paid history
StringBuilder phTxt = new StringBuilder();
foreach (PaymentHistory ph in thePayment._historyList)
{
ph.LoadReceiver(db);
ph.LoadUser(db);
Branch b = branches[ph._branchID];
string link = "TeacherManage.aspx?actPage=edit&targetID=" + ph._receiverTeacherID;
phTxt.AppendLine("<tr><td align=center>" + PaymentHistory.GetPaymentHistoryID(ph._paymentID) + "</td>");
phTxt.AppendLine("<td align=center>" + StringUtil.ConvertYearToEng(ph._paidDate, "yyyy/MM/dd HH:mm:ss") + "</td>");
phTxt.AppendLine("<td align=center>" + StringUtil.Int2StrComma(ph._paidCost) + "</td>");
phTxt.AppendLine("<td align=center><a href=\""+link+"\" >" + ph._receiverTeacher._firstname + " " + ph._receiverTeacher._surname + "</a></td>");
phTxt.AppendLine("<td align=center>" + ph._user._firstname + " " + ph._user._surname + "</td>");
phTxt.AppendLine("<td align=center>" + b._branchName + "</td>");
phTxt.AppendLine("<td align=center><a href=\"javascript:doInitPrint(" + ph._paymentID + ")\"><img src=\"" + Config.URL_PIC_SYS + "/view.gif\" border=0></a> </td>");
}
String htmlContent =
String.Format(templateContent
, c._courseName
, "<a href=\"TeacherManage.aspx?actPage=edit&targetID="+c._teacherID + "\" >" + c._teacher._firstname + " " + c._teacher._surname + "</a>"
, PaidGroup.GetPaidGroupID(c._paidGroupID) + " " + c._paidGroup._name
, teachTxt.ToString()
, rinfoTxt.ToString()
, StringUtil.Int2StrComma(thePayment._sumAllCost)
, StringUtil.Int2StrComma(thePayment._sumMaxPayable)
, StringUtil.Int2StrComma(thePayment._sumPaidCost)
, StringUtil.Int2StrComma(thePayment._sumMaxPayable - thePayment._sumPaidCost)
, phTxt.ToString()
);
outBuf.Append(htmlContent);
db.Close();
}
protected void DoAddTeacherSubmit(String paidGroupID, String teacherID)
{
PaidGroup group = new PaidGroup();
group._paidGroupID = Int32.Parse(paidGroupID);
// Save to DB
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
group.AddTeacherToDB(db, teacherID);
db.Close();
}