// Reads a CSV that is formatted as comma separated values per line // The first line must contain the legend public static CSVResult ReadCSVFile(string filePath) { FileStream fileStream = FileUtils.OpenFileStream(filePath, FileMode.Open, FileAccess.Read); if (fileStream == null) { return(null); } StreamReader streamReader = new StreamReader(fileStream); if (streamReader.Peek() < 0) { TimiDebug.LogErrorColor("Empty file", LogColor.grey); return(null); } CSVResult result = new CSVResult(); // Read the legend from the first line string legendLine = streamReader.ReadLine(); string[] legend = legendLine.Split(','); for (int i = 0; i < legend.Length; ++i) { result.keysPerItem.Add(legend[i]); } int lineNumber = 0; while (streamReader.Peek() >= 0) { ++lineNumber; string line = streamReader.ReadLine(); string[] words = line.Split(','); if (result.keysPerItem.Count != words.Length) { TimiDebug.LogWarningColor("Malformed item on line number: " + lineNumber, LogColor.grey); continue; } CSVItem item = new CSVItem(); for (int i = 0; i < result.keysPerItem.Count; ++i) { item.values[result.keysPerItem[i]] = words[i]; } result.items.Add(item); } fileStream.Close(); return(result); }
public CSVResult <IEnumerable <T> > ProcessCSV <T>(Stream csvStream) { var result = new CSVResult <IEnumerable <T> > { Output = new List <T>() }; var records = new List <T>(); using (var reader = new StreamReader(csvStream)) { var malformedRow = false; var configuration = new CsvConfiguration(CultureInfo.InvariantCulture) { Delimiter = Seperator.ToString(), BadDataFound = context => { malformedRow = true; result.FailedCount++; Debug.Print(context.RawRecord); } }; using var csv = new CsvReader(reader, configuration); csvStream.Position = 0; CsvHelperMapper.RegisterClassMaps(csv.Context); while (csv.Read()) { try { var record = csv.GetRecord <T>(); if (!malformedRow) { records.Add(record); result.SuccessCount++; } } catch { result.FailedCount++; } result.ProcessedCount++; malformedRow = false; } } result.Output = records; return(result); }
// GET: AttendanceRecords public async Task <IActionResult> Index(string sortOrder, string currentFilter, string searchString, int?pageNumber, string infoMessage, DateTime selectedMonth, bool getAsCsv) { ViewData["CurrentSort"] = sortOrder; ViewData["DateSortParm"] = string.IsNullOrEmpty(sortOrder) ? "date" : ""; ViewData["NameSortParm"] = sortOrder == "name" ? "name_desc" : "name"; ViewData["ApprovalStatusSortParm"] = sortOrder == "approval" ? "approval_desc" : "approval"; ViewData["ManagerApprovalControlDisabled"] = true; ViewData["InfoMessage"] = infoMessage; if (searchString != null) { pageNumber = 1; } else { searchString = currentFilter; } _logger.LogInformation($"Request month value: {selectedMonth}"); if ((selectedMonth == null) || (selectedMonth == DateTime.MinValue)) { selectedMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); } ViewData["SelectedMonth"] = $"{selectedMonth.Year}-{selectedMonth.ToString("MM")}"; var daysInMonth = DateTime.DaysInMonth(selectedMonth.Year, selectedMonth.Month); ViewData["CurrentFilter"] = searchString; var currentUserId = _userManager.GetUserId(User); var attendanceRecords = _context.AttendanceRecords.Where(ar => ar.WorkDay >= selectedMonth && ar.WorkDay < selectedMonth.AddDays(daysInMonth)) .Include(p => p.Employee) .ThenInclude(e => e.Team) .AsNoTracking(); attendanceRecords = await GetAttendanceRecordsInScope(currentUserId, attendanceRecords); if (!String.IsNullOrEmpty(searchString)) { attendanceRecords = attendanceRecords.Where(ar => (ar.Employee.LastName + ar.Employee.FirstName).Contains(searchString)); } switch (sortOrder) { case "name_desc": attendanceRecords = attendanceRecords.OrderByDescending(ar => ar.Employee.LastName + ar.Employee.FirstName); break; case "name": attendanceRecords = attendanceRecords.OrderBy(ar => ar.Employee.LastName + ar.Employee.FirstName); break; case "approval_desc": attendanceRecords = attendanceRecords.OrderByDescending(ar => ar.ManagerApprovalStatus); break; case "approval": attendanceRecords = attendanceRecords.OrderBy(ar => ar.ManagerApprovalStatus); break; case "date": attendanceRecords = attendanceRecords.OrderBy(ar => ar.WorkDay); break; default: attendanceRecords = attendanceRecords.OrderByDescending(ar => ar.WorkDay); break; } if (getAsCsv) { DataTable exportTable = GetAttendanceRecordsAsDataTable(attendanceRecords); var csvResult = new CSVResult(exportTable, $"{currentUserId}_{DateTime.Now.ToString(CultureInfo.InvariantCulture)}.csv"); return(csvResult); } else { return(View(await PaginatedListViewModel <AttendanceRecordModel> .CreateAsync(attendanceRecords, pageNumber ?? 1, CommonConstants.PAGE_SIZE))); } }
// GET: Payroll summary for selected employees public async Task <IActionResult> PayrollSummary(string searchString, DateTime selectedMonth, string currentFilter, bool getAsCsv) { _logger.LogInformation($"Request month value: {selectedMonth}"); if ((selectedMonth == null) || (selectedMonth == DateTime.MinValue)) { selectedMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); } ViewData["SelectedMonth"] = $"{selectedMonth.Year}-{selectedMonth.ToString("MM")}"; var daysInMonth = DateTime.DaysInMonth(selectedMonth.Year, selectedMonth.Month); if (searchString == null) { searchString = currentFilter; } ViewData["CurrentFilter"] = searchString; var currentUserId = _userManager.GetUserId(User); var attendanceRecords = _context.AttendanceRecords.Where(ar => ar.WorkDay >= selectedMonth && ar.WorkDay < selectedMonth.AddDays(daysInMonth)) .Where(ar => ar.ManagerApprovalStatus == ManagerApprovalStatus.Approved) .Include(p => p.Employee) .ThenInclude(e => e.Team) .AsNoTracking(); attendanceRecords = await GetAttendanceRecordsInScope(currentUserId, attendanceRecords); if (!String.IsNullOrEmpty(searchString)) { attendanceRecords = attendanceRecords.Where(ar => (ar.Employee.LastName + ar.Employee.FirstName).Contains(searchString)); } var byEmployeeIDResults = new Dictionary <string, Dictionary <string, int> >(); var arByUsername = attendanceRecords.ToList().GroupBy(ar => ar.EmployeeId); foreach (var group in arByUsername) { var afternoonSummary = group.GroupBy(ar => ar.AfternoonAttendance.ToString(), ar => ar.AfternoonAttendance, (afternoon, allAfternoons) => new { key = afternoon, countAR = allAfternoons.Count() }); var morningSummary = group.GroupBy(ar => ar.MorningAttendance.ToString(), ar => ar.MorningAttendance, (morning, allMornings) => new { key = morning, countAR = allMornings.Count() }); byEmployeeIDResults[group.Key] = new Dictionary <string, int>(); foreach (var attendance in Enum.GetNames(typeof(Attendance))) { byEmployeeIDResults[group.Key].Add(attendance, 0); byEmployeeIDResults[group.Key][attendance] += afternoonSummary.Any(x => x.key == attendance) ? afternoonSummary.First(x => x.key == attendance).countAR : 0; byEmployeeIDResults[group.Key][attendance] += morningSummary.Any(x => x.key == attendance) ? morningSummary.First(x => x.key == attendance).countAR : 0; } } DataTable exportTable = await GetSummaryResultsAsDataTable(byEmployeeIDResults, selectedMonth); if (getAsCsv) { var csvResult = new CSVResult(exportTable, $"{currentUserId}_{DateTime.Now.ToString(CultureInfo.InvariantCulture)}.csv"); return(csvResult); } else { List <PayrollSummaryViewModel> payrollSummaryList = ConvertDataTableToPayrollSummaryList(exportTable); //return View(await PaginatedList<PayrollSummaryModel>.Create(payrollSummaryList.AsQueryable<PayrollSummaryModel>(), pageNumber ?? 1, CommonConstants.PAGE_SIZE)); return(View(payrollSummaryList)); } }