/// <summary> /// GetNavRow reads the excel sheet and returns ExcelNav. /// It returns null if either fund, investor or commitment is missing /// </summary> /// <param name="row"></param> /// <returns></returns> private ExcelNav GetNavRow(int row) { ExcelNav nav = new ExcelNav(); try { nav.NavAmount = sheetFunctions.GetAmount(row, 3); } catch (Exception) { nav.NavAmount = 0; } nav.NavDate = sheetFunctions.GetDate(row, 2); nav.InvestorNumber = sheetFunctions.GetText(row, 1); nav.FundWkn = sheetFunctions.GetText(row, 0); if (commitment == null || currentInvestorNumber != nav.InvestorNumber || currentFundWkn != nav.FundWkn) { currentInvestorNumber = nav.InvestorNumber; currentFundWkn = nav.FundWkn; commitment = iCommitments.FirstOrDefault(c => c.InvestorNumber.Equals(nav.InvestorNumber) && c.PeFundNumber.Equals(nav.FundWkn)); if (commitment == null) { eventAggregator.GetEvent <ImportEvent>() .Publish($"Ein Commitment für den Investor {currentInvestorNumber} und die Beteiligungsnummer {currentFundWkn} wurde nicht gefunden."); return(null); } } nav.InvestorId = commitment.InvestorId; nav.PeFundId = commitment.PeFundId; nav.InvestorCommitmentId = commitment.InvestorCommitmentId; return(nav); }
private async Task CreateNavListAsync() { await Task.Run(() => { foreach (Worksheet ws in workbook.Sheets) { if (!ws.Name.Equals("NAV")) { continue; } sheetFunctions = new ExcelHelperFunctions(ws); TotalRecords = sheetFunctions.RowCount; // reading starts with second row as first row contains headlines for (int row = 1; row < sheetFunctions.RowCount; row++) { ExcelNav nav = GetNavRow(row); if (nav != null) { navList.Add(nav); } else { } } } }); }
private async Task ImportNavListAsync() { for (int counter = 0; counter < navList.Count; counter++) { if (counter % 100 == 0) { CurrentRecordNumber = $"Es wurden {counter:n0} von {navList.Count:n0} Sätzen verarbeitet."; } ExcelNav nav = navList.ElementAt(counter); InvestorPcap pcap = investorAccess.GetPcapForInvestorByCommitmentAndDate(nav.InvestorCommitmentId, nav.NavDate); if (pcap == null) { // insert new pcap InvestorPcap newPcap = new InvestorPcap() { AsOfDate = nav.NavDate, FinalPcapAmount = nav.NavAmount, InvestorCommitmentId = nav.InvestorCommitmentId, DateOfFinalPcap = nav.NavDate, EstimatedPcapAmount = nav.NavAmount }; try { investorAccess.InsertOrUpdateInvestorPcap(newPcap); } catch (Exception ex) { ErrorMessages.Add(ex.Message); } } else { if (pcap.FinalPcapAmount == nav.NavAmount) { continue; } pcap.FinalPcapAmount = nav.NavAmount; pcap.DateOfFinalPcap = nav.NavDate; pcap.EstimatedPcapAmount = nav.NavAmount; try { investorAccess.InsertOrUpdateInvestorPcap(pcap); } catch (Exception ex) { ErrorMessages.Add(ex.Message); } } } CurrentRecordNumber = $"Es wurden alle Datensätze verarbeitet."; }