// GET: Departments/Details/5
public ActionResult Details(int id)
{
DepartmentDetailsView detailsView = new DepartmentDetailsView();
detailsView.department = null;
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT d.Id,
d.Name,
d.Budget,
e.Id as EmployeeId,
e.FirstName,
e.LastName,
e.IsSuperVisor
FROM Department d
LEFT JOIN Employee e ON e.DepartmentId = d.Id
WHERE d.Id = @DepartmentId";
cmd.Parameters.Add(new SqlParameter("@DepartmentId", id));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (detailsView.department == null)
{
detailsView.department = new Department
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Budget = reader.GetInt32(reader.GetOrdinal("Budget"))
};
}
if (!reader.IsDBNull(reader.GetOrdinal("EmployeeId")))
{
detailsView.department.employees.Add(new Employee
{
Id = reader.GetInt32(reader.GetOrdinal("EmployeeId")),
FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
LastName = reader.GetString(reader.GetOrdinal("LastName")),
IsSuperVisor = reader.GetBoolean(reader.GetOrdinal("IsSuperVisor"))
});
}
}
var employeeSuperVisor = detailsView.department.employees.Find(e => e.IsSuperVisor == true);
detailsView.SuperVisor = $"{employeeSuperVisor.FirstName} {employeeSuperVisor.LastName}";
reader.Close();
return(View(detailsView));
}
}
}
/// <summary>
/// Method will return details
/// </summary>
/// <returns></returns>
public List <DepartmentDetailsView> GetDepartmentDetails()
{
List <DepartmentDetailsView> departmentDetailsViews = new List <DepartmentDetailsView>();
var departmentDetails = _departmentDetails.GetDepartmentDetails();
if (departmentDetails != null)
{
foreach (var item in departmentDetails)
{
if (departmentDetailsViews.FirstOrDefault(x => x.CategoryId == item.CategoryId) == null)
{
DepartmentDetailsView departmentDetailsView = GetViewObject(departmentDetails, item);
GetAmountByCategory(departmentDetailsView);
departmentDetailsViews.Add(departmentDetailsView);
}
}
GetAmountByYear(departmentDetailsViews);
}
return(departmentDetailsViews);
}
/// <summary>
/// Method to add the amount based on category to get total
/// </summary>
/// <param name="departmentDetailsView"></param>
private static void GetAmountByCategory(DepartmentDetailsView departmentDetailsView)
{
departmentDetailsView.TotalAmount = (Convert.ToInt64(departmentDetailsView.YearOne) + Convert.ToInt64(departmentDetailsView.YearTwo)
+ Convert.ToInt64(departmentDetailsView.YearThree) + Convert.ToInt64(departmentDetailsView.YearFour)
+ Convert.ToInt64(departmentDetailsView.YearFive)).ToString();
}