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 static bool CreateForm(OdbcDataReader reader, Payment payment)
{
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 "course_id": payment._courseID = reader.GetInt32(i);
break;
case "sum_all_cost": payment._sumAllCost = reader.GetInt32(i);
break;
case "sum_max_payable": payment._sumMaxPayable = reader.GetInt32(i);
break;
case "sum_paid_cost": payment._sumPaidCost = reader.GetInt32(i);
break;
case "last_paid_date": payment._lastPaidDate = new DateTime(reader.GetDate(i).Ticks);
break;
case "paid_round": payment._paidRound = reader.GetInt32(i);
break;
case "status": payment._status = reader.GetInt32(i);
break;
// helper info
case "bts_course_id": payment._btsCourseID = reader.GetString(i);
break;
case "course_name": payment._courseName = reader.GetString(i);
break;
case "course_start_date": payment._courseStartDate = new DateTime(reader.GetDate(i).Ticks);
break;
case "course_end_date": payment._courseEndDate = new DateTime(reader.GetDate(i).Ticks);
break;
case "paid_group_id": payment._paidGroupID = reader.GetInt32(i);
break;
}
}
return reader.HasRows;
}
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 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;
}
public static Payment CreateForm(OdbcDataReader reader)
{
Payment payment = new Payment();
Payment.CreateForm(reader, payment);
return payment;
}
public static LinkedList<Payment> CopyList(LinkedList<Payment> clist)
{
Payment[] tmpArray = new Payment[clist.Count];
clist.CopyTo(tmpArray, 0);
return new LinkedList<Payment>(tmpArray);
}
// 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;
}
public static Payment[] LoadListFromDBCustom(DBManager db, string sqlAll)
{
OdbcDataReader reader = db.Query(sqlAll);
LinkedList<Payment> list = new LinkedList<Payment>();
while (reader.Read())
{
list.AddLast(Payment.CreateForm(reader));
}
Payment[] entities = new Payment[list.Count];
int i = 0;
foreach (Payment t in list)
{
entities[i++] = t;
}
return entities;
}
protected void DoAddSubmitCourse()
{
Course c = new Course();
// validate data
c._btsCourseID = Request["bts_course_id"];
c._courseName = Request["course_name"];
c._shortName = Request["short_name"];
c._courseType = Request["course_type"];
c._courseDesc = Request["course_desc"];
c._roomID = Int32.Parse(Request["room_id"]);
c._teacherID = Int32.Parse(Request["teacher_id"]);
c._paidGroupID = Int32.Parse(Request["paid_group_id"]);
c._category = Request["category"];
c._startdate = StringUtil.getDate(Request["startdate"]);
c._enddate = StringUtil.getDate(Request["enddate"]);
c._dayOfWeek = Request["day_of_week"];
c._opentime = Request["opentime"];
c._cost =Int32.Parse(Request["cost"]);
c._seatLimit = Int32.Parse(Request["seat_limit"]);
c._bankRegisLimit = 0; // remove field
c._img = "noimg.jpg";
if (portrait.PostedFile.FileName != "")
{
try
{
string serverFileExt = Path.GetExtension(portrait.PostedFile.FileName);
Random rand = new Random((int)DateTime.Now.Ticks);
string fullpath = "";
string imgname = "";
do
{
string randomFName = rand.Next(Int32.MaxValue).ToString();
imgname = randomFName + serverFileExt;
fullpath = Config.PATH_APP_ROOT + "\\" + Config.URL_PIC_COURSE + "\\" + imgname;
} while (File.Exists(fullpath));
portrait.PostedFile.SaveAs(fullpath);
c._img = imgname;
}
catch (Exception err)
{
errorText = err.Message + err.StackTrace;
}
}
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
// Validate if bts code okay
Course[] dupBTSCourse = Course.LoadListFromDBCustom(db, "SELECT * FROM course c, payment p WHERE bts_course_id='" + c._btsCourseID + "' AND c.course_id=p.course_id AND (p.sum_max_payable>p.sum_paid_cost OR p.sum_max_payable=0)");
if (dupBTSCourse.Length == 0)
{
// no duplicate bts
// Save to DB
// Save to DB
db.BeginTransaction(IsolationLevel.ReadCommitted);
c.AddToDB(db);
c._courseID = Course.GetMaxCourseID(db);
// Update Payment with empty record
Payment payment = new Payment();
payment._courseID = c._courseID;
payment._sumAllCost = 0;
payment._sumPaidCost = 0;
payment._status = 0;
payment._lastPaidDate = DateTime.Now;
payment.AddToDB(db);
db.Commit();
db.Close();
}
}
protected void DoDeleteCourse(string courseID)
{
Course t = new Course();
t._courseID = Int32.Parse(courseID);
DBManager db = null;
try {
db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
// Check if payment id paid all
// If no, unable to delete this course
// Else
// Update status=1 (invalid)
Payment payment = new Payment();
if (payment.LoadFromDB(db, " course_id=" + courseID))
{
if (payment._sumPaidCost < payment._sumAllCost) // not yet paid all
{
errorText = "ยังมีเงินที่เบิกจ่ายไม่หมด " + (payment._sumAllCost - payment._sumPaidCost);
return;
}
else
{
payment._status = Payment.STATUS_INVALID;
payment.UpdateToDB(db);
}
}
// Delete course
t.DeleteToDB(db);
} finally {
if (db!=null) db.Close();
}
}