/// <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> GetExpenseReportsBySupervisor(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);
//string query = string.Format("SELECT ApprovedById, COUNT(ExpenseId) AS AmountApproved FROM ExpenseHeader WHERE Status ='ApprovedByAccounts' GROUP BY ApprovedById");
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 Page_Load(object sender, EventArgs e)
{
// disable all navigation items on page load
navConsultant.Visible = false;
navSupervisor.Visible = false;
navAccounts.Visible = false;
// create an array which stores a list of roles the current user is a member of
string[] myRoles;
myRoles = Roles.GetRolesForUser();
//enable navigation items for each role the current users is a member of
if (myRoles.Length > 0)
{
EmployeeDAL employeeDAL = new EmployeeDAL();
//Employee emp = new Employee();
Session["emp"] = employeeDAL.GetEmployee((Guid)Membership.GetUser().ProviderUserKey);
foreach (string role in myRoles)
{
if (role == "Consultant")
{
// Employee emp = new Employee();
//Session["userId"] = emp.FetchUserId(HttpContext.Current.User.Identity.Name);
navConsultant.Visible = true;
}
else if (role == "Supervisor")
{
navSupervisor.Visible = true;
}
else if (role == "Accounts")
{
navAccounts.Visible = true;
}
}
}
else
{
maincontent.InnerText = "User " + HttpContext.Current.User.Identity.Name + " is not a member of any roles!";
}
}
/// <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");
}
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();
}