public ActionResult DownloadExcel2(DateTime?getdate) { List <hik> passexel = new List <hik>(); var month = getdate; var tmlist = this.db.hiks.OrderBy(x => x.datetime).ToList(); var msname = this.db.master_file.ToList(); var emplist = new List <hik>(); var leavelist = db.Leaves.ToList(); foreach (var hik in tmlist) { if (!emplist.Exists(x => x.ID == hik.ID)) { if (hik.ID != string.Empty) { emplist.Add(hik); var qwEmployeeId = 0; int.TryParse(hik.ID, out qwEmployeeId); if (msname.Exists(x => x.employee_no == qwEmployeeId)) { hik.Person = msname.Find(x => x.employee_no == qwEmployeeId).employee_name; } } } } var abslist = new List <hik>(); if (month.HasValue) { var startdate = new DateTime(month.Value.Year, month.Value.Month, 1); var enddate = new DateTime( month.Value.Year, month.Value.Month, DateTime.DaysInMonth(month.Value.Year, month.Value.Month)); while (startdate != enddate) { if (startdate >= DateTime.Now.Date) { break; } foreach (var hik in emplist) { if (!tmlist.Exists(x => x.ID == hik.ID && x.date == startdate)) { if (startdate.DayOfWeek != DayOfWeek.Friday) { if (startdate.DayOfWeek != DayOfWeek.Saturday) { var emp = new hik(); var newdate = new DateTime(startdate.Year, startdate.Month, startdate.Day); emp.ID = hik.ID; emp.EMPID = hik.EMPID; emp.Person = hik.Person; emp.date = newdate; var qwEmployeeId = 0; int.TryParse(hik.ID, out qwEmployeeId); if (!leavelist.Exists( x => x.Start_leave <= startdate && x.End_leave >= startdate && x.master_file.employee_no == qwEmployeeId)) { abslist.Add(emp); } } } } } startdate = startdate.AddDays(1); } } passexel = abslist; var Ep = new ExcelPackage(); var Sheet = Ep.Workbook.Worksheets.Add(getdate.ToString()); Sheet.Cells["A1"].Value = "employee no"; Sheet.Cells["B1"].Value = "employee name"; Sheet.Cells["C1"].Value = "date "; var row = 2; foreach (var item in passexel.OrderBy(x => x.date).ThenBy(x => x.EMPID)) { Sheet.Cells[string.Format("A{0}", row)].Value = item.ID; Sheet.Cells[string.Format("B{0}", row)].Value = item.Person; Sheet.Cells[string.Format("C{0}", row)].Value = item.date.Value.ToString("dd MMM yyyy"); row++; } Sheet.Cells["A:AZ"].AutoFitColumns(); this.Response.Clear(); this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Response.AddHeader("content-disposition", "filename =absences.xlsx"); this.Response.BinaryWrite(Ep.GetAsByteArray()); this.Response.End(); return(this.RedirectToAction("Index")); }
public ActionResult Absent(DateTime?getdate, string empnos) { var month = getdate; this.ViewBag.eddate = getdate; var tmlist = this.db.hiks.OrderBy(x => x.datetime).ToList(); var msname = this.db.master_file.ToList(); var emplist = new List <hik>(); var leavelist = db.Leaves.ToList(); foreach (var hik in tmlist) { if (!emplist.Exists(x => x.ID == hik.ID)) { if (hik.ID != string.Empty) { emplist.Add(hik); var qwEmployeeId = 0; int.TryParse(hik.ID, out qwEmployeeId); if (msname.Exists(x => x.employee_no == qwEmployeeId)) { hik.Person = msname.Find(x => x.employee_no == qwEmployeeId).employee_name; } hik.EMPID = qwEmployeeId; } } } var abslist = new List <hik>(); if (month.HasValue) { var startdate = new DateTime(month.Value.Year, month.Value.Month, 1); var enddate = new DateTime( month.Value.Year, month.Value.Month, DateTime.DaysInMonth(month.Value.Year, month.Value.Month)); var holidays = db1.Holidays.Where(x => x.Date.Value.Month == month.Value.Month && x.Date.Value.Year == month.Value.Year).ToList(); while (startdate <= enddate) { if (startdate >= DateTime.Now.Date) { break; } foreach (var hik in emplist) { if (!tmlist.Exists(x => x.ID == hik.ID && x.date == startdate)) { if (startdate.DayOfWeek != DayOfWeek.Friday) { if (startdate.DayOfWeek != DayOfWeek.Saturday) { if (!holidays.Exists(x => x.Date == startdate)) { var emp = new hik(); var newdate = new DateTime(startdate.Year, startdate.Month, startdate.Day); emp.ID = hik.ID; emp.EMPID = hik.EMPID; emp.Person = hik.Person; emp.date = newdate; var qwEmployeeId = 0; int.TryParse(hik.ID, out qwEmployeeId); ViewBag.absapp = empnos; if (!empnos.IsNullOrWhiteSpace() && empnos.Contains(hik.ID + "(" + newdate.ToString() + ")")) { emp.absence_approved = true; } if (!leavelist.Exists(x => x.Start_leave <= startdate && x.End_leave >= startdate && x.master_file.employee_no == qwEmployeeId)) { abslist.Add(emp); } } } } } } startdate = startdate.AddDays(1); } } return(this.View(abslist.OrderBy(x => x.date).ThenBy(x => x.EMPID))); }