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();
            }
        }