/// <summary>
/// Get the total amount of expenses approved by individual supervisor, including:
/// - already approved by both supervisor and accountant (i.e. Status = ApprovedByAccountant)
/// EXCLUDING:
/// - pending for accountant approval (i.e. Status = ApprovedBySupervisor)
/// - approved by supervisor BUT rejected by accountant (i.e. Status = RejectedByAccountant)
/// </summary>
/// <param name="status"></param>
/// <returns></returns>
public List<Employee> GetSpendBySupervisors(int month)
{
List<Employee> employees = new List<Employee>();
EmployeeDAL employeeDAL = new EmployeeDAL();
DataAccessFunctions daFunctions = new DataAccessFunctions();
string query = string.Format("SELECT H.ApprovedById AS SupervisorId, COUNT(H.ExpenseId) AS AmountApproved, SUM(I.AudAmount) AS ExpenseApproved FROM ExpenseItem I LEFT OUTER JOIN ExpenseHeader H ON I.ExpenseHeaderId = H.ExpenseId WHERE H.Status ='ApprovedByAccounts' AND DATEPART(month,ProcessedDate)={0} GROUP BY H.ApprovedById", month);
daFunctions.Command = new SqlCommand(query, daFunctions.Connection);
try
{
daFunctions.Connection.Open();
SqlDataReader rdr = daFunctions.Command.ExecuteReader();
while (rdr.Read())
{
Employee emp = new Employee();
emp = employeeDAL.GetEmployee(rdr["SupervisorId"] as Guid? ?? default(Guid));
emp.AmountApproved = rdr["AmountApproved"] as int? ?? default(int);
emp.ExpenseApproved = rdr["ExpenseApproved"] as decimal? ?? default(decimal);
employees.Add(emp);
}
daFunctions.Connection.Close();
}
catch (Exception ex)
{
throw new Exception("There was a problem retrieving expense approved by supervisor reports: " + ex.Message);
}
return employees;
}
protected void btnSearchExpenses_Click(object sender, EventArgs e)
{
emp = (Employee)Session["emp"];
ExpenseReportDAL expenseReportDAL = new ExpenseReportDAL();
rptExpenseReport.DataSource = expenseReportDAL.GetReportsByDepartment(emp.Dept.DepartmentId, ddlSearchFilter.SelectedValue);
rptExpenseReport.DataBind();
}
/// <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;
}
public ExpenseReport()
{
CreatedBy = new Employee();
ApprovedBy = new Employee();
ProcessedBy = new Employee();
ExpenseToDept = new Department();
ExpenseItems = new List<ExpenseItem>();
}
protected void Page_Load(object sender, EventArgs e)
{
emp = (Employee)Session["emp"];
if (!IsPostBack)
{
InitializeRepeater();
}
}
protected void Page_Load(object sender, EventArgs e)
{
Employee emp = new Employee();
emp =(Employee) Session["emp"];
BudgetTracker budget = new BudgetTracker();
budget.DepartmentBudget(emp.Dept.MonthlyBudget, emp.Dept.DepartmentId);
decimal moneyRemaining = budget.RemainingAmount;
decimal totalExpenseAmount = budget.TotalExpenseAmount;
lblMoneySpent.Text = "Total money spent so far is : AU$ " + totalExpenseAmount.ToString();
lblMoneyRemaining.Text = "Total money remaining is : AU$ " + moneyRemaining.ToString();
}
/// <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;
}
private void InitializeExpenseReport()
{
reportBuilder = new ExpenseReportBuilder();
Employee employee = new Employee();
EmployeeDAL employeeDAL = new EmployeeDAL();
employee = employeeDAL.GetEmployee((Guid)Membership.GetUser().ProviderUserKey);
reportBuilder.expenseReport.CreateDate = DateTime.Now;
reportBuilder.expenseReport.CreatedBy = employee;
reportBuilder.expenseReport.ExpenseToDept = employee.Dept;
Session["expenseReportBuilder"] = reportBuilder;
txtEmployeeName.Text = employee.FirstName + " " + employee.Surname;
txtDepartment.Text = employee.Dept.DepartmentName;
txtExpenseDate.Text = reportBuilder.expenseReport.CreateDate.ToString("dd/MM/yyyy");
}
public void EmployeeDAL_IsGetEmployeeEqualToTestEmployee_IsEqual()
{
Guid id = new Guid("78560dd3-f95e-4011-b40d-a7b56ed17f24");
Employee employee = new Employee();
EmployeeDAL employeeDAL = new EmployeeDAL();
employee = employeeDAL.GetEmployee(id);
bool IsEqual = TestEmployeeComparer(employee);
Assert.IsTrue(IsEqual, "Employee from database is not equal to test employee");
}
private bool TestEmployeeComparer(Employee employee)
{
Employee testEmployee = new Employee();
testEmployee.UserId = new Guid("78560dd3-f95e-4011-b40d-a7b56ed17f24");
testEmployee.FirstName = "Vikki";
testEmployee.Surname = "Car";
testEmployee.Dept.DepartmentId = 1;
testEmployee.Dept.DepartmentName = "State Services";
testEmployee.Role = "Consultant";
if (testEmployee.UserId == employee.UserId && testEmployee.FirstName == employee.FirstName
&& testEmployee.Surname == employee.Surname && testEmployee.Dept.DepartmentId == employee.Dept.DepartmentId
&& testEmployee.Dept.DepartmentName == employee.Dept.DepartmentName && testEmployee.Role == employee.Role)
{
return true;
}
else { return false; }
}
public void Employee_CheckEmployeeIsNotNull_IsNotNull()
{
Employee employee = new Employee();
Assert.IsNotNull(employee.UserId, "Employee UserId is null");
Assert.IsNotNull(employee.Dept.DepartmentName, "Employee DepartmentName is null");
Assert.IsNotNull(employee.FirstName, "Employee FirstName is null");
Assert.IsNotNull(employee.Surname, "Employee Surname is null");
Assert.IsNotNull(employee.Role, "Employee Role is null");
}
protected void InitializeRepeater()
{
if (Session["emp"] != null)
{
budget.DepartmentBudget(emp.Dept.MonthlyBudget, emp.Dept.DepartmentId);
Session["budget"] = budget;
}
else
{
EmployeeDAL employeeDAL = new EmployeeDAL();
emp = employeeDAL.GetEmployee((Guid)Membership.GetUser().ProviderUserKey);
budget.DepartmentBudget(emp.Dept.MonthlyBudget, emp.Dept.DepartmentId);
Session["budget"] = budget;
}
UpdateBudgetMessage();
rptExpenseReport.DataSource = expDAL.GetReportsByDepartment(emp.Dept.DepartmentId, ReportStatus.Submitted.ToString());
rptExpenseReport.DataBind();
}
