/// <summary>
/// Gets employee profile from the employee table
/// </summary>
/// <param name="id">user id</param>
/// <returns>returns an employee object</returns>
public Employee GetEmployee(Guid id)
{
Employee employee = new Employee();
DepartmentDAL departmentDAL = new DepartmentDAL();
string query = String.Format("SELECT e.UserId, e.Firstname, e.Surname, e.DepartmentId, d.DepartmentName, e.Role FROM Employee e LEFT OUTER JOIN Department d on e.DepartmentId = d.DepartmentId WHERE UserId='{0}'", id);
SqlCommand cmd = new SqlCommand(query, daFunctions.Connection);
try
{
daFunctions.Connection.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
employee.UserId = (Guid)rdr.GetGuid(0);
employee.FirstName = (string)rdr["Firstname"];
employee.Surname = (string)rdr["Surname"];
employee.Dept = departmentDAL.GetDepartmentProfile(rdr["DepartmentId"] as int? ?? default(int));
employee.Role = (string)rdr["Role"];
}
daFunctions.Connection.Close();
}
catch (Exception ex)
{
throw new Exception("Unable to load user from employee table: " + ex.Message);
}
return employee;
}
/// <summary>
/// Retrieves the expense reports from the database with given query
/// </summary>
/// <param name="query">sql query</param>
/// <returns></returns>
private List<ExpenseReport> GetReportsFromDatabase(string query)
{
List<ExpenseReport> expenseReports = new List<ExpenseReport>();
EmployeeDAL employeeDAL = new EmployeeDAL();
DepartmentDAL departmentDAL = new DepartmentDAL();
DataAccessFunctions daFunctions = new DataAccessFunctions();
daFunctions.Command = new SqlCommand(query, daFunctions.Connection);
try
{
daFunctions.Connection.Open();
SqlDataReader rdr = daFunctions.Command.ExecuteReader();
while (rdr.Read())
{
ExpenseReport report = new ExpenseReport();
Employee createdBy = new Employee();
Employee approvedBy = new Employee();
Employee processedBy = new Employee();
decimal expenseTotal;
report.ExpenseId = rdr["ExpenseId"] as int? ?? default(int);
//report.DepartmentId = rdr["DepartmentId"] as int? ?? default(int);
report.CreateDate = (DateTime)rdr["CreateDate"];
report.ExpenseToDept = departmentDAL.GetDepartmentProfile(rdr["DepartmentId"] as int? ?? default(int));
report.Status = (ReportStatus)Enum.Parse(typeof(ReportStatus), (string)rdr["Status"]);
report.CreatedBy = employeeDAL.GetEmployee(rdr["CreatedById"] as Guid? ?? default(Guid));
report.ApprovedBy = employeeDAL.GetEmployee(rdr["ApprovedById"] as Guid? ?? default(Guid));
report.ProcessedBy = employeeDAL.GetEmployee(rdr["ProcessedById"] as Guid? ?? default(Guid));
report.ApprovedDate = rdr["ApprovedDate"] as DateTime? ?? default(DateTime);
report.ExpenseItems = GetExpenseItemsByExpenseId(report.ExpenseId, out expenseTotal);
report.ExpenseTotal = expenseTotal;
expenseReports.Add(report);
}
}
catch (Exception ex)
{
throw new Exception("There was a problem retrieving expense reports: " + ex.Message);
}
finally
{
daFunctions.Connection.Close();
}
return expenseReports;
}