private void FinalizeAndUpload(DataTable dt, int reviewId, RowsTransferredEventHandler rowsTransferred)
{
dt.SetColumnWithValue(ReviewIdColumnName, reviewId);
dt.MakeDateColumnsFitSqlServerBounds();
dt.IdealizeStringColumns();
dt.UploadIntoSqlServer(CreateSqlConnection, new UploadIntoSqlServerSettings {
RowsTransferredEventHandler = rowsTransferred
});
}
private void LoadActiveCardholders(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, st, onRowAddError, rowsTransferred, ConfigOptions.Value.ActiveCardholderImportConfig, CreateActiveCardholdersDataTable);
private void LoadCreateWorkingCapitalFundItems(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, st, onRowAddError, rowsTransferred, ConfigOptions.Value.WorkingCapitalFundReportConfig, CreateWorkingCapitalFundItemsDataTable);
private void LoadPegasysOpenItemsCreditCards(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, st, onRowAddError, rowsTransferred, ConfigOptions.Value.PegasysOpenItemsCreditCardsSheetsConfig, CreatePegasysOpenItemsCreditCardsDataTable);
private void LoadCreditCardAliases(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, st, onRowAddError, rowsTransferred, ConfigOptions.Value.CreditCardAliasConfig, CreateCreditCardAliasDataTable);
private void Load192s(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, st, onRowAddError, rowsTransferred, ConfigOptions.Value.Upload192ImportConfig, Create192DataTable);
private void LoadEasi(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred)
{
DataTable dt;
var ds = new DataSet();
var settings = new LoadTablesFromSpreadsheetSettings()
{
CreateDataTable = CreateEasiDataTable,
LoadAllSheetsDefaultSettings = new LoadRowsFromSpreadsheetSettings
{
TypeConverter = U.SpreadsheetHelpers.ExcelTypeConverter,
RowAddErrorHandler = onRowAddError
}
};
ds.LoadSheetsFromExcel(st, settings);
dt = MergeIntoSingleTable(ds, CreateEasiDataTable);
FinalizeAndUpload(dt, reviewId, rowsTransferred);
}
private async Task Load442sAsync(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred)
{
var dt = CreatePegasysOpenObligationsDataTable();
await dt.LoadRowsFromDelineatedTextAsync(st, new LoadRowsFromDelineatedTextSettings
{
SkipRawRows = 2,
RowAddErrorHandler = onRowAddError
});
dt.SetColumnWithValue(ReviewIdColumnName, reviewId);
dt.UploadIntoSqlServer(CreateSqlConnection, new UploadIntoSqlServerSettings {
RowsTransferredEventHandler = rowsTransferred
});
}
private void LoadExcelDataTable(int reviewId, Stream st, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred, Config.ImportConfig importConfig, Func <DataTable> dataTableCreator)
{
string sheetNamesCsv = importConfig.SheetsCsv;
int skipRawRows = importConfig.SkipRawRows;
DataTable dt;
var ds = new DataSet();
var settings = new LoadTablesFromSpreadsheetSettings()
{
SheetSettings = new List <LoadRowsFromSpreadsheetSettings>(),
CreateDataTable = dataTableCreator,
};
var sheetNames = R.CSV.ParseLine(sheetNamesCsv);
foreach (var sheetName in sheetNames)
{
settings.SheetSettings.Add(
new LoadRowsFromSpreadsheetSettings
{
SheetName = sheetName,
TypeConverter = U.SpreadsheetHelpers.ExcelTypeConverter,
UseSheetNameForTableName = true,
RowAddErrorHandler = onRowAddError,
SkipRawRows = skipRawRows,
});
}
ds.LoadSheetsFromExcel(st, settings);
dt = MergeIntoSingleTable(ds, dataTableCreator);
FinalizeAndUpload(dt, reviewId, rowsTransferred);
}
//TODO: Email on exception or let user know what happened
public async Task UploadFiles(UploadFilesModel model)
{
var reviewId = model.ReviewId;
var folder = await SpecialFolderProvider.GetReviewFolderAsync(model.ReviewId);
string importer = null;
int rowErrorCount = 0;
Action <Exception, int> onRowAddError = (ex, rowNum) =>
{
LogError(ex, "UploadFiles: OnRowAddError with {rowNum} in {importer} with {reviewId}", rowNum, importer, reviewId);
++rowErrorCount;
};
RowsTransferredEventHandler rowsTransferred = (sender, e) =>
{
LogInformation("UploadFiles: Loading progress. {rowCount} in {importer} with {reviewId}", e.RowsTransferred, importer, reviewId);
};
try
{
var allFiles =
model.PegasysFilePathsList
.Union(model.RetaFileList)
.Union(model.EasiFileList)
.Union(model.One92FileList)
.Union(model.CreditCardAliasCrosswalkFiles)
.Union(model.PegasysOpenItemsCreditCards)
.Union(model.WorkingCapitalFundReportFiles)
.Union(model.ActiveCardholderFiles)
.ToList();
LogInformation("Will ultimately process the following files:\n{filePaths}", allFiles.Format("\n", "\t{0}"));
importer = nameof(Load442sAsync);
foreach (var fn in model.PegasysFilePathsList)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
await Load442sAsync(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(LoadReta);
foreach (var fn in model.RetaFileList)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
LoadReta(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(LoadEasi);
foreach (var fn in model.EasiFileList)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
LoadEasi(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(Load192s);
foreach (var fn in model.One92FileList)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
Load192s(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(LoadCreditCardAliases);
foreach (var fn in model.CreditCardAliasCrosswalkFiles)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
LoadCreditCardAliases(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(LoadPegasysOpenItemsCreditCards);
foreach (var fn in model.PegasysOpenItemsCreditCards)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
LoadPegasysOpenItemsCreditCards(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(LoadCreateWorkingCapitalFundItems);
foreach (var fn in model.WorkingCapitalFundReportFiles)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
LoadCreateWorkingCapitalFundItems(reviewId, st, onRowAddError, rowsTransferred);
}
}
importer = nameof(LoadActiveCardholders);
foreach (var fn in model.ActiveCardholderFiles)
{
using (var st = await folder.OpenFileReadStreamAsync(fn))
{
LoadActiveCardholders(reviewId, st, onRowAddError, rowsTransferred);
}
}
}
catch (Exception ex)
{
LogError(ex, "Problem with UploadFiles in {reviewId}", reviewId);
throw;
}
finally
{
var level = rowErrorCount == 0 ? LogLevel.Information : LogLevel.Warning;
Logger.Log(level, "Importing of {reviewId} yielded {rowErrorCount}. Note, this does not indicate either overall success or failure.", reviewId, rowErrorCount);
}
}
private void LoadActiveCardholders(int reviewId, string uploadPath, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, uploadPath, onRowAddError, rowsTransferred, Properties.Settings.Default.ActiveCardholderSheetsToImport, Properties.Settings.Default.ActiveCardholderSkipRawRows, CreateActiveCardholdersDataTable);
private void LoadPegasysOpenItemsCreditCards(int reviewId, string uploadPath, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred) => LoadExcelDataTable(reviewId, uploadPath, onRowAddError, rowsTransferred, Properties.Settings.Default.PegasysOpenItemsCreditCardsSheetsToImport, Properties.Settings.Default.PegasysOpenItemsCreditCardsSkipRawRows, CreatePegasysOpenItemsCreditCardsDataTable);
private void Load442s(int reviewId, string uploadPath, Action <Exception, int> onRowAddError, RowsTransferredEventHandler rowsTransferred)
{
var dt = CreatePegasysOpenObligationsDataTable();
using (var st = File.OpenRead(uploadPath))
{
dt.LoadRowsFromDelineatedText(st, new LoadRowsFromDelineatedTextSettings
{
SkipRawRows = 2,
RowAddErrorHandler = onRowAddError
});
dt.SetColumnWithValue(ReviewIdColumnName, reviewId);
dt.UploadIntoSqlServer(CreateSqlConnection, new UploadIntoSqlServerSettings {
RowsTransferredEventHandler = rowsTransferred
});
}
}
//TODO: Email on exception or let user know what happened
public void UploadFiles(UploadFilesModel files)
{
var reviewId = files.ReviewId;
string importer = null;
int rowErrorCount = 0;
Action <Exception, int> onRowAddError = (ex, rowNum) =>
{
Log.Error(ex, "UploadFiles: OnRowAddError with {rowNum} in {importer} with {reviewId}", rowNum, importer, reviewId);
++rowErrorCount;
};
RowsTransferredEventHandler rowsTransferred = (sender, e) =>
{
Log.Information("UploadFiles: Loading progress. {rowCount} in {importer} with {reviewId}", e.RowsTransferred, importer, reviewId);
};
try
{
importer = nameof(Load442s);
foreach (var fn in files.PegasysFilePathsList)
{
Load442s(reviewId, fn, onRowAddError, rowsTransferred);
}
importer = nameof(LoadReta);
foreach (var fn in files.RetaFileList)
{
LoadReta(reviewId, fn, onRowAddError, rowsTransferred);
}
importer = nameof(LoadEasi);
foreach (var fn in files.EasiFileList)
{
LoadEasi(reviewId, fn, onRowAddError, rowsTransferred);
}
importer = nameof(Load192s);
foreach (var fn in files.One92FileList)
{
Load192s(reviewId, fn, onRowAddError, rowsTransferred);
}
importer = nameof(LoadCreditCardAliases);
foreach (var fn in files.CreditCardAliasCrosswalkFiles)
{
LoadCreditCardAliases(reviewId, fn, onRowAddError, rowsTransferred);
}
importer = nameof(LoadPegasysOpenItemsCreditCards);
foreach (var fn in files.PegasysOpenItemsCreditCards)
{
LoadPegasysOpenItemsCreditCards(reviewId, fn, onRowAddError, rowsTransferred);
}
importer = nameof(LoadActiveCardholders);
foreach (var fn in files.ActiveCardholderFiles)
{
LoadActiveCardholders(reviewId, fn, onRowAddError, rowsTransferred);
}
}
catch (Exception ex)
{
Log.Error(ex, "Problem with UploadFiles in {reviewId}", reviewId);
throw;
}
finally
{
var level = rowErrorCount == 0 ? Serilog.Events.LogEventLevel.Information : Serilog.Events.LogEventLevel.Warning;
Log.Write(level, "Importing of {reviewId} yielded {rowErrorCount}. Note, this does not indicate either overall success or failure.", reviewId, rowErrorCount);
}
}
