public ActionResult <DataImportVm> ProcessImportFile([FromBody] DataImportVm importFile, string Id, bool isRevenue = true)
{
if (!ModelState.IsValid)
{
Log.Error("Unable to process import file via ProcessImportFile() due to invalid model state for {0}", ModelState);
return(BadRequest("Invalid model state: " + ModelState));
}
var dataAccessComponent = new ImportFileDataProcessing();
if (importFile.IsRevenueData)
{
processedVm = dataAccessComponent.SaveRevenue(importFile, _dbCtx, Id);
// UI to interpret updated Vm attributes.
if (processedVm.RecordsSaved == 0)
{
// Possible invalid file path or bad network connectivity.
if (!string.IsNullOrEmpty(processedVm.MiscMessage))
{
Log.Warning("Aborted in ImportFileController.ProcessImportFile() due to {0} in {1}.", processedVm.MiscMessage.Trim(),
processedVm.ImportFilePath.Trim());
return(BadRequest(new { exceptionMessage = processedVm.MiscMessage.Trim(), isRevenueData = true }));
}
else if (importFile.ExceptionTickers != string.Empty)
{
return(BadRequest(new { exceptionTickers = processedVm.ExceptionTickers }));
}
else
{
return(BadRequest(new { exceptionMessage = "Unable to persist revenue" }));
}
}
processedVm.AmountSaved = decimal.Parse(string.Format("{0:0.00}", processedVm.AmountSaved));
Log.Information("Revenue import successful for {0} record(s), totaling ${1}.", processedVm.RecordsSaved, processedVm.AmountSaved);
return(CreatedAtAction("ProcessImportFile", new { count = processedVm.RecordsSaved, amount = processedVm.AmountSaved }, processedVm));
}
else // aka 'Positions' (Asset) processing.
{
processedVm = dataAccessComponent.SaveAssets(importFile, _dbCtx, Id);
if (processedVm.RecordsSaved == 0)
{
Log.Error("ImportFileController.ProcessImportFile() : {0}", processedVm.MiscMessage.Trim());
return(BadRequest(new { exceptionMessage = "Error saving new Position(s).", isRevenueData = false }));
}
// Returned customized anonymous object to be data-import.service catchError().
if (processedVm.ExceptionTickers != string.Empty)
{
return(BadRequest(new { exceptionTickers = processedVm.ExceptionTickers, isRevenueData = false }));
}
Log.Information("Position import successful for {0} record(s), representing {1}.", processedVm.RecordsSaved, processedVm.MiscMessage);
return(CreatedAtAction("ProcessImportFile", new { count = processedVm.RecordsSaved, savedTickers = processedVm.MiscMessage }, processedVm));
}
}
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);
}
}
public IEnumerable <AssetCreationVm> ParsePortfolioSpreadsheetForAssetRecords(string filePath, ImportFileDataProcessing dataAccessComponent, string id)
{
List <AssetCreationVm> assetsToCreateList = new List <AssetCreationVm>();
var profileDataAccessComponent = new ProfileDataProcessing(_ctx);
var existingProfileId = string.Empty;
var existingAssetClassId = "6215631D-5788-4718-A1D0-A2FC00A5B1A7";;
var newAssetId = Guid.NewGuid().ToString();
List <Position> positionsToBeSaved = null;
try
{
string lastTickerProcessed = string.Empty;
var importFile = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(importFile))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
int totalRows = workSheet.Dimension.End.Row;
int totalColumns = workSheet.Dimension.End.Column;
AssetCreationVm newAsset = new AssetCreationVm();
// Iterate XLS/CSV, ignoring column headings (row 1).
for (var rowNum = 2; rowNum <= totalRows; rowNum++)
{
// Validate XLS
var headerRow = workSheet.Cells[1, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
if (!ValidateFileType(filePath))
{
return(null);
}
// Args: Cells[fromRow, fromCol, toRow, toCol]
var row = workSheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
var enumerableCells = row as string[] ?? row.ToArray();
var positionDataAccessComponent = new PositionDataProcessing(_ctx);
// Existing Position-Account implies Profile existence.
IQueryable <Asset> positionAccount = positionDataAccessComponent.GetPositionAssetByTickerAndAccount(enumerableCells.ElementAt(1).Trim(),
enumerableCells.ElementAt(0).Trim(),
id);
if (!positionAccount.Any()) // No Position-Account found.
{
IQueryable <Profile> profilePersisted = null;
// Are we processing a different ticker symbol?
if (lastTickerProcessed.Trim().ToUpper() != enumerableCells.ElementAt(1).Trim().ToUpper())
{
lastTickerProcessed = enumerableCells.ElementAt(1).Trim().ToUpper();
// Do we first have a standard web-derived Profile (via 3rd party) record in our database?
profilePersisted = profileDataAccessComponent.FetchDbProfile(enumerableCells.ElementAt(1).Trim(), "");
if (profilePersisted == null)
{
// Do we secondly have a Customized Profile (via 3rd party) record in our database?
// Check for lost _investorSvc reference.
if (_investorSvc == null)
{
_investorSvc = new InvestorSvc(_ctx);
}
;
Investor currentInvestor = _investorSvc.GetById(id);
profilePersisted = profileDataAccessComponent.FetchDbProfile(enumerableCells.ElementAt(1).Trim(), currentInvestor.LoginName);
}
if (profilePersisted != null)
{
// Bypassing Profile creation for new Position.
existingProfileId = profilePersisted.First().ProfileId;
if (assetIdForPosition == string.Empty)
{
assetIdForPosition = newAssetId;
}
// Are we processing our first XLSX Position record?
if (positionsToBeSaved == null)
{
positionsToBeSaved = InitializePositions(new List <Position>(), enumerableCells);
}
else
{
positionsToBeSaved = InitializePositions(positionsToBeSaved, enumerableCells);
}
// Error seeding collection.
if (positionsToBeSaved == null)
{
return(null);
}
assetsToCreateList.Add(new AssetCreationVm
{
AssetId = Guid.NewGuid().ToString(),
AssetClassId = existingAssetClassId,
InvestorId = id,
ProfileId = existingProfileId,
LastUpdate = DateTime.Now,
Positions = positionsToBeSaved
});
}
else
{
// Obtain a new Profile via Tiingo API.
var webProfileData = profileDataAccessComponent.BuildProfile(enumerableCells.ElementAt(1).Trim().ToUpper());
if (webProfileData == null)
{
dataAccessComponent._exceptionTickers = enumerableCells.ElementAt(1).Trim().ToUpper();
return(null); // any necessary logging done via component.
}
else
{
// Dividend freq, months, & payDay all may be edited via 'Asset Profile'
// functionality for any created *customized* Profile only. Any standard
// Profile may NOT be edited this way, as this would impact many investors.
Profile newProfile = new Profile
{
ProfileId = webProfileData.ProfileId,
DividendYield = webProfileData.DividendYield > 0
? webProfileData.DividendYield
: 0,
CreatedBy = null,
DividendRate = webProfileData.DividendRate > 0 ? webProfileData.DividendRate : 0,
ExDividendDate = webProfileData.ExDividendDate ?? new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day),
DividendFreq = webProfileData.DividendFreq ?? "M",
DividendMonths = null,
DividendPayDay = 15,
EarningsPerShare = webProfileData.EarningsPerShare > 0
? webProfileData.EarningsPerShare
: 0,
LastUpdate = DateTime.Now,
PERatio = webProfileData.PERatio > 0
? webProfileData.PERatio
: 0,
TickerDescription = webProfileData.TickerDescription.Trim(),
TickerSymbol = webProfileData.TickerSymbol.ToUpper().Trim(),
UnitPrice = webProfileData.UnitPrice
};
assetIdForPosition = newAssetId;
assetsToCreateList.Add(new AssetCreationVm
{
AssetId = Guid.NewGuid().ToString(),
AssetClassId = existingAssetClassId,
InvestorId = id, //INVESTORID,
ProfileId = newProfile.ProfileId,
LastUpdate = DateTime.Now,
Positions = positionsToBeSaved == null
? InitializePositions(new List <Position>(), enumerableCells)
: InitializePositions(positionsToBeSaved, enumerableCells),
Profile = newProfile
});
foreach (var asset in assetsToCreateList)
{
if (asset.Positions.Count == 0)
{
Log.Error("Error creating new Position(s) for assetsToCreateList in ImportFileProcessing.ParsePortfolioSpreadsheetForAssetRecords().");
return(null);
}
}
;
}
}
}
else
{
// Asset header initialization & Profile check bypassed; processing SAME ticker - DIFFERENT account.
// Adding to existing AssetCreationVm.Positions.
var updatedPositions = InitializePositions(assetsToCreateList.Last().Positions.ToList(), enumerableCells);
assetsToCreateList.Last().Positions.Add(updatedPositions.Last());
}
}
else
{
// Attempted duplicate Position-Account insertion.
lastTickerProcessed = enumerableCells.ElementAt(1).Trim();
}
} // end 'for'
} // end 'using'
}
catch (Exception ex)
{
if (ex.Message.Length > 0)
{
Log.Error("Error found within ImportFileProcessing.ParsePortfolioSpreadsheetForAssetRecords(), due to {0}.", ex.Message);
}
else
{
Log.Error("Error found within ImportFileProcessing.ParsePortfolioSpreadsheetForAssetRecords().");
}
return(null);
}
return(assetsToCreateList);
}
