//Refined Sub-Category Data Bind
protected void SubCategoryBind(object sender, EventArgs e)
{
using (var conn = new MySqlConnection(strcon))
{
conn.Open();
string Query = "";
if (DropDownCategory.SelectedIndex != 0)
{
Query = "SELECT * FROM SubCategory WHERE CatId = '" + DropDownCategory.SelectedValue + "'";
}
else
{
Query = "SELECT * FROM SubCategory";
}
using (var cmd = new MySqlCommand(Query, conn))
{
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
DropDownSubCategory.DataSource = reader;
DropDownSubCategory.DataValueField = "SubCatId";
DropDownSubCategory.DataTextField = "SCName";
DropDownSubCategory.DataBind();
DropDownSubCategory.Items.Insert(0, new ListItem("--- Choose One ---", "NA"));
}
}
}
}
}
public IEnumerable <EmpAndAllocationDto> GetAllEmployeesWithAllocationDetails()
{
List <EmpAndAllocationDto> emps = (from emp in Entities
join rm in Entities on emp.ReportingManagerID equals rm.EmployeeEntryID into rme
from rmd in rme.DefaultIfEmpty()
where emp.IsDeleted == false && emp.LastWorkingDay.HasValue == false ||
(emp.LastWorkingDay.HasValue == true && emp.LastWorkingDay.Value >= DateTime.Today)
orderby emp.EmployeeID
select new EmpAndAllocationDto
{
EmployeeEntryID = emp.EmployeeEntryID,
EmployeeID = emp.EmployeeID,
EmployeeName = emp.FirstName + " " + emp.LastName,
EmploymentType = DataContext.DropDownSubCategories.FirstOrDefault(s => s.SubCategoryID == emp.EmploymentTypeID).SubCategoryName,
BusinessUnit = DataContext.DropDownSubCategories.FirstOrDefault(s => s.SubCategoryID == emp.BusinessUnitID).SubCategoryName,
PrimarySkills = emp.PrimarySkills,
VisaCategory = emp.VisaCategoryID.HasValue ? DataContext.DropDownSubCategories.FirstOrDefault(s => s.SubCategoryID == emp.VisaCategoryID).SubCategoryName : "",
VisaValidUpto = emp.VisaValidUpto,
PastExperience = emp.PastExperience,
ProjectManager = "",
ReportingManager = rmd.FirstName + " " + rmd.LastName,
DateOfJoin = emp.DateOfJoin,
}).Distinct().ToList();
foreach (EmpAndAllocationDto emp in emps)
{
EmpAssetDetail asset = DataContext.EmpAssetDetails.FirstOrDefault(ea => ea.EmployeeEntryID == emp.EmployeeEntryID);
emp.PrimarySkills = asset?.PrimarySkills + ";" + asset?.SecondarySkills;
emp.PastExperience = asset?.PastExperience;
DateTime today = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day);
ProjectAllocation allocation = DataContext.ProjectAllocations.Where(pa => pa.EmployeeID == emp.EmployeeEntryID && pa.IsDeleted == false &&
pa.AllocationEndDate >= today).OrderByDescending(al => al.AllocationEndDate).FirstOrDefault();
if (allocation != null)
{
Project project = DataContext.Projects.FirstOrDefault(e => e.ProjectID == allocation.ProjectID && e.IsDeleted == false);
if (project != null)
{
emp.ProjectName = project.ProjectName;
Employee pm = Entities.FirstOrDefault(e => e.EmployeeEntryID == project.ProjectManagerID);
emp.ProjectManager = $"{pm?.FirstName} {pm?.LastName}";
}
DropDownSubCategory allType = DataContext.DropDownSubCategories.FirstOrDefault(s => s.IsDeleted == false && s.SubCategoryID == allocation.AllocationTypeID);
emp.AllocationStartDate = allocation.AllocationStartDate;
emp.AllocationEndDate = allocation.AllocationEndDate;
}
}
return(emps);
}
