/// <summary>
/// Helper method for mass importing employees data from csv files
/// </summary>
public static async Task ImportValidationCsvAsync()
{
var fileName = FileSystemsHelpers.GetFilePath();
var engine = new FileHelperAsyncEngine <Employee>();
var validationData = new List <Employee>();
using (engine.BeginReadFile(fileName))
{
validationData.AddRange(engine);
await DatabaseHelper.InsertManyAsync <Employee>(validationData, true);
}
}
public async void EncryptAndSaveTokenAsync(Tokens savedTokens)
{
var hashedToken = Crypto.EncryptStringAES(savedTokens.ApiToken, savedTokens.SecretPin);
var hashedSecret = Crypto.EncryptStringAES(savedTokens.SecretPin, savedTokens.SecretPin);
var hashedKeys = new Tokens()
{
ApiToken = hashedToken, SecretPin = hashedSecret
};
var numOfRows = await DatabaseHelper.Insert <Tokens>(hashedKeys, true);
FileSystemsHelpers.WriteToFile($"Api Key have been saved! {DateTime.Now}");
MessageBox.Show("Api token has been saved!", "Rhumbix Encrypt", MessageBoxButton.OK,
MessageBoxImage.Information);
}
/// <summary>
/// Multi task queues for calling Rhumbix Api
/// </summary>
/// <param name="queries">Query start date and end date</param>
public async void RhumbixApiTaskQueues(Queries queries)
{
var key = FetchApiToken(queries.Pin);
if (key == "")
{
MessageBox.Show("Invalid pin please try again", "Rhumbix error", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
FileSystemsHelpers.ClearAllTexts();
FileSystemsHelpers.WriteToFile($"Last imported time: {DateTime.Now}");
// If no date is selected, abort method call
if (queries.StartDate == "" || queries.EndDate == "")
{
return;
}
await RunQueryAsync(queries, "Timekeeping", key);
// Sentry method to validate success or short circuit
if (File.ReadLines(statusPath).Last() == "Access denied")
{
MessageBox.Show("Invalid API token credentials please try again", "Rhumbix error", MessageBoxButton.OK,
MessageBoxImage.Error);
return;
}
await RunQueryAsync(queries, "Shift Extra", key);
await RunQueryAsync(queries, "Employees", key);
await RunQueryAsync(queries, "Projects", key);
await RunQueryAsync(queries, "Cost Codes", key);
await RunQueryAsync(queries, "Absences", key);
await RunQueryAsync(queries, "Timekeeping History", key);
await RunQueryAsync(queries, "Shift Extra History", key);
await RunQueryAsync(queries, "Absences History", key);
MessageBox.Show("Import complete! Please review status menu for detailed results", "Rhumbix API Connector", MessageBoxButton.OK, MessageBoxImage.Information);
}
/// <summary>
/// Call Rhumbix Api utility function
/// </summary>
/// <param name="queries">Enum selection query type</param>
public async Task RunQueryAsync(Queries queries, string selection, string key)
{
var timeList = new List <Timekeeping>();
var shiftList = new List <ShiftExtra>();
var employeeList = new List <Employee>();
var projectList = new List <Project>();
var costCodesList = new List <CostCodes>();
var absencesList = new List <Absences>();
var timeHistoryList = new List <TimekeepingHistory>();
var shiftHistoryList = new List <ShiftExtraHistory>();
var absencesHistoryList = new List <AbsencesHistory>();
var ids = string.Empty;
switch (selection)
{
case "Timekeeping":
var timeResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.TimekeepingEntries,
queries.StartDate, queries.EndDate, string.Empty, key);
if (timeResults == null)
{
return;
}
foreach (var results in timeResults)
{
foreach (var result in results.Results)
{
timeList.Add((Timekeeping)result);
}
}
var numOfRows = await DatabaseHelper.InsertManyAsync <Timekeeping>(timeList, true);
FileSystemsHelpers.WriteToFile($"Number of timekeeping records imported: {numOfRows}");
break;
case "Shift Extra":
var shiftResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.ShiftExtraEntries,
queries.StartDate, queries.EndDate, string.Empty, key);
if (shiftResults == null)
{
return;
}
foreach (var results in shiftResults)
{
foreach (var result in results.Results)
{
result.AAType = result.EntryStore.AAType;
shiftList.Add((ShiftExtra)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <ShiftExtra>(shiftList, true);
FileSystemsHelpers.WriteToFile($"Number of shift extra records imported: {numOfRows}");
break;
case "Employees":
var employeesResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.Employees,
queries.StartDate, queries.EndDate, string.Empty, key);
if (employeesResults == null)
{
return;
}
foreach (var results in employeesResults)
{
foreach (var result in results.Results)
{
using (var conn = new SQLiteConnection(DatabaseHelper.DbFile))
{
employeeList.Add((Employee)result);
}
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <Employee>(employeeList, true);
FileSystemsHelpers.WriteToFile($"Number of employee records imported: {numOfRows}");
break;
case "Projects":
var projectsResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.Projects,
queries.StartDate, queries.EndDate, string.Empty, key);
if (projectsResults == null)
{
return;
}
foreach (var results in projectsResults)
{
foreach (var result in results.Results)
{
projectList.Add((Project)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <Project>(projectList, true);
FileSystemsHelpers.WriteToFile($"Number of projects records imported: {numOfRows}");
break;
case "Cost Codes":
var costCodesResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.CostCodes,
queries.StartDate, queries.EndDate, string.Empty, key);
if (costCodesResults == null)
{
return;
}
foreach (var results in costCodesResults)
{
foreach (var result in results.Results)
{
costCodesList.Add((CostCodes)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <CostCodes>(costCodesList, true);
FileSystemsHelpers.WriteToFile($"Number of cost codes records imported: {numOfRows}");
break;
case "Absences":
var absencesResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.Absences,
queries.StartDate, queries.EndDate, string.Empty, key);
if (absencesResults == null)
{
return;
}
foreach (var results in absencesResults)
{
foreach (var result in results.Results)
{
absencesList.Add((Absences)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <Absences>(absencesList, true);
FileSystemsHelpers.WriteToFile($"Number of absences records imported: {numOfRows}");
break;
case "Timekeeping History":
ids = GetIdArrays(RhumbixApi.QueryType.TimekeepingEntries).Trim(); // Call helper method to retrieve all unique ids
var timeHistoryResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.TimeKeepingHistory,
queries.StartDate, queries.EndDate, ids, key);
if (timeHistoryResults == null)
{
return;
}
foreach (var results in timeHistoryResults)
{
foreach (var result in results.Results)
{
timeHistoryList.Add((TimekeepingHistory)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <TimekeepingHistory>(timeHistoryList, true);
FileSystemsHelpers.WriteToFile($"Number of timekeeping history records imported: {numOfRows}");
break;
case "Shift Extra History":
ids = GetIdArrays(RhumbixApi.QueryType.ShiftExtraHistory).Trim(); // Call helper method to retrieve all unique ids
var shiftHistoryResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.ShiftExtraHistory,
queries.StartDate, queries.EndDate, ids, key);
if (shiftHistoryResults == null)
{
return;
}
foreach (var results in shiftHistoryResults)
{
foreach (var result in results.Results)
{
shiftHistoryList.Add((ShiftExtraHistory)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <ShiftExtraHistory>(shiftHistoryList, true);
FileSystemsHelpers.WriteToFile($"Number of shift extra history records imported: {numOfRows}");
break;
case "Absences History":
ids = GetIdArrays(RhumbixApi.QueryType.AbsencesHistory).Trim(); // Call helper method to retrieve all unique ids
var absencesHistoryResults = await RhumbixApi.GetQueryTypes <List <QueryResults> >(RhumbixApi.QueryType.AbsencesHistory,
queries.StartDate, queries.EndDate, ids, key);
if (absencesHistoryResults == null)
{
return;
}
foreach (var results in absencesHistoryResults)
{
foreach (var result in results.Results)
{
absencesHistoryList.Add((AbsencesHistory)result);
}
}
numOfRows = await DatabaseHelper.InsertManyAsync <AbsencesHistory>(absencesHistoryList, true);
FileSystemsHelpers.WriteToFile($"Number of absence history records imported: {numOfRows}");
break;
}
}
public async void TransformTurnerData()
{
var employeesList = DatabaseHelper.GetList <Employee>();
var projectsList = DatabaseHelper.GetList <Project>();
var costCodesList = DatabaseHelper.GetList <CostCodes>();
var timeList = DatabaseHelper.GetList <Timekeeping>();
var absencesList = DatabaseHelper.GetList <Absences>();
var timeHistoryList = DatabaseHelper.GetList <TimekeepingHistory>();
var absencesHistoryList = DatabaseHelper.GetList <AbsencesHistory>();
var turnerList = new List <Transform>();
try
{
Logger.Info("Transform Method");
// Transform timekeeping data
if (timeList != null)
{
foreach (var item in timeList)
{
// Employee query
var employeeQuery = employeesList
.Where(x => x.CompanySuppliedId == item.Employee)
.Select(x => new { x.FirstName, x.LastName, x.Trade, x.Classification })
.FirstOrDefault();
var firstName = employeeQuery?.FirstName;
var lastName = employeeQuery?.LastName;
var trade = employeeQuery?.Trade;
var classification = employeeQuery?.Trade;
// Project query
var projectName = projectsList
.Where(x => x.JobNumber == item.JobNumber)
.Select(x => x.Name)
.FirstOrDefault();
// Cost code query
var costCodeDescription = costCodesList
.Where(x => x.Code == item.CostCode)
.Select(x => x.Description)
.FirstOrDefault();
// Approval query
var approverQuery = timeHistoryList
.Where(x => x.Id == item.Id && x.Status == "SUPERVISOR_APPROVED")
.Select(x => new { x.ModifiedBy, x.HistoryDate })
.FirstOrDefault();
var approverDetails = employeesList
.Where(x => x.CompanySuppliedId == approverQuery?.ModifiedBy)
.Select(x => new { x.FirstName, x.LastName })
.FirstOrDefault();
var approvalStatus = "Pending";
var approverFirstName = "";
var approverLastName = "";
var approverName = "";
var dateApproved = "";
var timeApproved = "";
if (approverQuery != null)
{
approvalStatus = "Approved";
approverFirstName = approverDetails?.FirstName;
approverLastName = approverDetails?.LastName;
approverName = $"{approverFirstName} {approverLastName}";
dateApproved = Convert.ToDateTime(approverQuery.HistoryDate).Date.ToString();
timeApproved =
$"{Convert.ToDateTime(approverQuery.HistoryDate).Hour}:{Convert.ToDateTime(approverQuery.HistoryDate).Minute}";
}
if (item.StandardTimeMinutes > 0)
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ProjectNumber = item.JobNumber,
CostCode = item.CostCode,
ActivityType = "1000",
AttendanceType = "800",
CompanyCode = "3000",
Hours = (double)item.StandardTimeMinutes / 60,
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ProjectName = projectName,
CostCodeDescription = costCodeDescription,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
if (item.OverTimeMinutes > 0)
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ProjectNumber = item.JobNumber,
CostCode = item.CostCode,
ActivityType = "1015",
AttendanceType = "815",
CompanyCode = "3000",
Hours = (double)item.OverTimeMinutes / 60,
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ProjectName = projectName,
CostCodeDescription = costCodeDescription,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
if (item.DoubleTimeMinutes > 0)
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ProjectNumber = item.JobNumber,
CostCode = item.CostCode,
ActivityType = "1020",
AttendanceType = "820",
CompanyCode = "3000",
Hours = (double)item.DoubleTimeMinutes / 60,
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ProjectName = projectName,
CostCodeDescription = costCodeDescription,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
}
}
// Transform absences data
if (absencesList != null)
{
foreach (var item in absencesList)
{
// Employee query
var employeeQuery = employeesList
.Where(x => x.CompanySuppliedId == item.Employee)
.Select(x => new { x.FirstName, x.LastName, x.Trade, x.Classification })
.First();
var firstName = employeeQuery.FirstName;
var lastName = employeeQuery.LastName;
var trade = employeeQuery.Trade;
var classification = employeeQuery.Trade;
// Approval query
var approverQuery = absencesHistoryList
.Where(x => x.Id == item.Id && x.Status == "SUPERVISOR_APPROVED")
.Select(x => new { x.ModifiedBy, x.HistoryDate })
.FirstOrDefault();
var approverDetails = employeesList
.Where(x => x.CompanySuppliedId == approverQuery?.ModifiedBy)
.Select(x => new { x.FirstName, x.LastName })
.FirstOrDefault();
var approvalStatus = "Pending";
var approverFirstName = "";
var approverLastName = "";
var approverName = "";
var dateApproved = "";
var timeApproved = "";
if (approverQuery != null)
{
approvalStatus = "Approved";
approverFirstName = approverDetails?.FirstName;
approverLastName = approverDetails?.LastName;
approverName = $"{approverFirstName} {approverLastName}";
dateApproved = Convert.ToDateTime(approverQuery.HistoryDate).Date.ToString();
timeApproved =
$"{Convert.ToDateTime(approverQuery.HistoryDate).Hour}:{Convert.ToDateTime(approverQuery.HistoryDate).Minute}";
}
if (item.Type == "Holiday")
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ActivityType = "1000",
AbsenceType = "110",
AbsenceDescription = item.Type,
CompanyCode = "3000",
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
else if (item.Type == "Vacation")
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ActivityType = "1000",
AbsenceType = "100",
AbsenceDescription = item.Type,
CompanyCode = "3000",
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
else if (item.Type == "Sick")
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ActivityType = "1000",
AbsenceType = "300",
AbsenceDescription = item.Type,
CompanyCode = "3000",
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
else
{
turnerList.Add(
new Transform()
{
PersonnelNo = item.Id,
Date = item.ShiftDate,
ActivityType = "Error - Check Value",
AbsenceType = "Error - Check Value",
AbsenceDescription = item.Type,
CompanyCode = "3000",
FirstName = firstName,
LastName = lastName,
EmployeeName = $"{firstName} {lastName}",
Trade = trade,
Classification = classification,
ApprovalStatus = approvalStatus,
ApproverFirstName = approverFirstName,
ApproverLastName = approverLastName,
ApproverName = approverName,
DateApproved = dateApproved,
TimeApproved = timeApproved
}
);
}
}
}
// Transform shift extra data
if (ShiftExtraList != null)
{
// Implementation to be determined. Not applicable for Turner transformation
}
var numOfRows = await DatabaseHelper.InsertManyAsync <Transform>(turnerList, true);
FileSystemsHelpers.WriteToFile($"Number of transformed records: {numOfRows}");
MessageBox.Show("Data transformation complete!", "Rhumbix Macro", MessageBoxButton.OK,
MessageBoxImage.Information);
}
catch (Exception ex)
{
Logger.Error(ex, "Exception occured during data transformation");
}
}