/// <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 void GetCommitmentRow(int row) { ImportCommitment commitment = new ImportCommitment(); commitment.PeFundNumber = sheetFunctions.GetText(row, 0); commitment.PeFundCurrency = sheetFunctions.GetText(row, 1); commitment.PeFundCurrencyId = FindCurrencyId(commitment.PeFundCurrency); commitment.PeFundName = sheetFunctions.GetText(row, 2); commitment.InvestorNumber = sheetFunctions.GetText(row, 3); commitment.InvestorCurrency = sheetFunctions.GetText(row, 4); commitment.InvestorCurrencyId = FindCurrencyId(commitment.InvestorCurrency); commitment.AsOfDate = sheetFunctions.GetDate(row, 5); try { commitment.Commitment = sheetFunctions.GetAmount(row, 6); } catch (Exception) { commitment.Commitment = 0; } if (!int.TryParse(commitment.InvestorNumber.Substring(0, 3), out int test)) { // lines with other content than 3 numerical digits will ignored // these lines are for institutional investors return; } ImportCommitment found = ICommitments. Where(c => c.PeFundNumber == commitment.PeFundNumber && c.InvestorNumber == commitment.InvestorNumber).FirstOrDefault(); if (found != null) { found.FoundInPsPlus = true; if (found.Commitment == commitment.Commitment) { return; } found.ErrorInformation = $"Abweichende Kapitalzusage zwischen PsPlus ({commitment.Commitment:N0}) und HQT Private Equity ({found.Commitment:N0}) "; found.HqpeCommitment = found.Commitment; CanShowDifferentCommitments = true; return; } // there is a commitment in PS-Plus with no matching commitment in HQPE // add a new importCommitment to the importCommitmentCollection // add a InvestorCommitment to the database if PeFund and Investor are found ImportCommitment import = new ImportCommitment() { AsOfDate = commitment.AsOfDate, PeFundName = commitment.PeFundName, PeFundNumber = commitment.PeFundNumber, InvestorNumber = commitment.InvestorNumber, InvestorCurrency = commitment.InvestorCurrency, InvestorCurrencyId = commitment.InvestorCurrencyId, PeFundCurrency = commitment.PeFundCurrency, PeFundCurrencyId = commitment.PeFundCurrencyId, Commitment = commitment.Commitment, FoundInPsPlus = true, ErrorInformation = "Es wurde kein Commitment in HQT Private Equity gefunden;" }; // try to find Investor using InvestorNumber; set InvestorId if found // try to find PEFund using Beteiligungsnumber; set PeFundId if found Investor investor = investorAccess.GetInvestorByHqTrustNumber(import.InvestorNumber); if (investor == null) { import.ErrorInformation += " kein Investor gefunden;"; import.InvestorId = 0; CanShowMissingInvestors = true; CanAddMissingItems = true; } if (investor != null) { import.InvestorId = investor.Id; } PeFund peFund = PefundAccess.GetPeFundByBeteiligungsnummer(import.PeFundNumber); if (peFund == null) { import.ErrorInformation += " kein Fund gefunden;"; import.PeFundId = 0; CanShowMissingFunds = true; CanAddMissingItems = true; } if (peFund != null) { import.PeFundId = peFund.Id; } // if investor and fund are found insert investorcommitment if (import.PeFundId > 0 && import.InvestorId > 0) { // Find Commitment using FundId and InvestorId // if found set InvestorCommitmentId and add ImportCommitment // if not add InvestorCommitment InvestorCommitment newCommitment = new InvestorCommitment() { CommitmentAmount = import.Commitment, InvestorId = import.InvestorId, PeFundId = import.PeFundId }; try { newCommitment = investorAccess.UpdateInvestorCommitments(newCommitment); import.ErrorInformation = "Das Commitment wurde in die Datenbank eingefügt."; import.InvestorCommitmentId = newCommitment.Id; import.CommitmentsAdded = true; CanShowAddedCommitments = true; } catch (Exception ex) { NotificationRequest.Raise(new Notification() { Title = ApplicationNames.NotificationTitle, Content = ex.Message }); CloseThisTab(); } } ICommitments.Add(import); return; }