public static int UpdatePaymentByCourse(DBManager db, int courseID)
{
Course c = new Course();
c.LoadFromDB(db, " course_id=" + courseID);
return(UpdatePaymentByCourse(db, c));
}
public bool LoadCourse(DBManager db)
{
if (_courseID <= 0)
{
return(false);
}
_course = new Course();
_course.LoadFromDB(db, " course_id=" + _courseID);
return(true);
}
public void DoEditCourse(string courseID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theCourse = new Course();
if (!theCourse.LoadFromDB(db, "course_id=" + courseID)) theCourse = null;
roomList = Room.LoadListFromDBCustom(db, "SELECT r.room_id, r.name, b.branch_name as branch_name FROM room r, branch b WHERE r.branch_id=b.branch_id");
teacherList = Teacher.LoadListFromDB(db, " ORDER BY firstname");
paidGroupList = PaidGroup.LoadListFromDB(db, " ORDER BY paid_group_id");
db.Close();
}
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 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;
this._courseType = _course._courseType;
this._courseCategotry = _course._category;
this._courseStart = _course._startdate;
this._courseEnd = _course._enddate;
return(true);
}
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 static int UpdatePaymentByCourse(DBManager db, int courseID)
{
Course c = new Course();
c.LoadFromDB(db, " course_id="+courseID);
return UpdatePaymentByCourse(db, c);
}
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;
}
protected void ProcessWizQueryCourseDetail(string courseID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
Course c = new Course();
c.LoadFromDB(db, " course_id="+courseID);
c.LoadTeacher(db);
db.Close();
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\" + AjaxSvc.WIZ_Q_COURSE_DETAIL + ".htm");
String templateContent = reader.ReadToEnd();
reader.Close();
String htmlContent =
String.Format(templateContent
, Config.URL_PIC_COURSE + "/" + c._img
, c._btsCourseID + " " + c._courseName
, c._startdate.ToString("dd/MM/yyyy", ci) + " - " + c._enddate.ToString("dd/MM/yyyy", ci) + "<br>"
+ c._dayOfWeek + " " + c._opentime
, c._teacher._firstname + " " + c._teacher._surname
, StringUtil.Int2StrComma(c._cost)
, c._courseDesc);
outBuf.Append(htmlContent);
}
protected void ProcessWizAddSelectedCourse(string course_id)
{
// get data from session
RegisTransaction reg = (RegisTransaction)Session[SessionVar.CURRENT_REGIS];
if (reg == null) { reg = new RegisTransaction(); }
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
Course course = new Course();
course.LoadFromDB(db," course_id=" + course_id);
db.Close();
if (course != null)
{
reg.AddCourse(course);
}
// save back to session
Session[SessionVar.CURRENT_REGIS] = reg;
}
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;
}
protected void DoViewCourse(string courseID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theCourse = new Course();
theCourse.LoadFromDB(db, " course_id=" + courseID);
theCourse.LoadTeacher(db);
theCourse.LoadPaidGroup(db);
db.Close();
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\course_view.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
String[,] students = LoadStudentList(db, courseID);
StringBuilder stdTxt = new StringBuilder();
int numStudent = (students.Length / 9);
int numCancel = 0;
for (int i = 0; i < numStudent; i++)
{
stdTxt.Append("<tr>");
for (int j = 0; j < 9; j++)
{
// edit registration
if (j == 8)
{
stdTxt.Append("<td>  <a href=\"javascript:jumpToEditRegistration(" + students[i, j] + ")\"><img src=\"img/sys/edit.gif\" border=\"0\" alt=\"Edit\"></a></td>");
continue;
}
stdTxt.Append("<td>  " + students[i, j]+"</td>");
if ((j == 6) && (students[i, j].Equals("ยกเลิก")))
{
numCancel++;
}
}
stdTxt.Append("</tr>");
}
String courseScheduleDetail = "-";
if (theCourse._courseType == "คอร์สสด") {
courseScheduleDetail = theCourse._startdate.ToString("dd/MM/yyyy", ci) + " - " + theCourse._enddate.ToString("dd/MM/yyyy", ci) + "<br>" + theCourse._dayOfWeek + " " + theCourse._opentime;
}
String htmlContent =
String.Format(templateContent
, Config.URL_PIC_COURSE + "/" + theCourse._img
, theCourse._btsCourseID + " " + theCourse._courseName
, theCourse._shortName
, theCourse._courseType
, theCourse._category
, courseScheduleDetail
, theCourse._teacher._firstname + " " + theCourse._teacher._surname
, PaidGroup.GetPaidGroupID(theCourse._paidGroupID)+ " " + theCourse._paidGroup._name
, StringUtil.Int2StrComma(theCourse._cost)
, theCourse._seatLimit.ToString()
, theCourse._bankRegisLimit.ToString()
, (numStudent - numCancel).ToString()
, theCourse._courseDesc
, "สมัคร " + numStudent + " คน ยกเลิก " + (numCancel) + " คน คงเหลือ " + (numStudent - numCancel) + " คน"
, stdTxt.ToString() );
outBuf.Append(htmlContent);
}
protected String DoSaveStudentListAsExcel(string courseID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theCourse = new Course();
theCourse._courseID = Int32.Parse(courseID);
theCourse.LoadFromDB(db);
String prefix = "course_students_";
int numDel = DeleteOldReportFile(prefix);
log.StampLine(Logger.INFO, "Delete old " + prefix + " " + numDel + " files");
DateTime now = DateTime.Now;
String dateStr = StringUtil.FillString(now.Ticks.ToString(), "0", 20, true) + "_"
+ StringUtil.FillString(Session.GetHashCode().ToString(), "0", 10, true);
String filename = prefix + dateStr + ".csv";
String physPath = Config.PATH_APP_ROOT + "\\file\\" + filename;
String urlPath = "file/" + filename;
FileStream fs = File.Open(physPath, FileMode.OpenOrCreate, FileAccess.Write);
TextWriter writer = new StreamWriter(fs, System.Text.Encoding.UTF8);
StringBuilder stdTxt = new StringBuilder();
/*
Encoding tis620 = Encoding.GetEncoding("tis-620");
Encoding utf8 = Encoding.UTF8;
Encoding encFrom = Encoding.Unicode;
Encoding encTo = Encoding.UTF8;
String unicodeString = "รหัสชีวิต";
// Convert the string into a byte[].
byte[] unicodeBytes = encFrom.GetBytes(unicodeString);
// Perform the conversion from one encoding to the other.
byte[] asciiBytes = Encoding.Convert(encFrom, encTo, unicodeBytes);
// Convert the new byte[] into a char[] and then into a string.
// This is a slightly different approach to converting to illustrate
// the use of GetCharCount/GetChars.
char[] asciiChars = new char[encTo.GetCharCount(asciiBytes, 0, asciiBytes.Length)];
encTo.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0);
// char[] unicodeChars = new char[encFrom.GetCharCount(unicodeBytes, 0, unicodeBytes.Length)];
// encFrom.GetChars(unicodeBytes, 0, unicodeBytes.Length, unicodeChars, 0);
//stdTxt.Append("ก");
*/
/*
stdTxt.Append(asciiChars + "\n");
stdTxt.Append(unicodeString + "\n");
// stdTxt.Append(unicodeChars+"\n");
stdTxt.Append("คิดถึงจัง");*/
//stdTxt.AppendLine((asciiString);
//stdTxt.AppendLine(unicodeString);
// รหัส,ชื่อ-นามสกุล,โรงเรียน,โทร.,อีเมล์,จำนวนเงิน,หมายเหตุ
//stdTxt.Append(StringUtil.GetExcelEncodingPrefix());
stdTxt.Append("รายชิ้อนักเรียนที่ลงทะเบียนคอร์ส " + theCourse._btsCourseID + " " + theCourse._courseName + "\n");
stdTxt.Append("วันที่สมัคร,ชื่อ-นามสกุล,โรงเรียน,โทร.,อีเมล์,จำนวนเงิน, สถานะ, หมายเหตุ \n" );
String[,] students = LoadStudentList(db, courseID);
for (int i = 0; i < students.Length / 9; i++)
{
for (int j = 0; j < 8; j++)
{
stdTxt.Append(StringUtil.AddCSVDQuote(students[i, j])+",");
}
stdTxt.AppendLine("");
if (i % 100 == 99)
{
writer.Write(stdTxt);
stdTxt = new StringBuilder();
}
}
if (stdTxt.Length > 0)
{
writer.Write(stdTxt);
}
writer.Close();
return urlPath;
}
protected String DoInitPrintStudentList(string courseID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theCourse = new Course();
theCourse.LoadFromDB(db);
String[,] students = LoadStudentList(db, courseID);
StringBuilder stdTxt = new StringBuilder();
for (int i=0;i<students.Length/9;i++) {
stdTxt.Append("<tr>");
for (int j = 0; j < 8; j++)
{
stdTxt.Append("<td nowrap><font size=2>  " + students[i, j]+"</font></td>");
}
stdTxt.Append("</tr>");
}
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\course_print_student.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
String htmlContent =
String.Format(templateContent
, theCourse._btsCourseID + " " + theCourse._courseName
, stdTxt.ToString() );
return htmlContent;
}
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;
this._courseType = _course._courseType;
this._courseCategotry = _course._category;
this._courseStart = _course._startdate;
this._courseEnd = _course._enddate;
return true;
}
public bool LoadCourse(DBManager db)
{
if (_courseID <= 0) return false;
_course = new Course();
_course.LoadFromDB(db, " course_id=" + _courseID);
return true;
}