public ActionResult doProcessCommittee() { if (Session["USER"] == null) { return(RedirectToAction("../Account/ManagementLogin")); } ViewBag.ResultMsg = ""; int countOfCommittee = 0; int countOfSuccess = 0; int countOfFail = 0; using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { List <TB_COMMITEE> commitees = db.TB_COMMITEE.OrderBy(c => c.COMMITEE_ID).Include("TB_M_TITLE").ToList(); if (commitees != null) { /* * DELETE TB_COMMITEE_ROOM */ foreach (TB_COMMITEE_ROOM dcmtr in context.TB_COMMITEE_ROOM.ToList()) { context.TB_COMMITEE_ROOM.Remove(dcmtr); } //context.TB_COMMITEE_ROOM.ToList().ForEach(context.TB_COMMITEE_ROOM.DeleteObject); countOfCommittee = context.TB_COMMITEE_ROOM.Count(); var varRoom = from a in db.TB_ROOM orderby a.ROOM_FOR_LEVEL, a.ROOM_NUMBER select a; List <TB_ROOM> rooms = varRoom.ToList(); if (rooms != null) { int index = 0; foreach (TB_ROOM room in rooms) { for (int i = 0; i < Convert.ToInt16(room.ROOM_COMMITTEE_COUNT); i++) { if (index < commitees.Count) { TB_COMMITEE_ROOM cr = new TB_COMMITEE_ROOM(); cr.COMMITEE_ID = commitees[index].COMMITEE_ID; cr.COMMITEE_ROOM_ID = room.ROOM_ID; context.TB_COMMITEE_ROOM.Add(cr); countOfSuccess++; index++; } else { //countOfFail++; //ViewBag.ResultMsg = "จำนวนคนไม่พอกับจำนวนห้อง !"; } } } } context.SaveChanges(); } } ViewBag.ResultMsg = "ประมวลผลกรรมการคุมสอบเรียบร้อยแล้ว (จำนวนกรรมการทั้งหมด " + countOfCommittee + " คน <br> สำเร็จ " + countOfSuccess + " รายการ <br> ไม่สำเร็จ " + countOfFail + " รายการ)"; return(View("ProcessCommittee")); }
public ActionResult ManagementLogin(LoginModel model, string returnUrl) { if (ModelState.IsValid) { using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { var user = context.TB_USER.FirstOrDefault(x => x.USERNAME == model.UserName && x.PASSWORD == model.Password); if (user == null) { ModelState.AddModelError("LogOnError", "The user name or password provided is incorrect."); } else { //FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe); if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/") && !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\")) { return(Redirect(returnUrl)); } else { //update lasted login time //user.LASTED_LOGIN = DateTime.Now; //context.ObjectStateManager.ChangeObjectState(user, System.Data.EntityState.Modified); var _update = db.TB_USER.FirstOrDefault(f => f.ID == user.ID); if (_update != null) { _update.ROLE_ID = user.ROLE_ID; _update.USERNAME = user.USERNAME; _update.PASSWORD = user.PASSWORD; _update.STATUS = user.STATUS; _update.CREATE_BY = user.CREATE_BY; _update.EMAIL = user.EMAIL; } context.SaveChanges(); //Redirect to default page Session["USER"] = user; Session["ROLE"] = user.TB_ROLE.NAME; String[] rolPermission = new String[user.TB_ROLE.TB_ROLE_PERMISSION.Count]; int index = 0; foreach (TB_ROLE_PERMISSION item in user.TB_ROLE.TB_ROLE_PERMISSION.ToList()) { rolPermission[index] = item.PERMISSION_CODE; index++; } Session["ROLE_PERMISSION"] = rolPermission; return(RedirectToAction("Index", "Management")); } } } } // If we got this far, something failed, redisplay form return(View(model)); }
public ActionResult deleteRound2Data() { if (Session["USER"] == null) { return(RedirectToAction("../Account/ManagementLogin")); } using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { foreach (TB_SCORE_ROUND_2 r1 in context.TB_SCORE_ROUND_2.ToList()) { context.TB_SCORE_ROUND_2.Remove(r1); } //context.TB_SCORE_ROUND_2.ToList().ForEach(context.TB_SCORE_ROUND_2.DeleteObject); context.SaveChanges(); } ViewBag.ResultMsg = "ล้างข้อมูลรอบเจียรไนเพชรเรียบร้อยแล้ว"; return(View("Round1")); }
public ActionResult clearRound21Score(int id) { if (Session["USER"] == null) { return(RedirectToAction("../Account/ManagementLogin")); } using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { Int32 stdCode = Convert.ToInt32(id); TB_SCORE_ROUND_2 score21 = context.TB_SCORE_ROUND_2.Where(s21 => s21.STD_CODE == stdCode).FirstOrDefault(); if (score21 != null) { score21.SCORE_1_11 = 0; score21.SCORE_1_12 = 0; score21.SCORE_1_13 = 0; score21.SCORE_1_14 = 0; score21.SCORE_1_15 = 0; score21.SCORE_1_21 = 0; score21.SCORE_1_22 = 0; score21.SCORE_1_23 = 0; score21.SCORE_1_24 = 0; score21.SCORE_1_25 = 0; score21.SCORE_1_31 = 0; score21.SCORE_1_32 = 0; score21.SCORE_1_33 = 0; score21.SCORE_1_34 = 0; score21.SCORE_1_35 = 0; context.SaveChanges(); ViewBag.ResultMsg = "ล้างข้อมูลเรียบร้อยแล้ว"; } else { ViewBag.ResultErrorMsg = "ยังไม่มีการบันทึกข้อมูล ไม่สามารถลบได้"; } } return(View("Round2_1", new ScoreRound21Model())); }
private void Import_To_Grid(string FilePath, string Extension, string isHDR) { logger.Debug(String.Format("####### --- Begin Start Load File at {0} --- ####", DateTime.Now)); int rejected = 0; List <TB_SCORE_ROUND_1> stdList = new List <TB_SCORE_ROUND_1>(); List <TB_EXAM_ABSENT> stdAppsentList = new List <TB_EXAM_ABSENT>(); List <TB_CONCERN> stdConcerntList = new List <TB_CONCERN>(); using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { context.Configuration.AutoDetectChangesEnabled = false; using (CsvFileReader reader = new CsvFileReader(FilePath)) { CsvRow row = new CsvRow(); while (reader.ReadRow(row)) { try { int stdCode = ((row[0] == null) ? 0 : (row[0].Equals("")) ? 0 : Convert.ToInt32(row[0])); if (!stdList.Any(x => x.STD_CODE == stdCode)) { TB_SCORE_ROUND_1 round1 = new TB_SCORE_ROUND_1(); round1.STD_CODE = stdCode; round1.ROUND_SCORE = ((row[1] == null) ? 0 : (row[1].Equals("")) ? 0 : Convert.ToInt32(row[1])); round1.PRIZE_ID = 0; stdList.Add(round1); //บันทึกขาดสอบ if (row[2].ToString().Equals("F") && round1.ROUND_SCORE == 0) { if (!stdAppsentList.Any(x => x.STD_CODE == stdCode)) { TB_EXAM_ABSENT tb_exam_absent = new TB_EXAM_ABSENT(); tb_exam_absent.STD_CODE = stdCode; stdAppsentList.Add(tb_exam_absent); } } //สงสัยว่าผิดระดับชั้น if (row[3].ToString().Equals("T")) { if (!stdConcerntList.Any(x => x.STD_CODE == stdCode)) { TB_CONCERN concern = new TB_CONCERN(); concern.STD_CODE = stdCode; stdConcerntList.Add(concern); } } } else { rejected++; logger.Debug("Reject>>duplicate student code:" + stdCode); } } catch (Exception ex) { logger.Error(ex.Message); } } } //Begin Insert foreach (TB_EXAM_ABSENT _val in stdAppsentList) { context.TB_EXAM_ABSENT.Add(_val); } context.SaveChanges(); foreach (TB_CONCERN _val in stdConcerntList) { context.TB_CONCERN.Add(_val); } context.SaveChanges(); int index = 1; foreach (TB_SCORE_ROUND_1 _val in stdList) { context.TB_SCORE_ROUND_1.Add(_val); index++; if (index % 200 == 0) { context.SaveChanges(); } } context.SaveChanges(); } logger.Debug("######## SUMMARY ########"); logger.Debug(String.Format("Total {0} (s),Reject {1} (s)>>>> Remain {2} (s)", stdList.Count, rejected, stdList.Count - rejected)); logger.Debug(String.Format("####### --- Finish Load File at {0} --- ####", DateTime.Now)); }
private void Import_To_Grid(string FilePath, string Extension, string isHDR) { string conStr = ""; switch (Extension) { case ".xls": //Excel 97-03 conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; break; case ".xlsx": //Excel 07 conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString; break; } conStr = String.Format(conStr, FilePath, isHDR); OleDbConnection connExcel = new OleDbConnection(conStr); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); connExcel.Close(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; oda.Fill(dt); //String xx = dt.Rows[0][0].ToString(); using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { //context.TB_COMMITEE.ToList().ForEach(context.TB_COMMITEE); foreach (TB_COMMITEE deleteCmt in context.TB_COMMITEE.ToList()) { context.TB_COMMITEE.Remove(deleteCmt); } for (int i = 1; i < dt.Rows.Count; i++) { TB_COMMITEE commitee = new TB_COMMITEE(); commitee.COMMITEE_TITLE_ID = 0; commitee.COMMITEE_NAME = dt.Rows[i][1].ToString(); commitee.COMMITEE_SURNAME = dt.Rows[i][2].ToString(); commitee.COMMITEE_PHONE = dt.Rows[i][3].ToString(); commitee.COMMITEE_STATUS = dt.Rows[i][4].ToString(); context.TB_COMMITEE.Add(commitee); } context.SaveChanges(); } connExcel.Close(); //Bind Data to GridView //GridView1.Caption = Path.GetFileName(FilePath); //GridView1.DataSource = dt; //GridView1.DataBind(); }
public ActionResult Special(ProcessStudentModel model) { if (Session["USER"] == null) { return(RedirectToAction("../Account/ManagementLogin")); } ViewBag.ResultMsg = ""; using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { List <TB_APPLICATION_STUDENT> students = null; var _result = from a in db.TB_APPLICATION_STUDENT orderby a.STD_LEVEL_ID, a.STD_ID select a; var sitPlaned = from b in db.TB_STUDENT_SEAT select b.STUDENT_ID; var stdCodes = from b in db.TB_STUDENT_SEAT orderby b.STUDENT_CODE ascending select b; int lastLevel1 = 0; int lastLevel2 = 0; int lastLevel3 = 0; int lastLevel4 = 0; int lastLevel5 = 0; lastLevel1 = getLastStudentCode(stdCodes.ToList(), 1); lastLevel2 = getLastStudentCode(stdCodes.ToList(), 2); lastLevel3 = getLastStudentCode(stdCodes.ToList(), 3); lastLevel4 = getLastStudentCode(stdCodes.ToList(), 4); lastLevel5 = getLastStudentCode(stdCodes.ToList(), 5); //นำข้อมูลรายชื่อนักเรียนที่ยังไม่ได้จัดมาเก็บไว้ใน tmp var tmp = from x in _result where !sitPlaned.Contains(x.STD_ID) orderby x.STD_LEVEL_ID, x.STD_ID select x; students = tmp.ToList(); if (students.Count > 0) { var varRoom = from a in db.TB_ROOM where a.ROOM_FOR_LEVEL == 6 orderby a.ROOM_FOR_LEVEL, a.ROOM_ID select a; List <TB_ROOM> rooms = varRoom.ToList(); if (rooms != null) { int studentIndex = 0; String[] rowPrefix = { "A", "B", "C", "D", "E", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; foreach (TB_ROOM room in rooms) { int sitIndex = 1; String[] rows = room.ROOM_ROW.ToUpper().Split('X'); if (rows.Length == 2) { int col = Convert.ToInt16(rows[0]); //c int row = Convert.ToInt16(rows[1]); //r // 1. วนแต่ละแถวเพื่อเรียงลำดับเลขที่นั่งสอบ for (int c = 0; c < col; c++) { int level = 0; switch (c + 1) { case 1: level = Convert.ToInt16(room.ROW_1); break; case 2: level = Convert.ToInt16(room.ROW_2); break; case 3: level = Convert.ToInt16(room.ROW_3); break; case 4: level = Convert.ToInt16(room.ROW_4); break; case 5: level = Convert.ToInt16(room.ROW_5); break; case 6: level = Convert.ToInt16(room.ROW_6); break; case 7: level = Convert.ToInt16(room.ROW_7); break; case 8: level = Convert.ToInt16(room.ROW_8); break; case 9: level = Convert.ToInt16(room.ROW_9); break; case 10: level = Convert.ToInt16(room.ROW_10); break; case 11: level = Convert.ToInt16(room.ROW_11); break; case 12: level = Convert.ToInt16(room.ROW_12); break; } for (int r = 0; r < row; r++) { if (studentIndex < students.Count) { if (level == students[studentIndex].STD_LEVEL_ID) { //get last index logger.Debug(c + 1 + "," + r + "=" + students[studentIndex].STD_ID + "," + room.ROOM_NUMBER + "\n"); TB_STUDENT_SEAT ss = new TB_STUDENT_SEAT(); ss.STUDENT_ID = students[studentIndex].STD_ID; switch (level) { case 1: ss.STUDENT_CODE = Convert.ToInt32(String.Format("{0}{1}", students[studentIndex].STD_LEVEL_ID, (lastLevel1 + sitIndex).ToString("0000"))); break; case 2: ss.STUDENT_CODE = Convert.ToInt32(String.Format("{0}{1}", students[studentIndex].STD_LEVEL_ID, (lastLevel2 + sitIndex).ToString("0000"))); break; case 3: ss.STUDENT_CODE = Convert.ToInt32(String.Format("{0}{1}", students[studentIndex].STD_LEVEL_ID, (lastLevel3 + sitIndex).ToString("0000"))); break; case 4: ss.STUDENT_CODE = Convert.ToInt32(String.Format("{0}{1}", students[studentIndex].STD_LEVEL_ID, (lastLevel4 + sitIndex).ToString("0000"))); break; case 5: ss.STUDENT_CODE = Convert.ToInt32(String.Format("{0}{1}", students[studentIndex].STD_LEVEL_ID, (lastLevel5 + sitIndex).ToString("0000"))); break; } ss.ROOM_ID = room.ROOM_ID; ss.SIT_NUMBER_PREFIX = rowPrefix[c]; ss.SIT_NUMBER = (r + 1); context.TB_STUDENT_SEAT.Add(ss); } else { continue; } } sitIndex++; studentIndex++; } } } else { logger.Debug(room.ROOM_NUMBER + ":ข้อมูล COLXROW ใน DB ของห้องไม่ถุกต้อง"); } } context.SaveChanges(); } else { logger.Debug("ไม่พบข้อมุลห้องว่าง"); } } else { ViewBag.ResultMsg = "ไม่พบข้อมูลนักเรียนที่ยังไม่ได้ที่นั่งสอบ"; return(View(model)); } } ViewBag.ResultMsg = "ประมวลผลที่นั่งสอบเรียบร้อยแล้ว"; return(View(model)); }
public ActionResult Index(ProcessStudentModel model) { if (Session["USER"] == null) { return(RedirectToAction("../Account/ManagementLogin")); } StringBuilder noRoomStd = new StringBuilder(); ViewBag.ResultMsg = ""; //int countOfCommittee = 0; int countOfSuccess = 0; //int countOfFail = 0; using (ChinaPhet10Entities context = new ChinaPhet10Entities()) { List <TB_APPLICATION_STUDENT> students = null; int studentLevel = Convert.ToInt16(model.StudentLevel); if (!String.IsNullOrWhiteSpace(model.StudentLevel)) { var varStudent = from a in db.TB_APPLICATION_STUDENT where a.STD_LEVEL_ID == studentLevel && a.STD_APPROVED_STATUS == 3 orderby a.STD_LEVEL_ID, a.STD_ID select a; students = varStudent.ToList(); } if (students != null) { /* * DELETE TB_STUDENT_SEAT BY LEVEL */ foreach (TB_APPLICATION_STUDENT std in students) { if (std.TB_STUDENT_SEAT != null) { TB_STUDENT_SEAT studentSeat = context.TB_STUDENT_SEAT.Where(s => s.STUDENT_CODE == std.TB_STUDENT_SEAT.STUDENT_CODE).FirstOrDefault(); context.TB_STUDENT_SEAT.Remove(studentSeat); } } var varRoom = from a in db.TB_ROOM where a.ROOM_FOR_LEVEL == studentLevel orderby a.ROOM_FOR_LEVEL, a.ROOM_ID select a; List <TB_ROOM> rooms = varRoom.ToList(); if (rooms != null) { int index = 0; int studentIndex = 1; int examSitIndex = 1; int tmpLevel = Convert.ToInt16(rooms[0].ROOM_FOR_LEVEL); String[] rowPrefix = { "A", "B", "C", "D", "E", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; foreach (TB_ROOM room in rooms) { if (tmpLevel != room.ROOM_FOR_LEVEL) { studentIndex = 1; examSitIndex = 1; } String[] rows = room.ROOM_ROW.ToUpper().Split('X'); if (rows.Length == 2) { int width = Convert.ToInt16(rows[0]); int height = Convert.ToInt16(rows[1]); // 1. วนแต่ละแถวเพื่อเรียงลำดับเลขที่นั่งสอบ for (int w = 0; w < width; w++) { for (int h = 0; h < height; h++) { if (index < students.Count) { TB_STUDENT_SEAT ss = new TB_STUDENT_SEAT(); ss.STUDENT_ID = students[index].STD_ID; ss.STUDENT_CODE = Convert.ToInt32(String.Format("{0}{1}", students[index].STD_LEVEL_ID, studentIndex.ToString("0000"))); ss.ROOM_ID = room.ROOM_ID; ss.SIT_NUMBER_PREFIX = rowPrefix[w]; ss.SIT_NUMBER = (h + 1);//Convert.ToInt32(String.Format("{0}{1}", room.ROOM_FOR_LEVEL, examSitIndex.ToString("0000"))); context.TB_STUDENT_SEAT.Add(ss); index++; studentIndex++; examSitIndex++; countOfSuccess++; } else { Console.WriteLine("จำนวนคนไม่พอกับจำนวนห้อง !"); } } } } else { //generate row error. Console.WriteLine(); } } } context.SaveChanges(); } } List <TB_M_LEVEL> tbMLevel = db.TB_M_LEVEL.Where(l => l.LEVEL_ID > 0).ToList(); ViewBag.StudentLevel = new SelectList(tbMLevel, "LEVEL_ID", "LEVEL_NAME_TH", model.StudentLevel); ViewBag.ResultMsg = "ประมวลผลที่นั่งสอบเรียบร้อยแล้ว";//+ ("จำนวนนักเรียนทั้งหมด " + students. + " คน <br> สำเร็จ " + countOfSuccess + " รายการ <br> ไม่สำเร็จ " + countOfFail + " รายการ)"; return(View(model)); }