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