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