/// <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; }