public FileStreamResult ajax_getReportsExl2(string sDate, string datas, int SortBy = 0, int SortHow = 0 )
{
WebClient client = new WebClient();
SLDocument sl = new SLDocument();
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "CSAttendanceReports");
SLStyle style1 = sl.CreateStyle();
style1.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
SLStyle style2 = sl.CreateStyle();
style2.Font.Bold = true;
DateTime date1;
DateTime.TryParse(sDate, out date1);
if (date1 == null)
throw new Exception("<i>Invalid date!</i>");
bool isTL = UserPermission.IsAuthorized(SessionHelper.EmployeeId, PermissionType.VIEW_SHIFT_TL);
var taskTypes = csTaskTrackerDb.TaskTypes;
if (empId == 0)
empId = SessionHelper.EmployeeId;
DateTime dtSDate = Convert.ToDateTime(sDate);
var thisEmp = EmpDb.Employees.Find(empId);
var Dept = EmpDb.Departments.Find(thisEmp.DepartmentID).DepartmentID;
List<AgentAttendance> reports = new List<AgentAttendance>();
List<int> columnTotal = new List<int>();
JObject object_data = JObject.Parse(datas);
DateTime endDate = dtSDate.AddDays(1); //end of date
if (Dept > -1)
{
var employees = EmpDb.Employees.Where(m => (Dept == 0 ? true : m.DepartmentID == Dept) && (m.LeaveDate == null || m.LeaveDate > DateTime.Today)); //and also, comment these...
foreach (var emp in employees)
{
int rowTotal = 0;
AgentAttendance report = new AgentAttendance();
report.empID = emp.EmployeeID;
var currentStatus = csTaskTrackerDb.AgentStatus.Where(m => m.EmployeeId == emp.EmployeeID && m.TimeChanged >= dtSDate.Date).OrderByDescending(m => m.ID).FirstOrDefault();
int status = 0;
if (currentStatus != null)
status = (int)currentStatus.StatusId;
string iconClass = "";
string iconColor = "";
report.empName = emp.Firstname + " " + emp.Lastname; //column for name
int index = 0;
var empTask = (from task in csTaskTrackerDb.CSTasks where (task.EmployeeID == emp.EmployeeID && task.StartDate == dtSDate) join ctask in csTaskTrackerDb.CSTaskTypes on task.TaskID equals ctask.CSTaskId select ctask.CSTaskDescription).FirstOrDefault();
report.task = empTask == null ? "NONE" : empTask; //Task
var thisEmpShift = Helpers.Helper.GetShift(emp.EmployeeID, emp.DepartmentID.GetValueOrDefault(), dtSDate);
var thisEmpAttendance = csTaskTrackerDb.Attendances.Find(empId, dtSDate);
var thisEmpRemarks = csTaskTrackerDb.CSRemarks.Where(x => x.EmployeeID == empId).FirstOrDefault();
DateTime ShiftStartTime = dtSDate.AddHours(Convert.ToDouble(thisEmpShift.StartTime)).AddHours(-3);
DateTime ShiftEndTime = dtSDate.AddHours(Convert.ToDouble(thisEmpShift.EndTime)).AddHours(+3);
var emp_time = object_data["data"].Children()
.Where(e => e["badgeNumber"].ToString() == emp.EmployeeCode && DateTime.Parse(e["time"].ToString()) > DateTime.Parse(ShiftStartTime.ToString())
&& DateTime.Parse(e["time"].ToString()) < DateTime.Parse(ShiftEndTime.ToString())).Select(t => t["time"]).FirstOrDefault();
report.shiftEndTime = ShiftEndTime; // shift end time;
report.shiftStartTime = ShiftStartTime; // shift start time;
report.shiftCode = thisEmpShift == null ? "Off" : thisEmpShift.Code; // Shift Code
report.time_in = emp_time == null ? "---" : Convert.ToDateTime(emp_time.ToString()).ToString("h:mm tt"); //time in
report.status = emp_time == null ? "ABSENT" : "PRESENT"; //status
report.remarks = (thisEmpRemarks == null ? "NONE" : thisEmpRemarks.Remarks); //remarks
report.employeeCode = emp.EmployeeCode;
reports.Add(report);
}
string sortByStr = "";
switch (SortBy)
{
case 1: sortByStr = "empName"; break;
case 2: sortByStr = "task"; break;
case 3: sortByStr = "shiftCode"; break;
case 4: sortByStr = "timeIn"; break;
case 5: sortByStr = "status"; break;
case 6: sortByStr = "remarks"; break;
}
if (SortBy > 0)
{
if (SortHow == 0)
reports = reports.OrderBy(m => m.GetType().GetProperty(sortByStr).GetValue(m, null)).ToList();
else
reports = reports.OrderByDescending(m => m.GetType().GetProperty(sortByStr).GetValue(m, null)).ToList();
}
}
sl.SetCellValue(1, 1, "Name");
sl.SetColumnWidth(1, 1, 30);
sl.SetCellValue(1, 2, "Task");
sl.SetColumnWidth(1, 2, 10);
sl.SetCellValue(1, 3, "Shift");
sl.SetColumnWidth(1, 3, 30);
sl.SetCellValue(1, 4, "Time In");
sl.SetColumnWidth(1, 4, 20);
sl.SetCellValue(1, 5, "Status");
sl.SetColumnWidth(1, 5, 10);
sl.SetCellValue(1, 6, "Remarks");
int c = 3;
int r = 2;
foreach (var rep in reports)
{
bool isFirst = true;
c = 1;
sl.SetCellValue(++r, c, rep.empName);
sl.SetCellValue(r, ++c, rep.task);
sl.SetCellValue(r, ++c, rep.shiftCode);
sl.SetCellValue(r, ++c, rep.time_in);
sl.SetCellValue(r, ++c, rep.status);
sl.SetCellValue(r, ++c, rep.remarks);
}
sl.AddWorksheet("Attendance Data");
int c1 = 2;
int r1 = 1;
foreach(var reps in reports) {
c1 = 1;
var attendance = object_data["data"].Children().Where(t => t["badgeNumber"].ToString() == reps.employeeCode && DateTime.Parse(t["time"].ToString()) > DateTime.Parse(reps.shiftStartTime.ToString())
&& DateTime.Parse(t["time"].ToString()) < DateTime.Parse(reps.shiftEndTime.ToString()));
sl.SetCellValue(1, 1, "Name");
sl.SetColumnWidth(1, 1, 30);
sl.SetCellValue(1, 2, "Employee Number");
sl.SetColumnWidth(1, 2, 10);
sl.SetCellValue(1, 3, "Transtype");
sl.SetColumnWidth(1, 3, 20);
sl.SetCellValue(1, 4, "Time");
sl.SetColumnWidth(1, 4, 30);
foreach(var att in attendance)
{
c1 = 1;
var emplname = att["name"].ToString();
var bagde = att["badgeNumber"].ToString();
var timeIN = Convert.ToDateTime(att["time"].ToString()).ToString("h:mm tt");
var transtype = (att["transtype"].ToString() == "1" ? "Time In" : "Time Out");
sl.SetCellValue(++r1, c1, reps.empName);
sl.SetCellValue(r1, ++c1, bagde);
sl.SetCellValue(r1, ++c1, transtype);
sl.SetCellValue(r1, ++c1, timeIN);
}
}
var ms = new MemoryStream();
sl.SaveAs(ms);
ms.Position = 0;
return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CSAttendanceReports.xlsx");
}
}