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); }