public static void ScheduleInterviewToDB(InterviewModel interviewModel, User user)
{
SqlConnection con = DBUtils.getDBConnection();
con.Open();
SqlCommand command;
command = new SqlCommand("insert into Interview (job_id, date, time, venue, scheduled_by) values (" + interviewModel.JobId + ", '" + interviewModel.Date.ToString() + "', '" + interviewModel.Time.ToString() + "', '" + interviewModel.Venue + "', " + user.user_id + ");", con);
command.ExecuteNonQuery();
command = new SqlCommand("UPDATE Job SET status='S' WHERE job_id=" + interviewModel.JobId + " ;", con);
command.ExecuteNonQuery();
command = new SqlCommand("UPDATE Application SET status_code='I', status='Interview Scheduled' WHERE job_id=" + interviewModel.JobId + " ;", con);
command.ExecuteNonQuery();
con.Close();
}
public static void ReleaseResultToDB(ResultModel resultModel, User user)
{
SqlConnection con = DBUtils.getDBConnection();
con.Open();
SqlCommand command;
List<CandidateResult> selectedCandidates = new List<CandidateResult>();
int count = 0;
foreach (var item in resultModel.Candidates)
{
if (item.IsSelected)
{
selectedCandidates.Add(item);
count++;
command = new SqlCommand("UPDATE Application SET status_code='S', status='Selected' WHERE candidate_id=" + item.UserID + " ;", con);
command.ExecuteNonQuery();
command = new SqlCommand("UPDATE Users SET state='Selected' WHERE user_id=" + item.UserID + " ;", con);
command.ExecuteNonQuery();
}
else
{
command = new SqlCommand("UPDATE Application SET status_code='R', status='Rejected' WHERE candidate_id=" + item.UserID + " ;", con);
command.ExecuteNonQuery();
command = new SqlCommand("UPDATE Users SET state='Blocked', account_act_date='"+DateTime.Now.AddDays(90).ToShortDateString()+"' WHERE user_id=" + item.UserID + " ;", con);
command.ExecuteNonQuery();
}
}
resultModel.numOfCandidatesSelected = count;
string selectedCandidatesSet = String.Join(",", selectedCandidates.Select(x => x.UserID.ToString()).ToArray());
command = new SqlCommand("INSERT INTO dbo.Results (job_id, declaration_date, num_of_candidates_selected, candidates, released_by) values (" + resultModel.JobId + ", '" + DateTime.Now.ToShortDateString() + "', " + resultModel.numOfCandidatesSelected + ", '" + selectedCandidatesSet + "', " + user.user_id + " );", con);
command.ExecuteNonQuery();
command = new SqlCommand("UPDATE Job SET status='R' WHERE job_id=" + resultModel.JobId + " ;", con);
command.ExecuteNonQuery();
con.Close();
}
public static void PostJobInDB(Job model, User poster)
{
SqlConnection con = DBUtils.getDBConnection();
con.Open();
List<Skill> selectedSkills = new List<Skill>();
foreach (var item in model.Skills)
{
if(item.Checked){
selectedSkills.Add(item);
}
}
string skillSet = String.Join(",", selectedSkills.Select(x => x.Id.ToString()).ToArray());
SqlCommand command = new SqlCommand("insert into Job (job_description, job_role_id, skill_set, vacancies, min_experience, max_experience, age_limit, posted_by, posted_on, status) values ('" + model.JobDesc + "', " + model.JobRole + ", '" + skillSet + "'," + model.Vacancies + ", " + model.MinExperience + ", " + model.MaxExperience + ", " + model.AgeLimit + ", " + poster.user_id + ", '" + DateTime.Now.ToShortDateString() + "', 'P' );", con);
command.ExecuteNonQuery();
con.Close();
}
public static List<User> GetUsers()
{
SqlConnection con = DBUtils.getDBConnection();
con.Open();
SqlCommand command = new SqlCommand("select user_id, username, role from dbo.Users ;", con);
command.ExecuteNonQuery();
List<User> userList = new List<User>();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
User user = new User();
user.user_id = reader.GetInt32(0);
user.username = reader.GetString(1);
user.role = reader.GetString(2);
userList.Add(user);
}
reader.Close();
con.Close();
return userList;
}
public static User validateUserAndGetRole(string username, string passwd)
{
SqlConnection con = DBUtils.getDBConnection();
con.Open();
SqlCommand command = new SqlCommand("select user_id, password, role, name, state, account_act_date from dbo.Users where username='******';", con);
SqlDataReader reader = command.ExecuteReader();
//Creating a user object
User user = new User();
if (reader == null || !reader.Read())
{
user.role = "INVALID";
return user;
}
if (Convert.ToString(reader[4]).Equals("Disabled"))
{
user.role = "Disabled";
return user;
}
user.user_id = Convert.ToInt32(reader[0]);
string pwd = Convert.ToString(reader[1]);
user.password = pwd;
user.role = Convert.ToString(reader[2]);
user.name = Convert.ToString(reader[3]);
user.state = Convert.ToString(reader[4]);
user.AccountActiveDate = Convert.ToDateTime(reader[5]);
user.username = username;
con.Close();
string passwdHash = StringUtils.GetMD5Hash(StringUtils.Reverse(passwd));
if (!passwdHash.Equals(pwd))
user.role = "INVALID";
return user;
}
public static StaffDashBoardModel GetStaffHome(User user)
{
StaffDashBoardModel model = new StaffDashBoardModel();
SqlConnection con = DBUtils.getDBConnection();
con.Open();
SqlCommand command = new SqlCommand("SELECT right_to_post,right_to_schedule,right_to_publish FROM dbo.Staff WHERE staff_id='" + user.user_id + "';", con);
SqlDataReader reader = command.ExecuteReader();
if (reader == null || !reader.Read())
{
return null;
}
if (Convert.ToBoolean(reader[0]))
{
model.RightToPost = "Yes";
}
else
{
model.RightToPost = "No";
}
if (Convert.ToBoolean(reader[1]))
{
model.RightToScheduleInterview = "Yes";
}
else
{
model.RightToScheduleInterview = "No";
}
if (Convert.ToBoolean(reader[2]))
{
model.RightToReleaseResults = "Yes";
}
else
{
model.RightToReleaseResults = "No";
}
reader.Close();
command = new SqlCommand("SELECT COUNT(1) FROM dbo.Job WHERE posted_by='" + user.user_id + "';", con);
reader = command.ExecuteReader();
if (reader == null || !reader.Read())
{
return null;
}
model.JobPosted = Convert.ToInt32(reader[0]);
reader.Close();
command = new SqlCommand("SELECT COUNT(1) FROM dbo.Interview WHERE scheduled_by='" + user.user_id + "';", con);
reader = command.ExecuteReader();
if (reader == null || !reader.Read())
{
return null;
}
model.InterviewsScheduled = Convert.ToInt32(reader[0]);
reader.Close();
command = new SqlCommand("SELECT COUNT(1) FROM dbo.Results WHERE released_by='" + user.user_id + "';", con);
reader = command.ExecuteReader();
if (reader == null || !reader.Read())
{
return null;
}
model.ResultsReleased = Convert.ToInt32(reader[0]);
reader.Close();
con.Close();
return model;
}