protected void DoAddSubmitUser()
{
AppUser u = new AppUser();
// validate data
u._username = Request["username"];
u._passwd = Request["passwd"];
u._firstname = Request["firstname"];
u._surname = Request["surname"];
u._roleId = Int32.Parse(Request["role_id"]);
u._branchID = Int32.Parse(Request["branch_id"]);
// 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();
try
{
u.AddToDB(db);
}
catch (Exception e)
{
// show message?
}
db.Close();
}
protected AppUser ValidateLogin(string username, string passwd)
{
List <String> list = GetSystemDriverList();
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
OdbcDataReader reader = null;
try
{
reader = db.Query("SELECT u.*, b.branch_name as branch_name FROM user u, branch b WHERE u.username='******' AND u.branch_id=b.branch_id");
while (reader.Read())
{
AppUser user = AppUser.CreateForm(reader);
if (user._encodedPassword == null)
{
return(null);
}
if (user._encodedPassword.Equals(AppUser.GetMD5Encoded(passwd)))
{
return(user);
}
}
return(null);
}
catch (Exception e) { Console.WriteLine(e.StackTrace); return(null); }
finally { db.Close(reader); }
}
public void DoAddUser()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
roleList = Role.LoadListFromDB(db, "");
branchList = Branch.LoadListFromDB(db, "");
db.Close();
}
protected void DoAddSubmitUser()
{
AppUser u = new AppUser();
// validate data
u._username = Request["username"];
u._passwd = Request["passwd"];
u._firstname = Request["firstname"];
u._surname = Request["surname"];
u._roleId = Int32.Parse(Request["role_id"]);
u._branchID = Int32.Parse(Request["branch_id"]);
// 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();
try
{
u.AddToDB(db);
}
catch (Exception e)
{
// show message?
}
db.Close();
}
protected void ProcessWizQueryStudentDetail(string studentID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
Student s = new Student();
s.LoadFromDB(db, " student_id=" + studentID);
db.Close();
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\" + AjaxSvc.WIZ_Q_STUDENT_DETAIL + ".htm");
String templateContent = reader.ReadToEnd();
reader.Close();
String htmlContent =
String.Format(templateContent
, Config.URL_PIC_STUDENT + "/" + s._img
, Student.GetStudentID(s._studentID)
, s._firstname + " " + s._surname + " (" + s._nickname + ")"
, s._citizenID
, Config.URL_PIC_SYS + (s._sex.Equals("Male") ? "/boy.gif" : "/girl.gif")
, s._school
, s._birthday.ToString("dd/MM/yyyy", ci)
, s._addr
, s.GetTel()
, s._email
);
outBuf.Append(htmlContent);
}
protected void ProcessWizAddSelectedStudent(string student_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);
Student student = new Student();
student.LoadFromDB(db, " student_id=" + student_id);
db.Close();
if (student != null)
{
reg._student = student;
reg._studentID = student._studentID;
}
// save back to session
Session[SessionVar.CURRENT_REGIS] = reg;
}
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 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 ProcessWizQueryCourses(string searchStr)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
String subQuery = " (SELECT count(*) FROM registration r WHERE r.status=0 AND r.course_id=c.course_id) ";
String sql = "SELECT c.*," + subQuery + " AS num_registered from course c WHERE " + Course.GetQSearchSQL(searchStr);
Course[] courses = Course.LoadListFromDBCustom(db, sql);
db.Close();
outBuf.Append("<table>");
foreach (Course c in courses)
{
if (c._numRegistered < c._seatLimit)
{
string jsShowDiv = " onmouseover=\"queryCourseDetail(" + c._courseID + ");showDivAt('divdetail')\" onmouseout=\"hideDiv('divdetail')\" ";
string icon = "<img style=\"cursor:pointer\" border=0 src=\"" + Config.URL_PIC_SYS + "/info.gif\" " + jsShowDiv + ">";
outBuf.Append("<tr valign=middle><td>  " + icon + "</td><td><a href=\"javascript:addCourse('" + c._courseID + "')\">" + c._btsCourseID + " " + c._courseName + "[" + c._numRegistered + "/" + c._seatLimit + "]</a></td></tr>");
}
else
{
// full
string jsShowDiv = " onmouseover=\"queryCourseDetail(" + c._courseID + ");showDivAt('divdetail')\" onmouseout=\"hideDiv('divdetail')\" ";
string icon = "<img style=\"cursor:pointer\" border=0 src=\"" + Config.URL_PIC_SYS + "/info.gif\" " + jsShowDiv + ">";
outBuf.Append("<tr valign=middle><td>  " + icon + "</td><td> <font color=red>เต็ม </font>" + c._btsCourseID + " " + c._courseName + "[" + c._numRegistered + "/" + c._seatLimit + "]</td></tr>");
}
}
outBuf.Append("</table>");
}
public void btnAdd_Click()
{
try
{
Employee employee = new Employee();
employee.ID = Convert.ToInt32(presenter1.crudFormView.txtID.Text);
employee.FistName = presenter1.crudFormView.txtFirstName.Text;
employee.LastName = presenter1.crudFormView.txtLastName.Text;
employee.Sex = presenter1.crudFormView.txtSex.Text;
employee.Age = Convert.ToInt32(presenter1.crudFormView.txtAge.Text);
IDBManager db = new MySQLDBManager();
db.AddEmployee(employee);
BindingSource bindingsource = new BindingSource();
presenter1.crudFormView.dataGridView1.DataSource = null;
presenter1.crudFormView.dataGridView1.DataSource = bindingsource;
presenter1.crudFormView.dataGridView1.Refresh();
con = new MySqlConnection();
con.ConnectionString = @"datasource=127.0.0.1;port=3306;username=root;password=;database=assigment1;Convert Zero Datetime=True;";
con.Open();
adapt = new MySqlDataAdapter("select id,first_name,last_name,sex,age from employees", con);
ds = new System.Data.DataSet();
adapt.Fill(ds, "Employee_Details");
presenter1.crudFormView.dataGridView1.DataSource = ds.Tables[0];
MessageBox.Show("Created!", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void DoAddPaidGroup()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
groupList = PaidGroup.LoadListFromDB(db, "");
//teacherList = Teacher.LoadListFromDB(db, " WHERE is_active=1 ORDER BY firstname");
db.Close();
}
public void AddForm()
{
try
{
Product product = new Product();
product.Title = presenter1.addFormView.txtTitle.Text;
product.Description = presenter1.addFormView.txtDescription.Text;
product.Color = presenter1.addFormView.txtColor.Text;
product.Size = Convert.ToDouble(presenter1.addFormView.txtSize.Text);
product.Price = Convert.ToDouble(presenter1.addFormView.txtPrice.Text);
product.Stock = Convert.ToInt32(presenter1.addFormView.txtSize.Text);
IDBManager db = new MySQLDBManager();
db.Create(product);
BindingSource bindingsource = new BindingSource();
presenter1.addFormView.dataGridView1.DataSource = null;
presenter1.addFormView.dataGridView1.DataSource = bindingsource;
presenter1.addFormView.dataGridView1.Refresh();
con = new MySqlConnection();
con.ConnectionString = @"datasource=127.0.0.1;port=3306;username=root;password=;database=assigment1;Convert Zero Datetime=True;";
con.Open();
adapt = new MySqlDataAdapter("select title ,description,color,size,price,stock from product", con);
ds = new System.Data.DataSet();
adapt.Fill(ds, "Product_Details");
presenter1.addFormView.dataGridView1.DataSource = ds.Tables[0];
MessageBox.Show("Created!", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
protected void AddMockStudent(int num)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
Random rand = new Random((int)DateTime.Now.Ticks);
for (int i = 0; i < num; i++)
{
Student t = new Student();
t._firstname = StringUtil.RandomString(8, true);
t._surname = StringUtil.RandomString(12, true);
t._nickname = StringUtil.RandomString(7, true);
t._img = "student"+rand.Next(1,6)+".jpg";
t._birthday = new DateTime(rand.Next(1980, 2005), rand.Next(1, 13), rand.Next(1, 29));
t._school = StringUtil.RandomString(20, true);
t._tel = "08" + rand.Next(10000000, 99999999);
t._tel2 = "08" + rand.Next(10000000, 99999999);
t._sex = (rand.Next(0,2)>0?"Male":"Female");
t._addr = StringUtil.RandomString(50, true);
t.AddToDB(db);
}
}
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();
}
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);
}
protected void AddMockStudent(int num)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
Random rand = new Random((int)DateTime.Now.Ticks);
for (int i = 0; i < num; i++)
{
Student t = new Student();
t._firstname = StringUtil.RandomString(8, true);
t._surname = StringUtil.RandomString(12, true);
t._nickname = StringUtil.RandomString(7, true);
t._img = "student" + rand.Next(1, 6) + ".jpg";
t._birthday = new DateTime(rand.Next(1980, 2005), rand.Next(1, 13), rand.Next(1, 29));
t._school = StringUtil.RandomString(20, true);
t._tel = "08" + rand.Next(10000000, 99999999);
t._tel2 = "08" + rand.Next(10000000, 99999999);
t._sex = (rand.Next(0, 2) > 0?"Male":"Female");
t._addr = StringUtil.RandomString(50, true);
t.AddToDB(db);
}
}
public void DoAddRoom()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
branchList = Branch.LoadListFromDB(db, "");
db.Close();
}
protected void DoInitPrintAll(string regisTransactionID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
outBuf = new StringBuilder();
// find one of registration from list by transaction id
AppUser user = (AppUser)Session[SessionVar.USER];
Registration[] reg = Registration.LoadListFromDB(db, " WHERE transaction_id=" + regisTransactionID + " AND branch_id=" + user._branchID + " ORDER BY regis_id ");
// print
PageBreaker pb = new PageBreaker(Config.PAGE_BREAK_MAX);
StringBuilder tmpBuf = Registration.PrintCard(db, reg[0]._regisID);
pb.Print(outBuf, tmpBuf, Config.PAGE_BREAK_CARD);
outBuf.AppendLine("<br>");
pb.Print(outBuf, Registration.PrintReceipt(db, reg[0]._regisID, "สำหรับนักเรียน"), Config.PAGE_BREAK_RECEIPT);
outBuf.AppendLine("<br>");
pb.Print(outBuf, Registration.PrintReceipt(db, reg[0]._regisID, "สำหรับโรงเรียน"), Config.PAGE_BREAK_RECEIPT);
db.Close();
// Save to session
Session[SessionVar.PRINT_INFO] = new StringBuilder(outBuf.ToString());
}
public void DoAddTeacher()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
groupList = PaidGroup.LoadListFromDB(db, "");
db.Close();
}
public void DoEditStudent(string studentID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theStudent = new Student();
if (!theStudent.LoadFromDB(db, "student_id=" + studentID)) theStudent = null;
// List Course History
string[,] bgclass = new string[,] { { "class=\"spec\"", "class=\"td1\"" }, { "class=\"specalt\"", "class=\"alt\"" } };
string query = "SELECT * from course ";
query = query + "WHERE course.course_id in (select registration.course_id from registration where registration.student_id='";
query = query + studentID + "') order by start_date desc";
OdbcDataReader reader = db.Query(query);
int i = 0;
while (reader.Read())
{
Course course = Course.CreateForm(reader);
outBuf3.Append("<tr>");
outBuf3.Append("<th scope=\"row\" abbr=\"Model\" " + bgclass[i % 2, 0] + " align=center>" + course._startdate.ToString("dd MMM yyyy", ci) + " </th>");
outBuf3.Append("<td " + bgclass[i % 2, 1] + " align=center>" + course._btsCourseID + " </td>");
outBuf3.Append("<td " + bgclass[i % 2, 1] + " align=left>" + course._courseName + " </td>");
outBuf3.Append("<td " + bgclass[i % 2, 1] + " >" + course._cost + " </td>");
outBuf3.Append("</tr>\n");
i++;
}
db.Close();
}
public void DoAddPromotion()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
PrintCourseOption(db);
db.Close();
}
protected List <string> GetListUser(string branch_id)
{
List <string> list = new List <String>();
if (string.IsNullOrEmpty(branch_id))
{
return(list);
}
string sql = "SELECT username,firstname,surname FROM user WHERE branch_id='" + branch_id + "'";
string ret = String.Empty;
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
OdbcDataReader reader = db.Query(sql);
int fCount = reader.FieldCount;
while (reader.Read())
{
string tmp = "[" + reader.GetString(0).ToUpper() + "] " + reader.GetString(1) + " " + reader.GetString(2);
list.Add(tmp);
}
db.Close();
return(list);
}
public void DoEditTeacher(string teacherID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
//Load GroupList
groupList = PaidGroup.LoadListFromDB(db, "");
theTeacher = new Teacher();
if (!theTeacher.LoadFromDB(db, "teacher_id=" + teacherID)) theTeacher = null;
// List Course History
string[,] bgclass = new string[,] { { "class=\"spec\"", "class=\"td1\"" }, { "class=\"specalt\"", "class=\"alt\"" } };
string query = "SELECT * from course ";
query = query + "WHERE teacher_id='";
query = query + teacherID + "' order by start_date desc";
OdbcDataReader reader = db.Query(query);
int i = 0;
while (reader.Read())
{
Course course = Course.CreateForm(reader);
outBuf3.Append("<tr>");
outBuf3.Append("<th scope=\"row\" abbr=\"Model\" " + bgclass[i % 2, 0] + " align=center>" + course._startdate.ToString("dd MMM yyyy", ci) + " </th>");
outBuf3.Append("<td " + bgclass[i % 2, 1] + " align=center>" + course._btsCourseID + " </td>");
outBuf3.Append("<td " + bgclass[i % 2, 1] + " align=left>" + course._courseName + " </td>");
outBuf3.Append("<td " + bgclass[i % 2, 1] + " >" + course._cost + " </td>");
outBuf3.Append("</tr>\n");
i++;
}
db.Close();
}
protected void DoEditSubmitPaidGroup(string groupID)
{
PaidGroup group = new PaidGroup();
// validate data
group._paidGroupID = Int32.Parse(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.UpdateToDB(db);
db.Close();
}
protected void DoEditSubmitTeacher(string teacherID)
{
Teacher t = new Teacher();
// validate data
t._teacherID = Int32.Parse(teacherID);
t._firstname = Request["firstname"];
t._surname = Request["surname"];
t._citizenID = Request["citizen_id"];
t._sex = Request["sex"];
t._tel = Request["tel"];
t._email = Request["email"];
t._addr = Request["addr"];
//t._subject = Request["subject"];
if (Request["birthday"] != null)
{
string[] s = Request["birthday"].Split('/');
t._birthday = new DateTime(Int32.Parse(s[2]) - 543, Int32.Parse(s[1]), Int32.Parse(s[0]));
}
else
{
t._birthday = new DateTime();
}
// default to old value
t._img = Request["img_old"];
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_TEACHER + "\\" + imgname;
} while (File.Exists(fullpath));
portrait.PostedFile.SaveAs(fullpath);
t._img = imgname;
}
catch (Exception err)
{
errorText = err.Message + err.StackTrace;
}
}
// 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();
t.UpdateToDB(db);
db.Close();
}
public void DoAddCourse()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
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 = paidGroupList = PaidGroup.LoadListFromDB(db, " ORDER BY paid_group_id");
db.Close();
}
public void UpdateStudent()
{
MySQLDBManager op = new MySQLDBManager();
Student student = new Student(20, "Ale", new System.DateTime(1995, 10, 27), "Cluj");
bool rez = op.UpdateTest(student);
Assert.IsTrue(rez);
}
public void AddStudent()
{
MySQLDBManager op = new MySQLDBManager();
Student student = new Student(21, "Alexandra", new System.DateTime(1995, 10, 27), "Cluj");
bool rez = op.AddTest(student);
Assert.IsTrue(rez);
}
public void DoAddPaidGroup()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
groupList = PaidGroup.LoadListFromDB(db, "");
//teacherList = Teacher.LoadListFromDB(db, " WHERE is_active=1 ORDER BY firstname");
db.Close();
}
public void DoAddCourse()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
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 = paidGroupList = PaidGroup.LoadListFromDB(db, " ORDER BY paid_group_id");
db.Close();
}
public void DoEditRoom(string roomID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theRoom = new Room();
if (!theRoom.LoadFromDB(db, "room_id=" + roomID)) theRoom = null;
branchList = Branch.LoadListFromDB(db, "");
db.Close();
}
public void DoEditBranch(string branchID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theBranch = new Branch();
if (!theBranch.LoadFromDB(db, "branch_id=" + branchID))
{
theBranch = null;
}
}
protected StringBuilder DoInitPrinRegistrationReceipt(string regisID, string title)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
StringBuilder tmpBuf = Registration.PrintReceipt(db, Int32.Parse(regisID), title);
db.Close();
return(tmpBuf);
}
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();
}
protected Branch[] LoadAllBranches()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
try
{
Branch[] allBranches = Branch.LoadListFromDB(db, "");
return allBranches;
}
catch (Exception e) { return null; }
finally { db.Close(); }
}
public void DoEditUser(string username)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
theUser = new AppUser();
if (!theUser.LoadFromDB(db, "username='******'")) theUser = null;
roleList = Role.LoadListFromDB(db, "");
branchList = Branch.LoadListFromDB(db, "");
db.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
// HttpWebResponse response = (HttpWebResponse)webRequest.GetResponse();
Encoding enc = Response.ContentEncoding;
Response.Write("<br>" + enc.HeaderName + " " + enc.BodyName + " ");
Response.Write("<br>" + Encoding.Default.HeaderName + " " + Encoding.Default.BodyName + " ");
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
//db.Execute("SET NAMES 'tis620'");
Student theStudent = new Student();
theStudent.LoadFromDB(db, " student_id=15318");
Response.Write("นักเรียนชื่อ " + theStudent._firstname + "นามสกุล " + theStudent._surname);
EncodingInfo[] ecs = Encoding.GetEncodings();
for (int j = 0; j < ecs.Length; j++)
{
Encoding ec = ecs[j].GetEncoding();
byte[] b = ec.GetBytes(theStudent._firstname);
Response.Write("<br>" + ec.HeaderName + " " + ec.BodyName + " ");
for (int i = 0; i < b.Length; i++)
{
Response.Write(b[i] + " ");
}
}
byte[] bytes = Encoding.Unicode.GetBytes(theStudent._firstname);
string myString = Encoding.UTF8.GetString(bytes);
Response.Write(myString);
bytes = Encoding.GetEncoding("tis-620").GetBytes(theStudent._firstname);
myString = Encoding.UTF8.GetString(bytes);
Response.Write(myString);
bytes = Encoding.GetEncoding("windows-874").GetBytes(theStudent._firstname);
myString = Encoding.UTF8.GetString(bytes);
Response.Write(myString);
CultureInfo ci = CultureInfo.InstalledUICulture;
Response.Write("Default Language Info:");
Response.Write("* Name: " + ci.Name);
Response.Write("* Display Name: " + ci.DisplayName);
Response.Write("* English Name: " + ci.EnglishName);
Response.Write("* 2-letter ISO Name: " + ci.TwoLetterISOLanguageName);
Response.Write("* 3-letter ISO Name: " + ci.ThreeLetterISOLanguageName);
Response.Write("* 3-letter Win32 API Name: " + ci.ThreeLetterWindowsLanguageName);
}
protected void DoDeleteBranch(string branchID)
{
Branch t = new Branch();
t._branchID = Int32.Parse(branchID);
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
t.DeleteToDB(db);
db.Close();
}
protected Branch[] LoadAllBranches()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
try
{
Branch[] allBranches = Branch.LoadListFromDB(db, "");
return(allBranches);
}
catch (Exception e) { return(null); }
finally { db.Close(); }
}
protected void DoDeleteUser(string username)
{
AppUser u = new AppUser();
u._username = username;
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
u.DeleteToDB(db);
db.Close();
}
// TODO: Check duplicated registration
protected void ProcessRegistration(RegisTransaction reg)
{
AppUser user = (AppUser)Session[SessionVar.USER];
// Collect additional variable
// * User information comes from session NOT DB
reg._username = user._username;
reg._paidMethod = Int32.Parse(Request["paid_method"]);
if (Session[SessionVar.BRANCH_SELECTED] == null)
{
reg._branchID = user._branchID;
}
else
{
reg._branchID = Int32.Parse((String)Session[SessionVar.BRANCH_SELECTED]);
user._branchID = reg._branchID;
Session[SessionVar.USER] = user;
}
reg._paiddate = StringUtil.getDate(Request["paid_date"]);
// seat
for (int i = 0; i < Request.Form.AllKeys.Length; i++)
{
if (Request.Form.AllKeys[i].StartsWith("seat_no"))
{
string courseID = Request.Form.AllKeys[i].Substring(7);
string seatNo = Request[Request.Form.AllKeys[i]];
try
{
reg._seatNoMap.Add(courseID, seatNo);
}
catch (Exception ex) { }
continue;
}
if (Request.Form.AllKeys[i].StartsWith("note"))
{
string courseID = Request.Form.AllKeys[i].Substring(4);
string note = Request[Request.Form.AllKeys[i]];
try
{
reg._noteMap.Add(courseID, note);
}
catch (Exception ex) { }
continue;
}
}
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
reg.AddToDB(db);
db.Close();
}
protected void DoDeleteStudent(string studentID)
{
Student t = new Student();
t._studentID = Int32.Parse(studentID);
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
t.DeleteToDB(db);
db.Close();
}
protected void DoListRegister(string searchStr)
{
// get Page
int pg = 1;
if (Request["pg"]!=null) pg = Int32.Parse(Request["pg"]);
string[,] bgclass = new string[,] { { "class=\"spec\"", "class=\"td1\"" }, { "class=\"specalt\"", "class=\"alt\"" } };
// listRegistration = new List<Registration>();
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
if (searchStr.Trim().Length > 0) searchStr = " WHERE " + searchStr;
int numRec = db.QueryCount("SELECT Count(*) FROM registration " + searchStr);
OdbcDataReader reader = db.Query("SELECT * FROM registration " + searchStr + " LIMIT " + Config.TBRECORD_PER_PAGE + " OFFSET " + (((pg - 1) * Config.TBRECORD_PER_PAGE)));
int i = 0;
while (reader.Read())
{
Registration regis = Registration.CreateForm(reader);
outBuf.Append("<tr>");
outBuf.Append("<th scope=\"row\" abbr=\"Model\" " + bgclass[i % 2, 0] + ">" + regis._regisID + "</th>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._transactionID + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._regisdate.ToString("dd/MM/yyyy",ci) + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._studentID + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._courseID + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._fullCost + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._promotionID + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._discountedCost + " </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center>" + regis._isPaid.ToString() + " </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 (maxpg % 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=\"ReportRegis.aspx?pg={0}\">{1}</a> ", i.ToString(), i.ToString()));
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// HttpWebResponse response = (HttpWebResponse)webRequest.GetResponse();
Encoding enc = Response.ContentEncoding;
Response.Write("<br>" + enc.HeaderName + " " + enc.BodyName + " ");
Response.Write("<br>" + Encoding.Default.HeaderName + " " + Encoding.Default.BodyName + " ");
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
//db.Execute("SET NAMES 'tis620'");
Student theStudent = new Student();
theStudent.LoadFromDB(db, " student_id=15318");
Response.Write("นักเรียนชื่อ " + theStudent._firstname + "นามสกุล "+ theStudent._surname);
EncodingInfo[] ecs = Encoding.GetEncodings();
for (int j = 0; j < ecs.Length; j++)
{
Encoding ec = ecs[j].GetEncoding();
byte[] b = ec.GetBytes(theStudent._firstname);
Response.Write("<br>"+ec.HeaderName + " " + ec.BodyName + " ");
for (int i = 0; i < b.Length; i++)
{
Response.Write(b[i] + " ");
}
}
byte[] bytes = Encoding.Unicode.GetBytes(theStudent._firstname);
string myString = Encoding.UTF8.GetString(bytes);
Response.Write(myString);
bytes = Encoding.GetEncoding("tis-620").GetBytes(theStudent._firstname);
myString = Encoding.UTF8.GetString(bytes);
Response.Write(myString);
bytes = Encoding.GetEncoding("windows-874").GetBytes(theStudent._firstname);
myString = Encoding.UTF8.GetString(bytes);
Response.Write(myString);
CultureInfo ci = CultureInfo.InstalledUICulture;
Response.Write("Default Language Info:");
Response.Write("* Name: "+ ci.Name);
Response.Write("* Display Name: "+ ci.DisplayName);
Response.Write("* English Name: "+ ci.EnglishName);
Response.Write("* 2-letter ISO Name: "+ ci.TwoLetterISOLanguageName);
Response.Write("* 3-letter ISO Name: "+ ci.ThreeLetterISOLanguageName);
Response.Write("* 3-letter Win32 API Name: "+ ci.ThreeLetterWindowsLanguageName);
}
public static void Reload()
{
_authDB.Clear();
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
OdbcDataReader reader = db.Query("SELECT r.role_id,r.name,rightname,action FROM authorization a, role r WHERE a.role_id = r.role_id");
while (reader.Read())
{
int roleID = reader.GetInt32(0);
string right = reader.GetString(2);
string action = reader.GetString(3);
Add(roleID, right, action);
}
db.Close();
}
protected void DoAddSubmitBranch()
{
Branch b = new Branch();
// validate data
b._branchName = Request["branch_name"];
b._branchCode = Request["branch_code"];
b._address = Request["address"];
b._tel = Request["tel"];
b._supervisor = Request["supervisor"];
b._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_BRANCH + "\\" + imgname;
} while (File.Exists(fullpath));
portrait.PostedFile.SaveAs(fullpath);
b._img = imgname;
}
catch (Exception err)
{
errorText = err.Message + err.StackTrace;
}
}
// 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();
b.AddToDB(db);
db.Close();
}
protected void DoEditRegistration(string regisID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
theReg = new Registration();
theReg.LoadFromDB(db, " regis_id=" + regisID);
theReg.LoadCourse(db);
theReg.LoadStudent(db);
theReg.LoadBranch(db);
// Branch branch = new Branch();
// branch.LoadFromDB(db, " branch_id=" + theReg._branchID);
// Generate HTML content
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\registration_edit.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
string promotionTxt = theReg._promotionID > 0 ?
"<a href=\"PromotionManage.aspx?actPage=view&targetID="+ theReg._promotionID + "\" >" + Promotion.GetPromotionID(theReg._promotionID) + " </a>" : "-";
String htmlContent =
String.Format(templateContent
, theReg.GetRegisTransactionID()
, Registration.GetRegistrationID(theReg._regisID)
, "<a href=\"CourseManage.aspx?actPage=view&targetID=" + theReg._course._courseID + "\" >" + theReg._course._btsCourseID + " " + theReg._course._courseName + "</a>"
, promotionTxt
, "<a href=\"StudentManage.aspx?actPage=view&targetID=" + theReg._studentID + "\" >" + Student.GetStudentID(theReg._student._studentID) + " " + theReg._student._firstname + " " + theReg._student._surname + "</a>"
, StringUtil.ConvertYearToEng(theReg._regisdate, "yyyy/MM/dd HH:mm:ss")
, StringUtil.ConvertYearToEng(theReg._paiddate, "yyyy/MM/dd")
, theReg._branch._branchName
, StringUtil.Int2StrComma(theReg._fullCost)
, StringUtil.Int2StrComma(theReg._discountedCost)
, Registration.GetPaidMethodText(theReg._paidMethod.ToString())
, theReg._seatNo
, theReg._username
, Registration.GetStatusText(theReg._status)
);
outBuf.Append(htmlContent);
db.Close();
}
protected void AddMockTeacher(int num)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
Random rand = new Random((int)DateTime.Now.Ticks);
for (int i = 0; i < num; i++)
{
Teacher t = new Teacher();
t._firstname = StringUtil.RandomString(11, true);
t._surname = StringUtil.RandomString(16, true);
t._img = "teacher" + rand.Next(1, 6) + ".jpg";
t._birthday = new DateTime(rand.Next(1900, 2005), rand.Next(1, 13), rand.Next(1, 29));
t._subject = StringUtil.RandomString(30, true);
t._tel = "08" + rand.Next(10000000, 99999999);
t._sex = (rand.Next(0, 2) > 0 ? "Male" : "Female");
t._addr = StringUtil.RandomString(50, true);
t.AddToDB(db);
}
}
protected void DoInitPrintAll(string regisTransactionID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
outBuf = new StringBuilder();
// find one of registration from list by transaction id
AppUser user = (AppUser)Session[SessionVar.USER];
Registration[] reg = Registration.LoadListFromDB(db, " WHERE transaction_id="+regisTransactionID+" AND branch_id="+ user._branchID +" ORDER BY regis_id ");
// print
PageBreaker pb = new PageBreaker(Config.PAGE_BREAK_MAX);
StringBuilder tmpBuf = Registration.PrintCard(db, reg[0]._regisID);
pb.Print(outBuf, tmpBuf, Config.PAGE_BREAK_CARD);
outBuf.AppendLine("<br>");
pb.Print(outBuf, Registration.PrintReceipt(db, reg[0]._regisID, "สำหรับนักเรียน"), Config.PAGE_BREAK_RECEIPT);
outBuf.AppendLine("<br>");
pb.Print(outBuf, Registration.PrintReceipt(db, reg[0]._regisID, "สำหรับโรงเรียน"), Config.PAGE_BREAK_RECEIPT);
db.Close();
// Save to session
Session[SessionVar.PRINT_INFO] = new StringBuilder(outBuf.ToString());
}
protected void DoInitPrintReceiptPaymentData(string paymentID)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
PaymentHistory pm = new PaymentHistory();
pm.LoadFromDB(db, " payment_id="+paymentID);
pm.LoadCourse(db);
pm._course.LoadTeacher(db);
pm._course.LoadPaidGroup(db);
// load teacher in this group
Teacher[] listTeacher = pm._course._paidGroup.LoadMemberTeachers(db);
pm.LoadReceiver(db);
// preload all branches
Dictionary<int, Branch> branches = Branch.LoadListFromDBAsMap(db, "");
// Load PaymentHistory BEFORE this
PaymentHistory[] pmList = PaymentHistory.LoadListFromDB(db, " WHERE course_id='" + pm._courseID + "' and payment_id<='" + pm._paymentID + "' ORDER BY payment_id");
// 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(listTeacher[i]._firstname + " " + listTeacher[i]._surname + "<br>");
}
// Construct Paid history
StringBuilder phTxt = new StringBuilder();
for (int i=0;i<pmList.Length;i++)
{
PaymentHistory ph = pmList[i];
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>" + ph._receiverTeacher._firstname + " " + ph._receiverTeacher._surname + "</td>");
phTxt.AppendLine("<td align=center>" + ph._user._firstname + " "+ ph._user._surname+ "</td>");
phTxt.AppendLine("<td align=center>" + b._branchName + "</td>");
}
// User
AppUser user = new AppUser();
user.LoadFromDB(db, " username='******'");
// Generate HTML content
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\payment_print.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
String htmlContent =
String.Format(templateContent
, StringUtil.ConvertYearToEng(pm._paidDate, "yyyy/MM/dd HH:mm:ss")
, pm._receiverTeacher._firstname + " " + pm._receiverTeacher._surname
, pm._course._btsCourseID + " \"" + pm._course._courseName + "\""
, StringUtil.Int2StrComma(pm._paidCost)
, pm._course._teacher._firstname + " " + pm._course._teacher._surname
, PaidGroup.GetPaidGroupID(pm._course._paidGroup._currentRound)
, teachTxt.ToString()
, StringUtil.Int2StrComma(pm._sumMaxPayable)
, StringUtil.Int2StrComma(pm._sumPaidCost + pm._paidCost)
, phTxt.ToString()
, user._firstname + " " + user._surname
);
outBuf.Append(htmlContent);
db.Close();
}
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;
}
protected void DoListPaymentByTeacher(string teacherID, string searchStr, bool isNewSearch)
{
DBManager db;
db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
listTeacher = Teacher.LoadListFromDB(db, " WHERE is_active=1 ORDER BY firstname ");
if (teacherID == null) return;
// 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>();
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.teacher_id=t.teacher_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, teacher t, 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, t.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, teacher t, room r WHERE "
+ whereSQL + " LIMIT " + Config.TBRECORD_PER_PAGE + " OFFSET " + (((pg - 1) * Config.TBRECORD_PER_PAGE)));
int i = 0;
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;
outBuf.Append("<tr>");
outBuf.Append("<th scope=\"row\" abbr=\"Model\" " + (sumPayable > 0 ? bgclass[i % 2, 0] : bgcolor) + ">" + payment._btsCourseID + "</th>");
outBuf.Append("<td " + bgclass[i % 2, n] + " align=center ><input type=\"checkbox\" name=\"" + chbname + "\" id=\"" + chbname + "\" />" + "</td>");
outBuf.Append("<td " + bgclass[i % 2, n] + " ><a href=\"CourseManage.aspx?actPage=view&targetID=" + payment._courseID + "\" >" + payment._courseName + "</a></td>");
outBuf.Append("<td " + bgclass[i % 2, n] + " align=center >" + PaidGroup.GetPaidGroupID(payment._paidGroupID) + "</td>");
outBuf.Append("<td " + bgclass[i % 2, n] + " align=center ><b>" + StringUtil.Int2StrComma(payment._sumAllCost) + "</b></td>");
outBuf.Append("<td " + bgclass[i % 2, n] + " align=center ><b><font color=blue>" + StringUtil.Int2StrComma(payment._sumMaxPayable) + "</font></b></td>");
outBuf.Append("<td " + bgclass[i % 2, n] + " align=center ><b><font color=red>" + StringUtil.Int2StrComma(payment._sumPaidCost) + "</font></b></td>");
outBuf.Append("<td " + bgclass[i % 2, 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[i % 2, 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>
}
protected void DoListRoom(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=\"specalt\"", "class=\"alt\"" } };
listRoom = new List<Room>();
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
string whereSQL = Room.GetQSearchSQL(searchStr);
if (whereSQL.Length > 0) whereSQL = " WHERE " + whereSQL;
int numRec = db.QueryCount("SELECT Count(*) FROM room " + whereSQL);
if (whereSQL.Length > 0) whereSQL = whereSQL + " AND r.branch_id=b.branch_id ";
else whereSQL = " WHERE r.branch_id=b.branch_id ";
OdbcDataReader reader = db.Query("SELECT r.*,b.branch_name as branch_name FROM room r, branch b " + whereSQL +"ORDER BY room_id LIMIT " + Config.TBRECORD_PER_PAGE + " OFFSET " + (((pg-1)*Config.TBRECORD_PER_PAGE)) );
int i = 0;
while (reader.Read())
{
Room room = Room.CreateForm(reader);
string divtxt = Config.URL_PIC_TEACHER + "/" + room._img;
outBuf.Append("<tr>");
outBuf.Append("<th scope=\"row\" abbr=\"Model\" " + bgclass[i % 2, 0] + " align=center valign=top width=100px>" +Room.GetRoomID(room._roomID) + "  </th>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=left><p><b>ห้อง: </b>" + room._name + "</p>");
outBuf.Append("<p><b>สาขา: </b>" + room._branchName + " </p>");
outBuf.Append("<p><b>จำนวนที่นั่ง: </b>" + room._seatNo + " </p>");
outBuf.Append("<p><b>รายละเอียด: </b></p><p>" + room._description.Replace("\r\n","<br>") + " </p>");
outBuf.Append(" </td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center><a href=\"" + Config.URL_PIC_ROOM + "/" + room._img + "\" ><img border=0 width=200px height=150px src=\"" + Config.URL_PIC_ROOM + "/" + room._img + "\" ></a></td>");
outBuf.Append("<td " + bgclass[i % 2, 1] + " align=center> ");
outBuf.Append("<a href=\"javascript:setVal('actPage','edit');setVal('targetID','" + room._roomID + "');doSubmit()\"><img src=\"img/sys/edit.gif\" border=0 alt=\"Edit\"></a> ");
outBuf.Append("<a href=\"javascript:if (confirm('Delete this room?')) { setVal('actPage','delete');setVal('targetID','" + room._roomID + "');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=\"TeacherManage.aspx?pg={0}&qsearch={1}\">{0}</a> ", i.ToString(), searchStr));
}
}
}
protected void DoAddSubmitRoom()
{
try
{
Room r = new Room();
// validate data
r._name = Request["roomname"];
r._branchID = Int32.Parse(Request["branch_id"]);
r._seatNo = Int32.Parse(Request["seat_no"]);
r._description = Request["description"];
r._img = "noimg.jpg";
if (portrait.PostedFile.FileName != "")
{
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_ROOM + "\\" + imgname;
} while (File.Exists(fullpath));
portrait.PostedFile.SaveAs(fullpath);
r._img = imgname;
}
// 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();
r.AddToDB(db);
db.Close();
}
catch (Exception err)
{
errorText = err.Message + err.StackTrace;
Response.Redirect("RoomManage.aspx?actPage=add&errorText=ข้อมูลไม่ถูกต้อง โปรดตรวจสอบอีกครั้ง");
}
}
protected AppUser ValidateLogin(string username, string passwd)
{
List<String> list = GetSystemDriverList();
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
OdbcDataReader reader = null;
try
{
reader = db.Query("SELECT u.*, b.branch_name as branch_name FROM user u, branch b WHERE u.username='******' AND u.branch_id=b.branch_id");
while (reader.Read())
{
AppUser user = AppUser.CreateForm(reader);
if (user._encodedPassword == null) return null;
if (user._encodedPassword.Equals(AppUser.GetMD5Encoded(passwd) ))
return user;
}
return null;
}
catch (Exception e) { Console.WriteLine(e.StackTrace); return null; }
finally { db.Close(reader); }
}
protected String DoSaveDailyAsExcel()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
String prefix = "registration_report_";
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();
//stdTxt.Append(StringUtil.GetExcelEncodingPrefix());
//ลำดับ วันที่ เลขที่ใบเสร็จ นักเรียน โรงเรียน ระดับชั้น รหัสคอร์ส ชื่อคอร์ส ราคา วิธีการชำระ สถานะ หมายเหตุ ชื่อผู้สมัคร
stdTxt.Append("รายงานสรุปยอดการลงทะเบียนประจำวันของสาขา " + branchName + "ตั้งแต่วันที่ " + StringUtil.ConvertYearToEng(startDate, "dd/MM/yyyy") + " ถึง " + StringUtil.ConvertYearToEng(startDate, "dd/MM/yyyy") +"\n");
stdTxt.Append("ลำดับ, วันที่รับชำระเงิน, วันที่สมัคร, รหัสใบเสร็จ, รหัสนักเรียน, ชื่อ-นามสกุล, โรงเรียน, ระดับชั้น,รหัสคอร์ส, ชื่อคอร์ส,วันที่เริ่ม, วันที่สิ้นสุด, ราคาจ่าย, วิธีชำระเงิน, สถานะ, ผู้รับสมัคร, ประเภท \n");
// content
for (int i = 0; i < reg.Length; i++)
{
//numPaidMethod[reg[i]._paidMethod]++;
//sumCostByPaidMethod[reg[i]._paidMethod] += reg[i]._discountedCost;
//sumAllCost += reg[i]._discountedCost;
AppUser theUser = userAllMap[reg[i]._username];
String courseStart = "-";
String courseEnd = "-";
if (reg[i]._courseType == "คอร์สสด")
{
courseStart = reg[i]._courseStart.ToString("dd/MM/yyyy", ci);
courseEnd = reg[i]._courseEnd.ToString("dd/MM/yyyy", ci);
}
stdTxt.Append(i + 1);
stdTxt.Append(", " + reg[i]._paiddate.ToString("dd/MM/yyyy", ci));
stdTxt.Append(", " + reg[i]._regisdate.ToString("dd/MM/yyyy HH:mm", ci));
stdTxt.Append(", " + reg[i].GetRegisTransactionID());
stdTxt.Append(", " + Student.GetStudentID(reg[i]._studentID));
stdTxt.Append(", " + StringUtil.AddCSVDQuote(reg[i]._studentFirstname + " " + reg[i]._studentSurname));
stdTxt.Append(", " + StringUtil.AddCSVDQuote(reg[i]._studentSchool));
stdTxt.Append(", " + StringUtil.AddCSVDQuote(StringUtil.ConvertEducateLevel(reg[i]._studentLevel)));
stdTxt.Append(", " + reg[i]._btsCourseID );
stdTxt.Append(", " + StringUtil.AddCSVDQuote(reg[i]._courseName));
stdTxt.Append(", " + courseStart);
stdTxt.Append(", " + courseEnd);
stdTxt.Append(", " + reg[i]._discountedCost);
stdTxt.Append(", " + Registration.GetPaidMethodText(reg[i]._paidMethod.ToString()));
stdTxt.Append(", " + Registration.GetStatusText(reg[i]._status));
stdTxt.Append(", " + StringUtil.AddCSVDQuote(theUser._firstname + " " + theUser._surname));
stdTxt.Append(", " + reg[i]._courseType);
stdTxt.Append("\n");
if (i % 100 == 99)
{
writer.Write(stdTxt);
stdTxt = new StringBuilder();
}
}
if (stdTxt.Length > 0)
{
writer.Write(stdTxt);
}
writer.Close();
return urlPath;
}
protected void LoadData(string startDateString, string endDateString, string paidMethod, string branchRegisedID, String branchID, String username, int status)
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
// Load branchlist
branchList = Branch.LoadListFromDBCustom(db, "SELECT * from branch ORDER BY branch_id ");
// Load userList all
AppUser[] userListAll = AppUser.LoadListFromDB(db, "");
userAllMap = new Dictionary<string, AppUser>();
foreach (AppUser aUser in userListAll) {
userAllMap.Add(aUser._username, aUser);
}
// Load userList for this login
AppUser loginUser = (AppUser)Session[SessionVar.USER];
String userQueryClause = "";
if (loginUser._roleId == Role.ROLE_MANAGEMENT)
{
userQueryClause = " WHERE role_id >= " + Role.ROLE_MANAGEMENT;
}
else if (loginUser._roleId == Role.ROLE_FRONTSTAFF)
{
userQueryClause = " WHERE user_id = " + loginUser._userId;
}
userList = AppUser.LoadListFromDB(db, userQueryClause + " order by firstname");
try
{
string[] s = startDateString.Split('/');
startDate = new DateTime(Int32.Parse(s[2]), Int32.Parse(s[1]), Int32.Parse(s[0]));
}
catch (Exception e)
{
startDate = DateTime.Today;
}
try
{
string[] s = endDateString.Split('/');
endDate = new DateTime(Int32.Parse(s[2]), Int32.Parse(s[1]), Int32.Parse(s[0]));
endDate = endDate.AddHours(23).AddMinutes(59).AddSeconds(59);
}
catch (Exception e)
{
endDate = DateTime.Today.AddHours(23).AddMinutes(59).AddSeconds(59);
}
// Get branch name
if (branchRegisedID.Equals("0"))
{
branchName = "ทุกสาขา";
}
else
{
Branch b = new Branch();
b.LoadFromDB(db, " branch_id=" + branchRegisedID);
branchName = b._branchName;
}
// Filter user
if (loginUser._roleId > Role.ROLE_MANAGEMENT)
{
if (username.Equals("all"))
{
username = loginUser._username;
}
}
// construct room list for the branch
string roomList = "";
if (!branchID.Equals("0"))
{
string roomSQL = "SELECT room_id FROM room where branch_id=" + branchID;
Room[] rooms = Room.LoadListFromDBCustom(db, roomSQL);
if (rooms.Length > 0)
{
foreach (Room r in rooms)
{
roomList = roomList + "," + r._roomID;
}
roomList = "( " + roomList.Substring(1) + ")";
}
}
string selectSQl = "SELECT rg.*, b.branch_code, s.firstname as student_firstname, s.surname as student_surname, s.school as student_school, s.level as student_level, c.bts_course_id as bts_course_id, c.course_name as course_name, c.course_type as course_type, c.category as course_category, c.start_date as start_date, c.end_date as end_date "
+ " FROM registration rg, student s, course c, branch b ";
string whereSQL = " WHERE rg.student_id=s.student_id AND rg.course_id=c.course_id "
+ " AND rg.status=" + status
+ " AND rg.regis_date between '" + startDate.ToString("yyyy/MM/dd HH:mm:ss", ci) + "' and '" + endDate.ToString("yyyy/MM/dd HH:mm:ss", ci) + "' "
+ ((!paidMethod.Equals("-1")) ? " AND rg.paid_method=" + paidMethod : "")
+ ((!branchRegisedID.Equals("0")) ? " AND rg.branch_id=" + branchRegisedID : "")
+ ((!username.Equals("all")) ? " AND rg.username='******'" : "")
+ ((roomList.Length > 0) ? " AND c.room_id in " + roomList : "")
+ " AND rg.branch_id = b.branch_id "
+ " ORDER BY rg.regis_id ";
reg = Registration.LoadListFromDBCustom(db, selectSQl + whereSQL);
db.Close();
/*
public int[] numPaidMethodCancel = new int[Registration.PAID_METHOD.Length];
public int[] sumCostByPaidMethodCancel = new int[Registration.PAID_METHOD.Length];
public int numCancel = 0;
public int sumCancelCost = 0;
*/
// prepare dict cate
for (int j = 0; j < Config.COURSE_CATE.Length; j++)
{
Dictionary<string, Object> map = new Dictionary<string,object>();
map["numByPaidMethod"] = new int[Registration.PAID_METHOD.Length];
map["sumCostByPaidMethod"] = new int[Registration.PAID_METHOD.Length];
map["numAll"] = 0;
map["sumCostAll"] = 0;
map["numByPaidMethodCancel"] = new int[Registration.PAID_METHOD.Length];
map["sumCostByPaidMethodCancel"] = new int[Registration.PAID_METHOD.Length];
map["numAllCancel"] = 0;
map["sumCostAllCancel"] = 0;
String cate = Config.COURSE_CATE[j];
sumByCourseCate.Add(cate, map);
}
for (int i = 0; i < reg.Length; i++)
{
if (reg[i]._status == 0) // normal
{
numPaidMethod[reg[i]._paidMethod]++;
sumCostByPaidMethod[reg[i]._paidMethod] += reg[i]._discountedCost;
numSuccess++;
sumAllCost += reg[i]._discountedCost;
}
else if (reg[i]._status == 1) // cancel
{
numPaidMethodCancel[reg[i]._paidMethod]++;
sumCostByPaidMethodCancel[reg[i]._paidMethod] += reg[i]._discountedCost;
numCancel++;
sumCancelCost += reg[i]._discountedCost;
}
for (int j = 0; j < Config.COURSE_CATE.Length; j++)
{
String cate = Config.COURSE_CATE[j];
Dictionary<string, Object> map = (Dictionary<string,object>)sumByCourseCate[cate];
if (reg[i]._courseCategotry.Equals(cate)) {
int[] _numByPaidMethod = (int[])map["numByPaidMethod"];
int[] _sumCostByPaidMethod = (int[])map["sumCostByPaidMethod"];
int[] _numByPaidMethodCancel = (int[])map["numByPaidMethodCancel"];
int[] _sumCostByPaidMethodCancel = (int[])map["sumCostByPaidMethodCancel"];
if (reg[i]._status == 0) // normal
{
_numByPaidMethod[reg[i]._paidMethod]++;
_sumCostByPaidMethod[reg[i]._paidMethod] += reg[i]._discountedCost;
map["numAll"] = (int)map["numAll"] + 1;
map["sumCostAll"] = (int)map["sumCostAll"] + reg[i]._discountedCost;
}
else if (reg[i]._status == 1) // cancel
{
_sumCostByPaidMethod[reg[i]._paidMethod]++;
_sumCostByPaidMethodCancel[reg[i]._paidMethod] += reg[i]._discountedCost;
map["numAllCancel"] = (int)map["numAllCancel"] + 1;
map["sumCostAllCancel"] = (int)map["sumCostAllCancel"] + reg[i]._discountedCost;
}
}
}
}
}
// TODO: Check duplicated registration
protected void ProcessRegistration(RegisTransaction reg)
{
AppUser user = (AppUser)Session[SessionVar.USER];
// Collect additional variable
// * User information comes from session NOT DB
reg._username = user._username;
reg._paidMethod = Int32.Parse(Request["paid_method"]);
if (Session[SessionVar.BRANCH_SELECTED] == null) {
reg._branchID = user._branchID;
} else {
reg._branchID = Int32.Parse((String)Session[SessionVar.BRANCH_SELECTED]);
user._branchID = reg._branchID;
Session[SessionVar.USER] = user;
}
reg._paiddate = StringUtil.getDate(Request["paid_date"]);
// seat
for (int i = 0; i < Request.Form.AllKeys.Length; i++)
{
if (Request.Form.AllKeys[i].StartsWith("seat_no"))
{
string courseID = Request.Form.AllKeys[i].Substring(7);
string seatNo = Request[Request.Form.AllKeys[i]];
try
{
reg._seatNoMap.Add(courseID, seatNo);
}
catch (Exception ex) { }
continue;
}
if (Request.Form.AllKeys[i].StartsWith("note"))
{
string courseID = Request.Form.AllKeys[i].Substring(4);
string note = Request[Request.Form.AllKeys[i]];
try
{
reg._noteMap.Add(courseID, note);
}
catch (Exception ex) { }
continue;
}
}
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
reg.AddToDB(db);
db.Close();
}
protected Student ProcessWizAddNewStudent()
{
Student t = new Student();
// validate data
t._firstname = Request["firstname"];
t._surname = Request["surname"];
t._nickname = Request["nickname"];
t._citizenID = Request["citizen_id"];
t._sex = Request["sex"];
t._tel = Request["tel1"] + Request["tel2"] + Request["tel3"];
t._tel2 = Request["tel21"] + Request["tel22"] + Request["tel23"];
t._email = Request["email"];
t._addr = Request["addr"];
t._school = Request["school"];
t._level = Int32.Parse(Request["level"]);
t._quiz = Student.EncodeQuizText(Page.Request);
if (Request["birthday"] != null)
{
string[] s = Request["birthday"].Split('/');
t._birthday = new DateTime(Int32.Parse(s[2]) - 543, Int32.Parse(s[1]), Int32.Parse(s[0]));
}
else
{
t._birthday = new DateTime();
}
t._create_date = DateTime.Today;
t._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_STUDENT + "\\" + imgname;
} while (File.Exists(fullpath));
portrait.PostedFile.SaveAs(fullpath);
t._img = imgname;
}
catch (Exception err)
{
errorText = err.Message + err.StackTrace;
return null;
}
}
// Do validation
// Save to DB and read to get student id
// Need to use transaction
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();
db.BeginTransaction(IsolationLevel.ReadCommitted);
// validate
// duplicate citizen id
if (t._citizenID.Length > 0)
{
int count = db.QueryCount("SELECT COUNT(*) FROM student WHERE citizen_id='" + t._citizenID + "'");
if (count > 0)
{
errorText = "รหัสบัตรประชาชน " + t._citizenID + " มีอยู่ในระบบแล้ว";
return null;
}
}
// Save to DB
t.AddToDB(db);
// Get just saved student
Student savedStudent = new Student();
savedStudent.LoadFromDBCustom(db, "SELECT * FROM student ORDER BY student_id DESC LIMIT 1");
db.Commit();
return savedStudent;
}
catch (Exception e)
{
errorText = "พบปัญหาบางประการ ข้อมูลไม่ถูกบันทึก";
return null;
}
finally
{
db.Close();
}
}
