public async Task <IEnumerable <Income> > Upload(StreamReader stream)
{
var incomes = new List <Data.Entities.Income>();
using (stream)
{
var workbook = new XSSFWorkbook(stream.BaseStream);
var sheet = workbook.GetSheet("Incomes");
for (var rownum = 0; rownum <= sheet.LastRowNum; rownum++)
{
if (sheet.GetRow(rownum) == null)
{
continue;
}
var row = sheet.GetRow(rownum);
var income = new Data.Entities.Income
{
Date = row.GetCell(0).DateCellValue,
ToId = (int)row.GetCell(1).NumericCellValue,
CategoryId = (int)row.GetCell(2).NumericCellValue,
Amount = (int)row.GetCell(3).NumericCellValue,
Comment = row.GetCell(4)?.StringCellValue,
UserId = _userId
};
incomes.Add(income);
}
}
using (var context = _factory.CreateDbContext())
{
await context.Incomes.AddRangeAsync(incomes);
await context.SaveChangesAsync();
}
var mappedExpenses = _mapper.MapAll <Income>(incomes);
return(mappedExpenses);
}
public IEnumerable <Data.Entities.Income> ParseRevenueSpreadsheetForIncomeRecords(string filePath, ImportFileDataProcessing dataAccessComponent, string loggedInvestorId)
{
List <Data.Entities.Income> newIncomeListing = new List <Data.Entities.Income>();
IncomeDataProcessing incomeDataAccessComponent = new IncomeDataProcessing(_ctx);
DataAccess.Asset.AssetData assetDataAccessComponent = new DataAccess.Asset.AssetData(_ctx);
IQueryable <string> fetchedPositionId;
try
{
FileInfo importFile = new FileInfo(filePath);
using (var package = new ExcelPackage(importFile))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
int totalRows = workSheet.Dimension.End.Row;
int totalColumns = workSheet.Dimension.End.Column;
_xlsTickerSymbolsOmitted = string.Empty;
for (var rowNum = 2; rowNum <= totalRows; rowNum++)
{
// Validate XLS
IEnumerable <string> headerRow = workSheet.Cells[1, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
if (!ValidateFileAttributes(true, headerRow) || !ValidateFileType(filePath))
{
return(null);
}
IEnumerable <string> row = workSheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
string[] enumerableCells = row as string[] ?? row.ToArray();
// 'totalRows' may yield inaccurate results; we'll test for last record, e.g., 'enumerableCells[0] ('Recvd Date').
if (!enumerableCells.Any() || enumerableCells[0] == "")
{
if (_xlsTickerSymbolsOmitted.Any())
{
dataAccessComponent._exceptionTickers = _xlsTickerSymbolsOmitted;
return(null);
}
return(newIncomeListing);
}
string xlsTicker = enumerableCells.ElementAt(3).Trim();
string xlsAccount = CommonSvc.ParseAccountTypeFromDescription(enumerableCells.ElementAt(1).Trim());
fetchedPositionId = assetDataAccessComponent.FetchPositionId(loggedInvestorId, xlsTicker, xlsAccount).AsQueryable();
// Checking PositionId rather than asset is sufficient.
// Validate either a bad ticker symbol, or that no account was affiliated with the position/asset in question.
if (!fetchedPositionId.Any())
{
if (_xlsTickerSymbolsOmitted == string.Empty)
{
_xlsTickerSymbolsOmitted += xlsTicker;
}
else
{
_xlsTickerSymbolsOmitted += ", " + xlsTicker;
}
continue;
}
duplicateResults = incomeDataAccessComponent.FindIncomeDuplicates(fetchedPositionId.First().ToString(), enumerableCells.ElementAt(0), enumerableCells.ElementAt(4));
if (duplicateResults.Any())
{
if (_xlsTickerSymbolsOmitted == string.Empty)
{
_xlsTickerSymbolsOmitted += xlsTicker;
}
else
{
_xlsTickerSymbolsOmitted += ", " + xlsTicker;
}
continue;
}
if (_xlsTickerSymbolsOmitted != string.Empty)
{
_viewModel.ExceptionTickers = _xlsTickerSymbolsOmitted;
}
Data.Entities.Income newIncomeRecord = new Data.Entities.Income
{
IncomeId = Guid.NewGuid().ToString(),
PositionId = fetchedPositionId.First().ToString(),
DateRecvd = DateTime.Parse(enumerableCells.ElementAt(0)),
AmountRecvd = decimal.Parse(enumerableCells.ElementAt(4)),
LastUpdate = DateTime.Now
};
newIncomeListing.Add(newIncomeRecord);
_totalXlsIncomeRecordsToSave += 1;
newIncomeRecord = null;
} // end 'for'
if (_xlsTickerSymbolsOmitted.Length > 0)
{
_viewModel.ExceptionTickers = _xlsTickerSymbolsOmitted;
Log.Warning("Invalid XLS/XLSX position(s) found, revenue import aborted for {0}.", _xlsTickerSymbolsOmitted);
}
return(newIncomeListing);
} // end 'using'
}
catch (Exception ex)
{
if (ex.Message.Length > 0)
{
Log.Error("Invalid xlsx format, or bad file path found within ImportFileProcessing.ParseRevenueSpreadsheetForIncomeRecords(), due to {0}.", ex.Message);
}
else
{
Log.Error("Error found within ImportFileProcessing.ParseRevenueSpreadsheetForIncomeRecords().");
}
return(null);
}
}
