public void ActionOnReport(int? expenseId, Employee employee,ReportStatus status)
{
//IEmployeeService employeeService = new EmployeeService();
//Employee employee = employeeService.GetEmployee((int)Membership.GetUser().ProviderUserKey);
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var report = (from ExpReport in ctx.ExpenseReports
where ExpReport.ExpenseId == expenseId
select ExpReport).FirstOrDefault();
//if (action == "Approve")
//{
report.ApprovedDate = DateTime.Now;
//report.Status = "ApprovedBySupervisor";
report.Status = status.ToString();
report.ApprovedById = employee.UserId;
ctx.SaveChanges();
//}
//else
//{
// report.ApprovedDate = DateTime.Now;
// report.Status = "RejectedBySupervisor";
// report.ApprovedById = employee.UserId;
// ctx.SaveChanges();
//}
}
}
public Employee GetEmployee(int userId)
{
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var query = from employee in ctx.Employees.Include("Department")
where employee.UserId == userId
select employee;
return (Employee)query.First();
}
}
public decimal? CheckReportTotal(int? expenseId)
{
decimal? reportTotal = 0;
using(EMEntitiesContext ctx=new EMEntitiesContext())
{
var report=(from reports in ctx.ExpenseReports.Include("ExpenseItems")
where reports.ExpenseId==expenseId
select reports).Single();
foreach (var ExpenseItem in report.ExpenseItems)
{
reportTotal = reportTotal + ExpenseItem.AudAmount;
}
return reportTotal;
}
}
public List<AmountProcessedSupervisor> GetAmountSupervisor()
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var totalExpenseInOneReport = from expense in ctx.ExpenseItems
group expense by expense.ExpenseId into exp
select new
{
expID = exp.Key,
totalAmount = exp.Sum(expense => expense.AudAmount)
};
var reportsApprovedInCurrentMonth = from report in ctx.ExpenseReports
join ex in totalExpenseInOneReport
on report.ExpenseId equals ex.expID
where report.CreateDate.Value.Month == month && report.CreateDate.Value.Year == year && report.Status == "ApprovedByAccounts"
select new
{
supervisorID = report.ApprovedById,
total = ex.totalAmount
};
var totalExpenseApprovedBySupervisor = from report in reportsApprovedInCurrentMonth
group report by report.supervisorID into rep
select new
{
supervisorID = rep.Key,
total = rep.Sum(report => report.total)
};
var result = from emp in ctx.Employees
join spent in totalExpenseApprovedBySupervisor
on emp.UserId equals spent.supervisorID
select new AmountProcessedSupervisor
{
Fullname = emp.Firstname + " " + emp.Surname,
amountApproved = spent.total
};
return (List<AmountProcessedSupervisor>)result.ToList();
}
}
public decimal? TotalExpenseProcessByCompany(int month, int year)
{
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
decimal? total = 0;
var reports = from expenseReports in ctx.ExpenseReports
where expenseReports.ProcessedDate.Value.Month == month && expenseReports.ProcessedDate.Value.Year == year
&& expenseReports.Status == "ApprovedByAccounts"
select expenseReports;
foreach (var expenseItems in reports)
{
foreach (var totalamount in expenseItems.ExpenseItems)
{
total = total + totalamount.AudAmount;
}
}
return total;
}
}
public decimal? TotalExpenseAmountByDept(int? departmentID)
{
int month=DateTime.Now.Month;
int year=DateTime.Now.Year;
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
decimal? total = 0;
var reports= from expenseReports in ctx.ExpenseReports
where expenseReports.ApprovedDate.Value.Month==month && expenseReports.ApprovedDate.Value.Year==year && expenseReports.Department.DepartmentId==departmentID && (expenseReports.Status=="ApprovedBySupervisor"||expenseReports.Status=="ApprovedByAccounts")
select expenseReports;
foreach(var expenseItems in reports)
{
foreach(var totalamount in expenseItems.ExpenseItems)
{
total = total + totalamount.AudAmount;
}
}
return total;
}
}
public decimal? GetDepartmentMonthlySpendCommitted(int month, int year, Department department)
{
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
decimal? total = 0;
var reports = from expenseReports in ctx.ExpenseReports
where expenseReports.ProcessedDate.Value.Month == month && expenseReports.ProcessedDate.Value.Year == year
&& expenseReports.ProcessedById != null && department.DepartmentId == expenseReports.Department.DepartmentId
&& expenseReports.Status == "ApprovedByAccounts" // John June 3
select expenseReports;
foreach (var expenseItems in reports)
{
foreach (var totalamount in expenseItems.ExpenseItems)
{
total = total + totalamount.AudAmount;
}
}
return total;
}
}
public List<ExpenseReport> GetExpenseReportByConsultant(string status, Employee consultant)
{
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var result = from report in ctx.ExpenseReports.Include("CreatedBy").Include("ExpenseItems").Include("Department")
where report.CreatedBy.UserId == consultant.UserId && report.Status == status
select report;
return (List<ExpenseReport>)result.ToList();
}
}
public void ProcessReport(int? expenseId, Employee employee, ReportStatus status)
{
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var report = (from ExpReport in ctx.ExpenseReports
where ExpReport.ExpenseId == expenseId
select ExpReport).FirstOrDefault();
report.ProcessedDate = DateTime.Now;
report.Status = status.ToString();
report.ProcessedById = employee.UserId;
ctx.SaveChanges();
}
}
public void InsertExpenseReport(ExpenseReport report)
{
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
ExpenseReport newReport = new ExpenseReport();
newReport.CreatedBy = ctx.Employees.First(e => e.UserId == report.CreatedBy.UserId);
newReport.CreateDate = report.CreateDate;
newReport.Department = ctx.Departments.First(d => d.DepartmentId == report.Department.DepartmentId);
newReport.Status = ReportStatus.Submitted.ToString();
newReport.ExpenseItems = report.ExpenseItems;
ctx.ExpenseReports.Add(newReport);
ctx.SaveChanges();
}
}
public List<ExpenseReport> GetReportsBySupervisor(string status)
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
IEmployeeService employeeService = new EmployeeService();
Employee employee = employeeService.GetEmployee((int)Membership.GetUser().ProviderUserKey);
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var result = (from i in ctx.ExpenseReports.Include("CreatedBy").Include("ExpenseItems").Include("Department").Include("ApprovedBy")
where i.Department.DepartmentId == employee.Department.DepartmentId && i.CreateDate.Value.Month == month && i.CreateDate.Value.Year == year && i.Status == status
select i);
return (List<ExpenseReport>)result.ToList();
}
}
public List<ExpenseReport> GetReportsByAccounts(string status)
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
using (EMEntitiesContext ctx = new EMEntitiesContext())
{
var result = (from report in ctx.ExpenseReports.Include("CreatedBy").Include("ExpenseItems").Include("Department").Include("ApprovedBy")
where report.CreateDate.Value.Month == month && report.CreateDate.Value.Year == year && report.Status == status
select report);
return (List<ExpenseReport>)result.ToList();
}
}