public static bool CreateForm(OdbcDataReader reader, Branch branch)
{
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 "branch_id": branch._branchID = reader.GetInt32(i);
break;
case "branch_name": branch._branchName = reader.GetString(i);
break;
case "branch_code": branch._branchCode = reader.GetString(i);
break;
case "address": branch._address = reader.GetString(i);
break;
case "tel": branch._tel = reader.GetString(i);
break;
case "img": branch._img = reader.GetString(i);
break;
case "supervisor": branch._supervisor = reader.GetString(i);
break;
}
}
return reader.HasRows;
}
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 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;
}
}
}
}
}
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();
}
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;
}
public static Branch[] LoadListFromDBCustom(DBManager db, string sqlAll)
{
OdbcDataReader reader = db.Query(sqlAll);
LinkedList<Branch> list = new LinkedList<Branch>();
while (reader.Read())
{
list.AddLast(Branch.CreateForm(reader));
}
Branch[] entities = new Branch[list.Count];
int i = 0;
foreach (Branch b in list)
{
entities[i++] = b;
}
return entities;
}
public static Branch CreateForm(OdbcDataReader reader)
{
Branch branch = new Branch();
Branch.CreateForm(reader, branch);
return branch;
}
public void CreateTransactionCode(DBManager db, DateTime regisdate)
{
// format
// 1. operating branch code - 2 digits
// 2. paid method C/K/D/T/V
// 3. yyMM 1302
// 5. number of transaction this month XXX
Branch branch = new Branch();
branch.LoadFromDB(db, "branch_id=" + this._branchID);
// find the number of transaction for the user on this month
int numRegisted = GetTransationCountThisMonth(db, _paidMethod);
StringBuilder buf = new StringBuilder(40);
buf.Append(branch._branchCode);
buf.Append(PAID_METHOD_TRANCODE[_paidMethod]);
buf.Append(regisdate.Year.ToString().Substring(2)).Append(StringUtil.FillString(regisdate.Month.ToString(), "0", 2, true));
buf.Append(StringUtil.FillString((numRegisted + 1).ToString(), "0", 3, true));
// set
this._transactionCode = buf.ToString();
//
}
public bool LoadBranch(DBManager db)
{
if (_branchID <= 0) return false;
_branch = new Branch();
return _branch.LoadFromDB(db, " branch_id=" + _branchID);
}
// here is branch that make registration not branch of course
public bool LoadBranch(DBManager db)
{
if (_branchID <= 0) return false;
_branch = new Branch();
bool result = _branch.LoadFromDB(db, " branch_id=" + _branchID);
_branchCode = _branch._branchCode;
return result;
}
public static StringBuilder PrintCard(DBManager db, int regisID)
{
StringBuilder outBuf = new StringBuilder();
Registration theReg = new Registration();
theReg.LoadFromDB(db, " regis_id=" + regisID);
theReg.LoadCourse(db);
theReg.LoadStudent(db);
Branch branch = new Branch();
branch.LoadFromDB(db, " branch_id=" + theReg._branchID);
AppUser authorizer = new AppUser();
authorizer.LoadFromDB(db, " username='******'");
// Load all registration in the same transaction
Registration[] reg = Registration.LoadListFromDBIncludeCourseHelper(db, " r.transaction_id="+theReg._transactionID + " AND r.branch_id="+theReg._branchID);
// Generate HTML content
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\registration_print_card.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
int[] rowH = { 22, 20, 20, 20, 20, 20, 20 };
StringBuilder courseCalendar = new StringBuilder();
for (int i = 0; i < reg.Length; i++)
{
reg[i].LoadCourse(db);
Branch b = reg[i]._course.LoadBranchInfo(db);
courseCalendar.Append("<tr height=\"24px\">");
courseCalendar.Append("<td width=\"38px\" align=left><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">   " + reg[i]._btsCourseID + "</font></td>");
courseCalendar.Append("<td width=\"100px\" align=left><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\"> " + reg[i]._courseShortName + "</font></td>");
courseCalendar.Append("<td width=\"17px\" align=left><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + b._branchCode + "</font></td>");
courseCalendar.Append("<td width=\"48px\"><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + StringUtil.ConvertYearToEng(reg[i]._course._startdate, "dd/MM/yy") + "</font></td>");
courseCalendar.Append("<td width=\"25px\"><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + reg[i]._course._dayOfWeek+"</font></td>");
courseCalendar.Append("<td width=\"70px\"><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + reg[i]._course._opentime + "</font></td>");
courseCalendar.Append("</tr>");
}
/*
<tr height="10px"><td colspan=2></td></tr>
<tr><td width="10px" align="right"> </td><td><font size=2>คอร์ส: {4}</font></td></tr>
<tr><td align="right"> </td><td><font size=2>ชื่อคอร์ส: {5} </font></td></tr>
<tr><td align="right"> </td><td><font size=2>วันที่เริ่ม: {6}</font></td></tr>
<tr><td align="right"> </td><td><font size=2>เวลา: {7}</font></td></tr>
<tr><td align="right"> </td><td><font size=2>หนังสือ: </font></td></tr>
*/
String htmlContent =
String.Format(templateContent
, theReg._student._firstname + " " + theReg._student._surname
, Student.GetStudentID(theReg._student._studentID)
, StringUtil.ConvertYearToEng(theReg._regisdate, "dd/MM/yyyy")
, authorizer._firstname + " " + authorizer._surname
, reg[0].GetRegisTransactionID()
, courseCalendar.ToString()
);
outBuf.Append(htmlContent);
return outBuf;
}
protected void Page_Load(object sender, EventArgs e)
{
// DEBUG
if (Config.AUTO_LOGIN)
{
if (Session[SessionVar.USER] == null)
{
AppUser auser = new AppUser();
auser._username = "******";
auser._firstname = "Weerawat";
auser._surname = "Seetalalai";
auser._roleId = 1;
auser._branchID = 1;
auser._branchName = "BTS สีลม";
Session[SessionVar.USER] = auser;
// preload all branches into Session
Branch[] b = new Branch[2];
b[0] = new Branch();
b[0]._branchID = 1;
b[0]._branchName = "BTS สีลม";
b[1] = new Branch();
b[1]._branchID = 2;
b[1]._branchName = "BTS สยาม";
Session["BRANCHES"] = b;
}
}
else
{
String loginPage = "AppLogin.aspx";
if (Session[SessionVar.USER] == null)
{
Response.Write("<br><font color=red size=3>คุณยังไม่ได้ทำการล็อกอินเข้าระบบ </font>");
Response.Write("<br><a href=\"" + "AppLogin.aspx" + "\">ไปหน้าล็อกอิน</a>");
Response.Redirect(loginPage + "?message=คุณยังไม่ได้ทำการล็อกอินเข้าระบบ");
}
}
String noRightPage = redirectPage + "?backPage=" + Request.UrlReferrer;
AppUser user = (AppUser)Session[SessionVar.USER];
if (user == null)
{
Response.Redirect(noRightPage);
/*
string attName = "redirectPage";
if (Context.Items.Contains(attName))
{
if (Context.Items[attName] != null)
{
redirectPage = (string)Context.Items[attName];
Response.Redirect(redirectPage);
}
}
*/
}
if (checkRight.ToUpper().Equals("TRUE"))
{
int idxAppName = Request.Path.Substring(1).IndexOf("/");
string right = Request.Path.Substring(idxAppName + 2);
if (!Authorizer.Verify(user._roleId, right, Request["actPage"]))
{
Response.Redirect(noRightPage);
}
}
}
public static StringBuilder PrintReceipt(DBManager db, Registration theReg, string title)
{
StringBuilder outBuf = new StringBuilder();
Branch branch = theReg._branch;
AppUser authorizer = new AppUser();
authorizer.LoadFromDB(db, " username='******'");
// Load all course registered in the same transaction
String sql = "SELECT rg.*,c.course_name as course_name "
+ " FROM registration rg, course c "
+ " WHERE rg.course_id=c.course_id AND transaction_id=" + theReg._transactionID + " AND branch_id=" + theReg._branchID + " ORDER BY regis_id ";
Registration[] regCourses = Registration.LoadListFromDBCustom(db, sql);
// load branch code
regCourses[0].LoadBranch(db);
// Generate HTML content
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\registration_print_receipt.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
StringBuilder courseTxt = new StringBuilder();
int sumFullCost = 0;
int sumDiscountedCost = 0;
for (int i = 0; i < regCourses.Length; i++)
{
regCourses[i].LoadCourse(db);
Branch b = regCourses[i]._course.LoadBranchInfo(db);
sumFullCost += regCourses[i]._fullCost;
sumDiscountedCost += regCourses[i]._discountedCost;
String startDateInfo = "-";
String endDateInfo = "-";
if (regCourses[i]._courseType == "คอร์สสด")
{
startDateInfo = StringUtil.ConvertYearToEng(regCourses[i]._course._startdate, "dd/MM/yyyy");
endDateInfo = StringUtil.ConvertYearToEng(regCourses[i]._course._enddate, "dd/MM/yyyy");
}
courseTxt.Append("<tr>");
courseTxt.Append("<td align=center><font size=2>" + regCourses[i]._course._btsCourseID + "</font></td>");
courseTxt.Append("<td><font size=1>" + regCourses[i]._courseName + "</font></td>");
courseTxt.Append("<td align=center><font size=2>" + startDateInfo + "</font></td>");
courseTxt.Append("<td align=center><font size=2>" + endDateInfo + "</font></td>");
courseTxt.Append("<td align=center><font size=2>" + regCourses[i]._course._opentime + "</font></td>");
courseTxt.Append("<td align=center><font size=2>" + StringUtil.Int2StrComma(regCourses[i]._fullCost) + "</font></td>");
courseTxt.Append("<td align=center><font size=2>" + StringUtil.Int2StrComma(regCourses[i]._fullCost - regCourses[i]._discountedCost) + "</font></td>");
courseTxt.Append("<td align=center><font size=2>" + b._branchCode + "</font></td>");
courseTxt.AppendLine("</tr>");
}
// paid method
StringBuilder paidMethodTxt = new StringBuilder();
for (int i = 0; i < PAID_METHOD.Length; i++)
{
if (theReg._paidMethod == i)
{
paidMethodTxt.Append(" [√]");
}
else
{
paidMethodTxt.Append(" [  ]");
}
paidMethodTxt.Append(GetPaidMethodText(i.ToString()));
}
String htmlContent =
String.Format(templateContent
, theReg.GetRegisTransactionID()
, branch._branchName
, StringUtil.ConvertYearToEng(theReg._regisdate, "dd/MM/yyyy HH:mm")
, Student.GetStudentID(theReg._student._studentID)
, theReg._student._firstname + " " + theReg._student._surname
, theReg._student._school
, StringUtil.ConvertEducateLevel(theReg._student._level)
, theReg._student.GetTel()
, courseTxt.ToString()
, paidMethodTxt.ToString()
, StringUtil.Int2StrComma(sumFullCost - sumDiscountedCost)
, StringUtil.Int2StrComma(sumDiscountedCost)
, authorizer._firstname + " " + authorizer._surname
, title
, StringUtil.ConvertYearToEng(theReg._paiddate, "dd/MM/yyyy")
);
outBuf.Append(htmlContent);
return(outBuf);
}
public static StringBuilder PrintCard(DBManager db, int regisID)
{
StringBuilder outBuf = new StringBuilder();
Registration theReg = new Registration();
theReg.LoadFromDB(db, " regis_id=" + regisID);
theReg.LoadCourse(db);
theReg.LoadStudent(db);
Branch branch = new Branch();
branch.LoadFromDB(db, " branch_id=" + theReg._branchID);
AppUser authorizer = new AppUser();
authorizer.LoadFromDB(db, " username='******'");
// Load all registration in the same transaction
Registration[] reg = Registration.LoadListFromDBIncludeCourseHelper(db, " r.transaction_id=" + theReg._transactionID + " AND r.branch_id=" + theReg._branchID);
// Generate HTML content
TextReader reader = new StreamReader(Config.PATH_APP_ROOT + "\\template\\registration_print_card.htm");
String templateContent = reader.ReadToEnd();
reader.Close();
int[] rowH = { 22, 20, 20, 20, 20, 20, 20 };
StringBuilder courseCalendar = new StringBuilder();
for (int i = 0; i < reg.Length; i++)
{
reg[i].LoadCourse(db);
Branch b = reg[i]._course.LoadBranchInfo(db);
courseCalendar.Append("<tr height=\"24px\">");
courseCalendar.Append("<td width=\"38px\" align=left><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">   " + reg[i]._btsCourseID + "</font></td>");
courseCalendar.Append("<td width=\"100px\" align=left><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\"> " + reg[i]._courseShortName + "</font></td>");
courseCalendar.Append("<td width=\"17px\" align=left><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + b._branchCode + "</font></td>");
courseCalendar.Append("<td width=\"48px\"><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + StringUtil.ConvertYearToEng(reg[i]._course._startdate, "dd/MM/yy") + "</font></td>");
courseCalendar.Append("<td width=\"25px\"><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + reg[i]._course._dayOfWeek + "</font></td>");
courseCalendar.Append("<td width=\"70px\"><font style=\"font: 10px 'Trebuchet MS', Verdana, Arial, Helvetica, sans-serif;\">" + reg[i]._course._opentime + "</font></td>");
courseCalendar.Append("</tr>");
}
/*
* <tr height="10px"><td colspan=2></td></tr>
* <tr><td width="10px" align="right"> </td><td><font size=2>คอร์ส: {4}</font></td></tr>
* <tr><td align="right"> </td><td><font size=2>ชื่อคอร์ส: {5} </font></td></tr>
* <tr><td align="right"> </td><td><font size=2>วันที่เริ่ม: {6}</font></td></tr>
* <tr><td align="right"> </td><td><font size=2>เวลา: {7}</font></td></tr>
* <tr><td align="right"> </td><td><font size=2>หนังสือ: </font></td></tr>
*/
String htmlContent =
String.Format(templateContent
, theReg._student._firstname + " " + theReg._student._surname
, Student.GetStudentID(theReg._student._studentID)
, StringUtil.ConvertYearToEng(theReg._regisdate, "dd/MM/yyyy")
, authorizer._firstname + " " + authorizer._surname
, reg[0].GetRegisTransactionID()
, courseCalendar.ToString()
);
outBuf.Append(htmlContent);
return(outBuf);
}