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