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