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