/// <summary>
/// Retrieve names of skills and employees who give top rating for skill
/// </summary>
/// <returns></returns>
public List <KeyValuePair <string, string> > GetTopRatedRecentEmployees()
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
//Get rating id with top rating value
var MaximumRatingId = context.Ratings.Where(s => s.Value == 5).Select(s => s.Id).FirstOrDefault();
int RatingId = Convert.ToInt32(MaximumRatingId);
//Get skill name and employee name of those employee whose rating id matches with top rating id.
var skill = (from sr in context.SkillRatings
join e in context.Employees
on sr.EmployeeId equals e.Id
join r in context.Ratings
on sr.RatingId equals r.Id
join s in context.Skills
on sr.SkillId equals s.SkillId
where sr.Status == true && s.Status == true && sr.RatingId == RatingId && e.Status == true
orderby sr.RatingId descending
select new { s.SkillName, e.Name }).ToList();
List <KeyValuePair <string, string> > topSkills = new List <KeyValuePair <string, string> >();
foreach (var skillEmp in skill)
{
//Add skill name and employee name as key value pair
topSkills.Add(new KeyValuePair <string, string>(skillEmp.SkillName, skillEmp.Name));
}
return(topSkills);
}
}
/// <summary>
/// To get role of a user
/// </summary>
/// <param name="employeecode"></param>
/// <param name="password"></param>
/// <returns></returns>
public string GetRole(string employeecode, string password)
{
try
{
using (SkillsetDbContext db = new SkillsetDbContext())
{
double pass = Convert.ToDouble(password);
//get status of employee
var status = (from employee in db.Employees where (employee.EmployeeCode == employeecode && employee.MobileNumber == pass) select (employee.Status)).FirstOrDefault();
if (status == true)
{
//get role of employee
var role = (from employee in db.Employees join roles in db.Roles on employee.RoleId equals roles.Id where (employee.EmployeeCode == employeecode) select roles.Name).FirstOrDefault();
return role;
}
else
{
var role = "Not Valid User";
return role;
}
}
}
catch (Exception)
{
return "Not Valid User";
}
}
/// <summary>
/// Sets status of skill to false using Skill Is
/// </summary>
/// <param name="skill Id"></param>
/// <returns></returns>
public int Delete(int?skillId)
{
if (skillId != null)
{
using (var db = new SkillsetDbContext())
{
var deleteSkill = db.Skills.Find(skillId);
deleteSkill.Status = false;
StringBuilder builder = new StringBuilder();
//Changing skill name of deleted skill to prevent conflict
builder.Append(deleteSkill.SkillName).Append("-deleted-").Append(deleteSkill.SkillId);
deleteSkill.SkillName = builder.ToString();
db.Entry(deleteSkill).State = EntityState.Modified;
//Corresponding ratings in skill has to be deleted
var deleteRatingSkill = db.SkillRatings.Where(s => s.SkillId == skillId).ToList();
deleteRatingSkill.ForEach(a => a.Status = false);
foreach (var ratingSkill in deleteRatingSkill)
{
db.Entry(ratingSkill).State = EntityState.Modified;
}
db.SaveChanges();
}
return(1);
}
return(0);
}
/// <summary>
/// Retrieves the list of qualifications
/// </summary>
/// <returns></returns>
public List <Qualification> GetQualifications()
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
return(context.Qualifications.ToList());
}
}
/// <summary>
/// Finding employee name from table Employee
/// </summary>
/// <param name="employeeCode"></param>
/// <returns></returns>
public string FindEmployeeName(string employeeCode)
{
using (var context = new SkillsetDbContext())
{
return(context.Employees.Where(d => d.EmployeeCode == employeeCode).Select(d => d.Name).FirstOrDefault());
}
}
/// <summary>
/// Insert skill into Skill table
/// </summary>
/// <param name="skill"></param>
/// <returns></returns>
public int Create(Skill skill)
{
try
{
if (skill != null)
{
using (var db = new SkillsetDbContext())
{
skill.Status = true;
//Check if Skill Name exists already
if (db.Skills.Where(s => s.SkillName.ToLower() == skill.SkillName.ToLower()).Count() != 0)
{
return(-1);
}
db.Skills.Add(skill);
db.SaveChanges();
}
return(1);
}
return(0);
}
catch (Exception)
{
return(-1);
}
}
/// <summary>
/// Finding skill name from table Skill
/// </summary>
/// <param name="skillId"></param>
/// <returns></returns>
public string FindSkillName(int skillId)
{
using (var context = new SkillsetDbContext())
{
return(context.Skills.Where(d => d.SkillId == skillId).Select(d => d.SkillName).FirstOrDefault());
}
}
/// <summary>
/// Retrieve the employee details according to id
/// </summary>
/// <returns></returns>
public Employee GetProfile(string id)
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
return(context.Employees.Where(e => e.EmployeeCode == id).FirstOrDefault());
}
}
/// <summary>
/// Retrieves the list of managers
/// </summary>
/// <returns></returns>
public List <Employee> GetManagers()
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
return(context.Employees.Where(p => p.RoleId == 2 && p.Status == true).ToList());
}
}
/// <summary>
/// Adds new employee to the Employee table
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
public int AddEmployee(Employee employee)
{
var employeeList = new List <Employee>();
int employeeCount;
try
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
employeeList = context.Employees.ToList();
employeeCount = employeeList.Count();
//checks for duplicate employee
int status = CheckDuplicateEmployee(employeeList, employee);
//if duplicate employee is not found
if (status == 0)
{
employee.Status = true;
employee.Id = employeeCount + 1;
context.Employees.Add(employee);
context.SaveChanges();
return(0);
}
else
{
return(status);
}
}
}
catch
{
return(-1);
}
}
/// <summary>
/// Finding note of a rating value from table Rating
/// </summary>
/// <param name="ratingId"></param>
/// <returns></returns>
public string FindRatingNote(int ratingId)
{
using (var context = new SkillsetDbContext())
{
return(context.Ratings.Where(d => d.Id == ratingId).Select(d => d.Note).FirstOrDefault());
}
}
/// <summary>
/// Retrieve average ratings for primary skills
/// </summary>
/// <returns></returns>
public string GetRatingAverage()
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
List <int> totalValues = new List <int>();
List <int> specificValues = new List <int>();
StringBuilder result = new StringBuilder();
string id = string.Empty;
//Get total ratings count for each skill
totalValues = GetTotalValue();
//Get sum of actual ratings for each skill
specificValues = GetSpecificValue();
for (int i = 0; i < specificValues.Count; i++)
{
//Calculate average rating for each skill
float ratingAvg = (float)specificValues.ElementAt(i) / (float)totalValues.ElementAt(i);
result.Append(ratingAvg);
result.Append(", ");
}
return(result.ToString());
}
}
/// <summary>
/// Retrieve the details of the employee with particular id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Employee GetEmployeeDetails(string id)
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
return(context.Employees.Where(p => p.EmployeeCode == id && p.Status == true).Single());
}
}
/// <summary>
/// Retrieves the list of designations
/// </summary>
/// <returns></returns>
public List <Designation> GetDesignations()
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
return(context.Designations.Where(p => p.Id != 1).ToList());
}
}
/// <summary>
/// Finding skill value from table Rating
/// </summary>
/// <param name="ratingId"></param>
/// <returns></returns>
public int FindSkillValue(int ratingId)
{
using (var context = new SkillsetDbContext())
{
return(context.Ratings.Where(d => d.Id == ratingId).Select(d => d.Value).FirstOrDefault());
}
}
/// <summary>
/// Retrieves the list of roles
/// </summary>
/// <returns></returns>
public List <Role> GetRole()
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
return(context.Roles.Where(p => p.Id != 1).ToList());
}
}
/// <summary>
/// Retrieve all skillsRatingsValues from Rating table
/// </summary>
/// <returns></returns>
public IList <Rating> GetAllRatingValues()
{
using (var db = new SkillsetDbContext())
{
var RatingValues = db.Ratings.ToList();
return(RatingValues);
}
}
/// <summary>
/// Retrieve skill object using skill name
/// </summary>
/// <param name="skillName"></param>
/// <returns></returns>
public Skill GetSkillBySkillName(string skillName)
{
using (var db = new SkillsetDbContext())
{
var skillDetails = db.Skills.Where(s => s.SkillName == skillName && s.Status == true).FirstOrDefault();
return(skillDetails);
}
}
/// <summary>
/// Retrieve all skills from Skill table
/// </summary>
/// <returns></returns>
public IList <Skill> GetAllSkills()
{
using (var db = new SkillsetDbContext())
{
var skillList = db.Skills.Where(s => s.Status == true).ToList();
return(skillList);
}
}
//method to find id of the specified employees's record
int FindId(string employeeCode)
{
using (var context = new SkillsetDbContext())
{
int empId = context.Employees.Where(m => m.EmployeeCode == employeeCode).Select(m => m.Id).FirstOrDefault();
return(empId);
}
}
/// <summary>
/// Retrieve all skillsRatings from SkillRatings table
/// </summary>
/// <returns></returns>
public IList <SkillRating> GetAllRatings(int empId)
{
using (var db = new SkillsetDbContext())
{
var skillRatingList = db.SkillRatings.Where(s => s.EmployeeId == empId && s.Status == true).OrderByDescending(s => s.RatingDate).ToList();
return(skillRatingList);
}
}
/// <summary>
/// Get employee id corresponding to an employee code
/// </summary>
/// <param name="employeeCode"></param>
/// <returns>int</returns>
public int GetEmployeeId(string employeeCode)
{
using (SkillsetDbContext context = new SkillsetDbContext())
{
var managerId = context.Employees.Where(x => x.EmployeeCode == employeeCode).Select(employee => employee.Id).FirstOrDefault();
return(managerId);
}
}
/// <summary>
/// Retrieve skill id using unique skill name
/// </summary>
/// <param name="skillName"></param>
/// <returns></returns>
public int GetIdBySkillName(string skillName)
{
using (var db = new SkillsetDbContext())
{
var skillId = db.Skills.Where(s => s.SkillName == skillName && s.Status == true).FirstOrDefault().SkillId;
return(skillId);
}
}
/// <summary>
/// Retrieve the name for a particular role id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetRoleName(string id)
{
int roleId = Convert.ToInt32(id);
using (SkillsetDbContext context = new SkillsetDbContext())
{
var name = context.Roles.Where(p => p.Id == roleId).Select(p => p.Name).Single();
return(name.ToString());
}
}
/// <summary>
/// Retrieve the name for a particular manager id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetManagerName(string id)
{
int managerId = Convert.ToInt32(id);
using (SkillsetDbContext context = new SkillsetDbContext())
{
var name = context.Employees.Where(p => p.Id == managerId).Select(p => p.Name).Single();
return(name.ToString());
}
}
/// <summary>
/// Retrieve the name for a particular qualification id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string GetQualificationName(string id)
{
int qualificationId = Convert.ToInt32(id);
using (SkillsetDbContext context = new SkillsetDbContext())
{
var name = context.Qualifications.Where(p => p.Id == qualificationId).Select(p => p.Name).Single();
return(name.ToString());
}
}
/// <summary>
/// Retrieve total skill ratings count
/// </summary>
/// <returns></returns>
public int GetSkillRatingsCount()
{
int skillRatingsCount = default(int);
using (SkillsetDbContext context = new SkillsetDbContext())
{
//Get distinct skill ratings count
skillRatingsCount = context.SkillRatings.Where(s => s.Status).Distinct().Count();
}
return(skillRatingsCount);
}
/// <summary>
/// Retrieve total employees count
/// </summary>
/// <returns></returns>
public int GetEmployeesCount()
{
int employeesCount = default(int);
using (SkillsetDbContext context = new SkillsetDbContext())
{
//Employee count excluding admin
employeesCount = (context.Employees.Where(s => s.Status).Distinct().Count()) - 1;
}
return(employeesCount);
}
/// <summary>
/// Retrieve total skill count
/// </summary>
/// <returns></returns>
public int GetSkillsCount()
{
int skillsCount = default(int);
using (SkillsetDbContext context = new SkillsetDbContext())
{
//Get distinct count of skills where status of skill is true
skillsCount = context.Skills.Where(s => s.Status).Distinct().Count();
}
return(skillsCount);
}
/// <summary>
/// Get Skill details of an employee from table Skillrating
/// </summary>
/// <param name="employeeCode"></param>
/// <returns></returns>
public List <SkillRating> GetSkillDetails(string employeeCode)
{
using (var context = new SkillsetDbContext())
{
int empId = FindId(employeeCode);
var query = from sr in context.SkillRatings
from s in context.Skills
where (sr.EmployeeId == empId && sr.SkillId == s.SkillId && sr.Status)
select sr;
return(query.OrderBy(s => s.RatingDate).ToList());
}
}
