public AttendanceLogSearchResultObject GetListAttendanceLog(AttendanceLogSearchCondition model) { model.DateFrom = DateTime.ParseExact(model.DateFrom.ToString("dd/MM/yyyy") + " " + model.TimeFrom + ":00.000", "dd/MM/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture); model.DateTo = DateTime.ParseExact(model.DateTo.ToString("dd/MM/yyyy") + " " + model.TimeTo + ":59.999", "dd/MM/yyyy HH:mm:ss.fff", CultureInfo.InvariantCulture); try { var listAttendanceLog = (from a in db.AttendanceLog.AsNoTracking() join e in db.Employee.AsNoTracking() on a.EmployeeId equals e.EmployeeId into g from ae in g.DefaultIfEmpty() join d in db.Department.AsNoTracking() on ae.DepartmentId equals d.DepartmentId into gr from aed in gr.DefaultIfEmpty() join f in db.JobTitle.AsNoTracking() on ae.JobTitleId equals f.JobTitleId into af from af1 in af.DefaultIfEmpty() where (string.IsNullOrEmpty(model.EmployeeCode) || ae.Code.Contains(model.EmployeeCode)) && (string.IsNullOrEmpty(model.EmployeeName) || ae.Name.Contains(model.EmployeeName)) && (string.IsNullOrEmpty(model.CameraIPAddress) || a.CameraIPAdress.Contains(model.CameraIPAddress)) && (string.IsNullOrEmpty(model.DepartmentId) || ae.DepartmentId.Equals(model.DepartmentId)) && (model.FaceCount == null || a.FaceCount == model.FaceCount) orderby a.Date descending select new AttendanceLogSearchResult() { AttendanceLogId = a.AttendanceLogId, EmployeeName = ae.Name, EmployeeId = a.EmployeeId, EmployeeCode = ae.Code, DepartmentName = aed.Name, JobTitleName = af1.Name, Date = (DateTime)a.Date, Confidence = a.Confidence, CameraIPAddress = a.CameraIPAdress, ImageLink = a.ImageLink, ObjSelect = false, FaceCount = a.FaceCount, ImageFace = a.ImageFace, }).AsQueryable(); listAttendanceLog = listAttendanceLog.Where(r => r.Date <= model.DateTo); listAttendanceLog = listAttendanceLog.Where(r => r.Date >= model.DateFrom); listAttendanceLog = listAttendanceLog.Where(r => r.Confidence >= model.ConfidenceFrom); listAttendanceLog = listAttendanceLog.Where(r => r.Confidence <= model.ConfidenceTo); var count = listAttendanceLog.Select(u => u.AttendanceLogId).ToList().Count; var listResult = SQLHelpper.OrderBy(listAttendanceLog, model.OrderBy, model.OrderType).Skip((model.PageNumber - 1) * model.PageSize).Take(model.PageSize).ToList(); AttendanceLogSearchResultObject result = new AttendanceLogSearchResultObject() { ListResult = listResult, TotalItem = count }; return(result); } catch (Exception ex) { throw new ErrorException(ErrorMessage.ERR001, ex.InnerException); } }
public HttpResponseMessage ExportExcel(AttendanceLogSearchCondition model) { try { AttendanceLogSearchResultObject result = attendanceLogBusiness.ExportExcel(model); return(Request.CreateResponse(HttpStatusCode.OK, result)); } catch (Exception ex) { return(Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message)); } }
public AttendanceLogSearchResultObject GetListNoAttendanceLog(NoAttendanceLogSearchCondition model) { try { List <string> listEmployeeId = new List <string>(); listEmployeeId = (from t in db.TimeAttendanceLog.AsNoTracking() where model.DateFrom <= t.Date && t.Date <= model.DateTo select t.EmployeeId).ToList(); var listEmployee = (from e in db.Employee.AsNoTracking() join d in db.Department.AsNoTracking() on e.DepartmentId equals d.DepartmentId join j in db.JobTitle.AsNoTracking() on e.JobTitleId equals j.JobTitleId where (string.IsNullOrEmpty(model.DepartmentId) || e.DepartmentId.Equals(model.DepartmentId)) && (string.IsNullOrEmpty(model.JobTitleId) || e.JobTitleId.Equals(model.JobTitleId)) && (string.IsNullOrEmpty(model.EmployeeCode) || e.Code.Contains(model.EmployeeCode)) && (string.IsNullOrEmpty(model.EmployeeName) || e.Name.Contains(model.EmployeeName)) && !listEmployeeId.Contains(e.EmployeeId) select new AttendanceLogSearchResult() { EmployeeId = e.EmployeeId, EmployeeName = e.Name, EmployeeCode = e.Code, DepartmentName = d.Name, JobTitleName = j.Name, }).AsQueryable(); var count = listEmployee.ToList().Count; var listResult = SQLHelpper.OrderBy(listEmployee, model.OrderBy, model.OrderType).Skip((model.PageNumber - 1) * model.PageSize).Take(model.PageSize).ToList(); AttendanceLogSearchResultObject result = new AttendanceLogSearchResultObject() { ListResult = listResult, TotalItem = count }; return(result); } catch (Exception ex) { throw new ErrorException(ErrorMessage.ERR001, ex.InnerException); } }
public AttendanceLogSearchResultObject ExportExcel(NoAttendanceLogSearchCondition model) { //Khởi tạo Excel ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; string pathClient = HttpContext.Current.Server.MapPath("/Template/ThongkeLuotVaoRa.xlsx"); IWorkbook workbook = application.Workbooks.Open(pathClient); IWorksheet sheet = workbook.Worksheets[0]; //Khởi tạo dữ liệu Model try { int index = 1; AttendanceLogSearchResultObject result = GetListNoAttendanceLog(model); var list = result.ListResult; foreach (var e in list) { if (string.IsNullOrEmpty(e.EmployeeCode)) { e.EmployeeCode = "Unknow"; } if (string.IsNullOrEmpty(e.EmployeeName)) { e.EmployeeName = "Unknow"; } if (string.IsNullOrEmpty(e.DepartmentName)) { e.DepartmentName = "Unknow"; } e.DateString = e.Date.ToString("dd/MM/yyyy HH:mm:ss"); } var listExport = (from p in list select new { Index = index++, p.EmployeeCode, p.EmployeeName, p.DepartmentName, p.DateString, p.Confidence, p.CameraIPAddress, }).ToList(); if (listExport.Count > 0) { sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; sheet.Range[6, 2, 6 + listExport.Count, 8].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; sheet.ImportData(listExport, 7, 2, false); } //Xuất Excel string pathExport = "/Template/Export/" + DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "ThongkeVaoRa.xlsx"; workbook.SaveAs(HttpContext.Current.Server.MapPath(pathExport)); workbook.Close(); excelEngine.Dispose(); result.PathExport = pathExport; return(result); } catch (Exception ex) { throw new ErrorException(ErrorMessage.ERR001, ex.InnerException); } }