public List <User_Info> GetEmployeesInfo(List <int> employees_id)
{
List <User_Info> employees = new List <User_Info>();
for (int i = 0; i < employees_id.Count; i++)
{
User_Info user = new User_Info();
connection();
SqlCommand cmd = new SqlCommand("employee_info", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@employee_id", employees_id[i]);
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
user.User_id = Convert.ToInt32(dr["employee_id"]);
user.UserName = Convert.ToString(dr["user_name"]);
user.Email = Convert.ToString(dr["email"]);
user.PhoneNumber = Convert.ToString(dr["phone_number"]);
user.Address = Convert.ToString(dr["address"]);
}
employees.Add(user);
}
return(employees);
}
public List <User_Info> getFeedbackFromTask(int employeeId, string projectName, string taskName)
{
List <User_Info> users = new List <User_Info>();
connection();
SqlCommand cmd = new SqlCommand("get_feedback_for_task", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@employee_giver_id", employeeId);
cmd.Parameters.AddWithValue("@project_name", projectName);
cmd.Parameters.AddWithValue("@task_name", taskName);
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
User_Info user = new User_Info();
user.User_id = Convert.ToInt32(dr["employee_id"]);
user.UserName = Convert.ToString(dr["user_name"]);
user.Email = Convert.ToString(dr["email"]);
user.PhoneNumber = Convert.ToString(dr["phone_number"]);
user.Address = Convert.ToString(dr["address"]);
user.EmployeeSkills = getFeedbackSkillForEmployee(Convert.ToInt32(dr["employee_id"]), Convert.ToInt32(dr["task_id"]));
users.Add(user);
}
return(users);
}
// returns 0 in failure and 1 or greater if success
public int addEmployee(User_Info userInfo)
{
int success;
connection();
SqlCommand cmd = new SqlCommand("add_employee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@user_name", userInfo.UserName);
cmd.Parameters.AddWithValue("@password", Encrypt.Encode(userInfo.Password));
cmd.Parameters.AddWithValue("@email", userInfo.Email);
cmd.Parameters.AddWithValue("@full_name", userInfo.FullName);
cmd.Parameters.AddWithValue("@address", userInfo.Address);
cmd.Parameters.AddWithValue("@ssd", userInfo.Ssn);
cmd.Parameters.AddWithValue("@salary", userInfo.Salary);
cmd.Parameters.AddWithValue("@start_date", userInfo.StartDate);
cmd.Parameters.AddWithValue("@phone_number", userInfo.PhoneNumber);
cmd.Parameters.AddWithValue("@gender", userInfo.Gender);
cmd.Parameters.AddWithValue("@birth_date", userInfo.BirthDate);
cmd.Parameters.AddWithValue("@educational_degree", userInfo.EducationalDrgree);
cmd.Parameters.AddWithValue("@graduation_date", userInfo.GraduationDate);
cmd.Parameters.AddWithValue("@notes", userInfo.Notes);
con.Open();
success = cmd.ExecuteNonQuery();
con.Close();
return(success);
}
//get list of employees with skills
public List <User_Info> getRecommendedEmployeeForTask(Task task)
{
List <Skill> skills = task.Skills;
List <User_Info> employees = new List <User_Info>();
for (int i = 0; i < skills.Count; i++)
{
connection();
SqlCommand cmd = new SqlCommand("get_recommended_employee_for_skill", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@skill_id", skills[i].SkillId);
cmd.Parameters.AddWithValue("@start_date", task.AssignDate);
cmd.Parameters.AddWithValue("@end_date", task.Deadline);
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
User_Info user = new User_Info();
user.User_id = Convert.ToInt32(dr["employee_id"]);
user.UserName = Convert.ToString(dr["user_name"]);
user.Email = Convert.ToString(dr["email"]);
user.PhoneNumber = Convert.ToString(dr["phone_number"]);
user.Address = Convert.ToString(dr["address"]);
Skill skill = new Skill();
skill.SkillId = skills[i].SkillId;
skill.SkillName = Convert.ToString(dr["skill_name"]);
skill.Rate = Convert.ToInt32(dr["skill_rank"]);
user.EmployeeSkills = new List <Skill>();
user.EmployeeSkills.Add(skill);
employees.Add(user);
}
}
return(employees);
}
public Task ViewTask(int taskId)
{
Task task = new Task();
connection();
SqlCommand cmd = new SqlCommand("get_task", con);///////////////////
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@task_id", taskId);
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
task.TaskId = Convert.ToInt32(dr["task_id"]);
task.TaskName = Convert.ToString(dr["task_name"]);
task.ProjectId = Convert.ToInt32(dr["project_id"]);
task.AssignDate = Convert.ToDateTime(dr["assign_time"]);
task.Deadline = Convert.ToDateTime(dr["deadline"]);
}
List <User_Info> employees = new List <User_Info>();
connection();
cmd = new SqlCommand("get_task_employees", con);///////////////
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@task_id", taskId);
sd = new SqlDataAdapter(cmd);
dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
User_Info user = new User_Info();
user.User_id = Convert.ToInt32(dr["employee_id"]);
user.UserName = Convert.ToString(dr["user_name"]);
user.Email = Convert.ToString(dr["email"]);
user.PhoneNumber = Convert.ToString(dr["phone_number"]);
user.Address = Convert.ToString(dr["address"]);
if (!DBNull.Value.Equals(dr["end_time"]))
{
task.EndTime = Convert.ToDateTime(dr["end_time"]);
}
employees.Add(user);
}
task.Employees = employees;
List <Skill> skills = new List <Skill>();
connection();
cmd = new SqlCommand("get_task_skills", con);//////////////////////
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@task_id", taskId);
sd = new SqlDataAdapter(cmd);
dt = new DataTable();
con.Open();
sd.Fill(dt);
con.Close();
foreach (DataRow dr in dt.Rows)
{
Skill skill = new Skill();
skill.SkillId = Convert.ToInt32(dr["skill_id"]);
skill.SkillName = Convert.ToString(dr["skill_name"]);
skills.Add(skill);
}
task.Skills = skills;
return(task);
}
