public ActionResult ExamList(int?MasterListID, int?i, string JobPosition, string Firstname, string Lastname) { MasterListEntities master = new MasterListEntities(); var item = master.Masterlists.ToList().Where(x => x.EmploymentStatus == "Applicant").ToPagedList(i ?? 1, 10); LeaveForm model = new LeaveForm(); DataTable dtblIQtest = new DataTable(); DataTable dtblQuestions = new DataTable(); DataTable dtblAccountant = new DataTable(); DataTable dtblEssay = new DataTable(); if (MasterListID == null) { return(View(master.Masterlists.ToList().Where(x => x.EmploymentStatus == "Applicant").ToPagedList(i ?? 1, 10))); } using (SqlConnection sqlCon = new SqlConnection(connectionString)) { sqlCon.Open(); string query = "SELECT * FROM IQtest Where ApplicantID = @MasterListID"; SqlDataAdapter sqlDa = new SqlDataAdapter(query, sqlCon); sqlDa.SelectCommand.Parameters.AddWithValue("@MasterListID", MasterListID); sqlDa.Fill(dtblIQtest); string querys = "SELECT * FROM Questions"; SqlDataAdapter sqlDas = new SqlDataAdapter(querys, sqlCon); sqlDas.Fill(dtblQuestions); string queryss = "SELECT * FROM Accountingtest Where ApplicantID = @MasterListID"; SqlDataAdapter sqlDass = new SqlDataAdapter(queryss, sqlCon); sqlDass.SelectCommand.Parameters.AddWithValue("@MasterListID", MasterListID); sqlDass.Fill(dtblAccountant); string querysss = "SELECT * FROM Essay Where ApplicantID = @MasterListID"; SqlDataAdapter sqlDasss = new SqlDataAdapter(querysss, sqlCon); sqlDasss.SelectCommand.Parameters.AddWithValue("@MasterListID", MasterListID); sqlDasss.Fill(dtblEssay); } if (dtblIQtest.Rows.Count >= 1) { ViewBag.IQtest1 = dtblIQtest.Rows[0][2].ToString(); ViewBag.IQtest2 = dtblIQtest.Rows[0][3].ToString(); ViewBag.IQtest3 = dtblIQtest.Rows[0][4].ToString(); ViewBag.IQtest4 = dtblIQtest.Rows[0][5].ToString(); ViewBag.IQtest5 = dtblIQtest.Rows[0][6].ToString(); ViewBag.IQtest6 = dtblIQtest.Rows[0][7].ToString(); ViewBag.IQtest7 = dtblIQtest.Rows[0][8].ToString(); ViewBag.IQtest8 = dtblIQtest.Rows[0][9].ToString(); ViewBag.IQtest9 = dtblIQtest.Rows[0][10].ToString(); ViewBag.IQtest10 = dtblIQtest.Rows[0][11].ToString(); ViewBag.IQtest11 = dtblIQtest.Rows[0][12].ToString(); ViewBag.IQtest12 = dtblIQtest.Rows[0][13].ToString(); ViewBag.IQtest13 = dtblIQtest.Rows[0][14].ToString(); ViewBag.IQtest14 = dtblIQtest.Rows[0][15].ToString(); ViewBag.IQtest15 = dtblIQtest.Rows[0][16].ToString(); ViewBag.IQtest16 = dtblIQtest.Rows[0][17].ToString(); ViewBag.IQtest17 = dtblIQtest.Rows[0][18].ToString(); ViewBag.IQtest18 = dtblIQtest.Rows[0][19].ToString(); ViewBag.IQtest19 = dtblIQtest.Rows[0][20].ToString(); ViewBag.IQtest20 = dtblIQtest.Rows[0][21].ToString(); ViewBag.IQtest21 = dtblIQtest.Rows[0][22].ToString(); ViewBag.IQtest22 = dtblIQtest.Rows[0][23].ToString(); ViewBag.IQtest23 = dtblIQtest.Rows[0][24].ToString(); ViewBag.IQtest24 = dtblIQtest.Rows[0][25].ToString(); ViewBag.IQtest25 = dtblIQtest.Rows[0][26].ToString(); ViewBag.IQtest26 = dtblIQtest.Rows[0][27].ToString(); ViewBag.IQtest27 = dtblIQtest.Rows[0][28].ToString(); ViewBag.IQtest28 = dtblIQtest.Rows[0][29].ToString(); ViewBag.IQtest29 = dtblIQtest.Rows[0][30].ToString(); ViewBag.IQtest30 = dtblIQtest.Rows[0][31].ToString(); ViewBag.Q1 = dtblQuestions.Rows[0][1].ToString(); ViewBag.Q2 = dtblQuestions.Rows[0][2].ToString(); ViewBag.Q3 = dtblQuestions.Rows[0][3].ToString(); ViewBag.Q4 = dtblQuestions.Rows[0][4].ToString(); ViewBag.Q5 = dtblQuestions.Rows[0][5].ToString(); ViewBag.Q6 = dtblQuestions.Rows[0][6].ToString(); ViewBag.Q7 = dtblQuestions.Rows[0][7].ToString(); ViewBag.Q8 = dtblQuestions.Rows[0][8].ToString(); ViewBag.Q9 = dtblQuestions.Rows[0][9].ToString(); ViewBag.Q10 = dtblQuestions.Rows[0][10].ToString(); ViewBag.Q11 = dtblQuestions.Rows[0][11].ToString(); ViewBag.Q12 = dtblQuestions.Rows[0][12].ToString(); ViewBag.Q13 = dtblQuestions.Rows[0][13].ToString(); ViewBag.Q14 = dtblQuestions.Rows[0][14].ToString(); ViewBag.Q15 = dtblQuestions.Rows[0][15].ToString(); ViewBag.Q16 = dtblQuestions.Rows[0][16].ToString(); ViewBag.Q17 = dtblQuestions.Rows[0][17].ToString(); ViewBag.Q18 = dtblQuestions.Rows[0][18].ToString(); ViewBag.Q19 = dtblQuestions.Rows[0][19].ToString(); ViewBag.Q20 = dtblQuestions.Rows[0][20].ToString(); ViewBag.Q21 = dtblQuestions.Rows[0][21].ToString(); ViewBag.Q22 = dtblQuestions.Rows[0][22].ToString(); ViewBag.Q23 = dtblQuestions.Rows[0][23].ToString(); ViewBag.Q24 = dtblQuestions.Rows[0][24].ToString(); ViewBag.Q25 = dtblQuestions.Rows[0][25].ToString(); ViewBag.Q26 = dtblQuestions.Rows[0][26].ToString(); ViewBag.Q27 = dtblQuestions.Rows[0][27].ToString(); ViewBag.Q28 = dtblQuestions.Rows[0][28].ToString(); ViewBag.Q29 = dtblQuestions.Rows[0][29].ToString(); ViewBag.Q30 = dtblQuestions.Rows[0][30].ToString(); if (JobPosition == "Associate Accountant" || JobPosition == "Senior Accountant") { ViewBag.Accountant1 = dtblAccountant.Rows[0][2].ToString(); ViewBag.AccountantS1 = dtblAccountant.Rows[0][14].ToString(); ViewBag.Accountant2 = dtblAccountant.Rows[0][3].ToString(); ViewBag.Accountant31 = dtblAccountant.Rows[0][4].ToString(); ViewBag.AccountantS31 = dtblAccountant.Rows[0][15].ToString(); ViewBag.Accountant32 = dtblAccountant.Rows[0][5].ToString(); ViewBag.AccountantS32 = dtblAccountant.Rows[0][16].ToString(); ViewBag.Accountant33 = dtblAccountant.Rows[0][6].ToString(); ViewBag.AccountantS33 = dtblAccountant.Rows[0][17].ToString(); ViewBag.Accountant4 = dtblAccountant.Rows[0][7].ToString(); ViewBag.AccountantS4 = dtblAccountant.Rows[0][18].ToString(); ViewBag.Accountant5 = dtblAccountant.Rows[0][8].ToString(); ViewBag.Accountant6 = dtblAccountant.Rows[0][9].ToString(); ViewBag.Accountant7 = dtblAccountant.Rows[0][10].ToString(); ViewBag.AccountantS7 = dtblAccountant.Rows[0][19].ToString(); ViewBag.Accountant8 = dtblAccountant.Rows[0][11].ToString(); ViewBag.AccountantS8 = dtblAccountant.Rows[0][20].ToString(); ViewBag.Accountant9 = dtblAccountant.Rows[0][12].ToString(); ViewBag.Accountant10 = dtblAccountant.Rows[0][13].ToString(); ViewBag.AccountantS10 = dtblAccountant.Rows[0][21].ToString(); } else { ViewBag.Essay1 = dtblEssay.Rows[0][2].ToString(); ViewBag.Essay2 = dtblEssay.Rows[0][3].ToString(); ViewBag.Essay3 = dtblEssay.Rows[0][4].ToString(); ViewBag.Essay4 = dtblEssay.Rows[0][5].ToString(); ViewBag.Essay5 = dtblEssay.Rows[0][6].ToString(); ViewBag.Essay6 = dtblEssay.Rows[0][7].ToString(); ViewBag.Essay7 = dtblEssay.Rows[0][8].ToString(); ViewBag.Essay8 = dtblEssay.Rows[0][9].ToString(); ViewBag.Essay9 = dtblEssay.Rows[0][10].ToString(); ViewBag.Essay10 = dtblEssay.Rows[0][11].ToString(); ViewBag.Essay11 = dtblEssay.Rows[0][12].ToString(); ViewBag.Essay12 = dtblEssay.Rows[0][13].ToString(); ViewBag.Essay13 = dtblEssay.Rows[0][14].ToString(); } ViewBag.MasterlistID = MasterListID; ViewBag.JobPosition = JobPosition; ViewBag.Firstname = Firstname; ViewBag.Lastname = Lastname; TempData["MasterlistID"] = "1"; return(View(master.Masterlists.ToList().Where(x => x.EmploymentStatus == "Applicant").ToPagedList(i ?? 1, 10))); } else { return(RedirectToAction("ExamList", master.Masterlists.ToList().Where(x => x.EmploymentStatus == "Applicant").ToPagedList(i ?? 1, 10))); } }
public ActionResult ApplicantList(int?i) { MasterListEntities master = new MasterListEntities(); return(View(master.Masterlists.ToList().ToPagedList(i ?? 1, 10))); }
public ActionResult AddApplicant(Masterlist masterlist, WorkExperience workExperience, int?examno) { DataTable dtblApplicant = new DataTable(); using (SqlConnection sqlCon = new SqlConnection(connectionString)) { sqlCon.Open(); string query = "SELECT Applicant_ExamNo from Masterlist ORDER BY Applicant_ExamNo desc"; SqlDataAdapter sqlDa = new SqlDataAdapter(query, sqlCon); sqlDa.Fill(dtblApplicant); } if (dtblApplicant.Rows.Count > 0) { examno = Convert.ToInt32(dtblApplicant.Rows[0][0].ToString()); examno = examno + 1; } else { examno = 100001; } MasterListEntities Master = new MasterListEntities(); using (SqlConnection sqlCon = new SqlConnection(connectionString)) { sqlCon.Open(); string query = " select * from Masterlist where Birthday = @Birthday AND LastName = @LastName AND FirstName = @FirstName"; SqlCommand sqlCmd = new SqlCommand(query, sqlCon); sqlCmd.Parameters.AddWithValue("@Birthday", masterlist.Birthday); sqlCmd.Parameters.AddWithValue("@LastName", masterlist.LastName); sqlCmd.Parameters.AddWithValue("@FirstName", masterlist.FirstName); SqlDataReader sdr = sqlCmd.ExecuteReader(); if (sdr.Read()) { TempData["error"] = "Applicant Already Applied!"; } else { sdr.Close(); string querys = "INSERT INTO Masterlist(FirstName,MiddleName,LastName,Birthday,MaritalStatus,JobTitle,Street_Address1,Street_Address2,City,Province,ZipCode,ContactNumber,PersonalEmail,Applicant_AppliedDate,Applicant_ExamNo)" + "VALUES(@FirstName,@MiddleName,@LastName,@Birthday,@MaritalStatus,@JobTitle,@Street_Address1,@Street_Address2,@City,@Province,@ZipCode,@ContactNumber,@PersonalEmail,@Applicant_AppliedDate,@Applicant_ExamNo)"; SqlCommand sqlCmds = new SqlCommand(querys, sqlCon); sqlCmds.Parameters.AddWithValue("@FirstName", masterlist.FirstName); sqlCmds.Parameters.AddWithValue("@MiddleName", masterlist.MiddleName); sqlCmds.Parameters.AddWithValue("@LastName", masterlist.LastName); sqlCmds.Parameters.AddWithValue("@Birthday", masterlist.Birthday); sqlCmds.Parameters.AddWithValue("@MaritalStatus", masterlist.MaritalStatus); sqlCmds.Parameters.AddWithValue("@JobTitle", masterlist.JobTitle); sqlCmds.Parameters.AddWithValue("@Street_Address1", masterlist.Street_Address1); sqlCmds.Parameters.AddWithValue("@Street_Address2", masterlist.Street_Address2); sqlCmds.Parameters.AddWithValue("@City", masterlist.City); sqlCmds.Parameters.AddWithValue("@Province", masterlist.Province); sqlCmds.Parameters.AddWithValue("@ZipCode", masterlist.ZipCode); sqlCmds.Parameters.AddWithValue("@ContactNumber", masterlist.ContactNumber); sqlCmds.Parameters.AddWithValue("@PersonalEmail", masterlist.PersonalEmail); sqlCmds.Parameters.AddWithValue("@Applicant_AppliedDate", masterlist.Applicant_AppliedDate); sqlCmds.Parameters.AddWithValue("@Applicant_ExamNo", examno); SqlDataReader sdrs = sqlCmds.ExecuteReader(); int masterlistID = Master.Masterlists.Max(item => item.MasterlistID); sdrs.Close(); string queries = "INSERT INTO WorkExperience(MasterlistID,CompanyName,JobTitle,DateFrom,DateTo)" + "VALUES(@MasterlistID,@CompanyName,@JobTitles,@DateFrom,@DateTo)"; SqlCommand sqlcommands = new SqlCommand(queries, sqlCon); sqlcommands.Parameters.AddWithValue("@MasterlistID", masterlistID); sqlcommands.Parameters.AddWithValue("@CompanyName", workExperience.CompanyName); sqlcommands.Parameters.AddWithValue("@JobTitles", workExperience.JobTitle); sqlcommands.Parameters.AddWithValue("@DateFrom", workExperience.DateFrom); sqlcommands.Parameters.AddWithValue("@DateTo", workExperience.DateTo); SqlDataReader sqldrs = sqlcommands.ExecuteReader(); TempData["success"] = "New Applicant: " + masterlist.FirstName + " " + masterlist.LastName + " Added!"; sqldrs.Close(); } } return(RedirectToAction("AddApplicant")); }