private async Task <ObservableCollection <ImportCommitment> > CreateImportCommitmentsAsync() { ObservableCollection <ImportCommitment> commitments = new ObservableCollection <ImportCommitment>(); var hqtCommitments = await investorAccess.GetHqtCommitmentsAsync(); foreach (InvestorCommitment commitment in hqtCommitments) { ImportCommitment import = new ImportCommitment() { AsOfDate = DateTime.MinValue, Commitment = commitment.CommitmentAmount, ErrorInformation = string.Empty, FoundInPsPlus = false, InvestorCommitmentId = commitment.Id, InvestorCurrency = commitment.PeFund.Currency.CurrencyShortName, InvestorCurrencyId = (int)commitment.PeFund.CurrencyId, InvestorId = (int)commitment.InvestorId, InvestorNumber = commitment.Investor.InvestorHqTrustAccount, PeFundCurrency = commitment.PeFund.Currency.CurrencyShortName, PeFundCurrencyId = (int)commitment.PeFund.CurrencyId, PeFundName = commitment.PeFund.FundName, PeFundId = commitment.PeFundId, PeFundNumber = commitment.PeFund.FundHqTrustNumber }; commitments.Add(import); } return(commitments); }
/// <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 bool MissingFundsFilter(object obj) { ImportCommitment c = obj as ImportCommitment; if (c.PeFundId == 0) { return(true); } return(false); }
private void OnShowDifferentCommitments() { ImportCommitments.Filter = null; ImportCommitments.Filter = item => { ImportCommitment c = item as ImportCommitment; return(c.HqpeCommitment != 0); }; // Clear GroupDescriptors ImportCommitments.GroupDescriptions.Clear(); }
private void OnShowAddedCommitments() { ImportCommitments.Filter = null; ImportCommitments.Filter = item => { ImportCommitment c = item as ImportCommitment; return(c.CommitmentsAdded == true); }; // Clear GroupDescriptors ImportCommitments.GroupDescriptions.Clear(); }
private void OnShowMissingInvestors() { ImportCommitments.Filter = null; ImportCommitments.Filter = item => { ImportCommitment c = item as ImportCommitment; return(c.InvestorId == 0); }; // Clear GroupDescriptors and add group by investor ImportCommitments.GroupDescriptions.Clear(); ImportCommitments.GroupDescriptions.Add(new PropertyGroupDescription("InvestorNumber")); }
public void InsertImportCommitment(ImportCommitment import) { using (HqTrustData dbContext = new HqTrustData()) { dbContext.ImportCommitments.Add(import); try { dbContext.SaveChanges(); } catch (Exception ex) { throw new Exception($"Fehler beim Einfügen eines PS Plus Commitments {ex.Message}"); } } }
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; }
private void InsertCashFlowsAsync() { //the first item of cashFlows is read //select all cashFlows with the same porfolionumber and investornumber //remove all selected records from cashFlows // try to find commitment in commitments // if found: // read all InvestorCashFlows from the Database int counter = 0; int previousCounter = 0; while (cashFlows.Count > 0) { ImportCashFlow cf = cashFlows.ElementAt(0); List <ImportCashFlow> specificCashFlows = cashFlows.Where(c => c.InvestorNumber == cf.InvestorNumber && c.PeFundNumber == cf.PeFundNumber).ToList(); DateTime lastCashFlowDate = specificCashFlows.Max(c => c.CashFlowDate); DateTime firstCashFlowDate = specificCashFlows.Min(c => c.CashFlowDate); for (int i = 0; i < specificCashFlows.Count; i++) { cashFlows.RemoveAt(0); } counter += specificCashFlows.Count; if (counter - previousCounter > 100) { CurrentNumberOfRecords = $"Es wurden {counter:n0} Datensätze verarbeitet."; previousCounter = counter; } eventAggregator.GetEvent <ImportInformationEvent>().Publish(new ImportInformation() { ImportName = "Cashflows", Information = $"Der Import der Cashflows ist gestartet" }); List <InvestorCashFlow> dbCashFlows = new List <InvestorCashFlow>(); ImportCommitment commitment = commitments.FirstOrDefault(c => c.InvestorNumber == cf.InvestorNumber && c.PeFundNumber == cf.PeFundNumber); if (commitment != null) { if (commitment.InvestorCommitmentId == 0) { continue; // InvestorCommitmentId =0 if commitment is not in the database } // => no CashFlows are inserted dbCashFlows = investorAccess.GetCashFlowsForCommitment(commitment.InvestorCommitmentId); } else { continue; // if commitment is not found leave database unchanged } foreach (ImportCashFlow import in specificCashFlows) { if (import.PsPlusId == 0) { import.PsPlusId = 1; // there is no PSPlusId for transaction before client starts with HQTrust; default Id =1 } InvestorCashFlow foundCf = dbCashFlows.FirstOrDefault(c => c.PsPlusId == import.PsPlusId); //if a cashflow with a corresponding PsPlusId is found ==> read next Cashflow if (foundCf != null) { dbCashFlows.Remove(foundCf); continue; } // if not found try to find a cashFlow with the same Date (+- 3 days) InvestorCashFlow selectedCashFlow = null; foreach (InvestorCashFlow icf in dbCashFlows) { TimeSpan span = icf.EffectiveDate.Subtract(import.CashFlowDate); if (Math.Abs(span.Days) > 3) { continue; } if (icf.CashFlowAmount == import.AmountPeFundCurrency) { icf.PsPlusId = import.PsPlusId; selectedCashFlow = icf; break; } } if (selectedCashFlow != null) { //update CashFlow // remove CashFlow from dbCashFlows selectedCashFlow.PsPlusId = import.PsPlusId; selectedCashFlow.CapitalGain = import.CapitalGain; selectedCashFlow.CashFlowAmount = import.AmountPeFundCurrency; selectedCashFlow.CashFlowAmountInInvestorCurrency = import.AmountInvestorCurrency; selectedCashFlow.Dividends = import.Dividends; selectedCashFlow.EffectiveDate = import.CashFlowDate; selectedCashFlow.PartnershipExpenses = import.PartnershipExpenses; selectedCashFlow.RecallableAmount = import.RecallableAmount; selectedCashFlow.ReturnOfCapital = import.ReturnOfCapital; selectedCashFlow.WithholdingTax = import.WithholdingTax; try { investorAccess.UpdateInvestorCashFlow(selectedCashFlow); } catch (Exception ex) { eventAggregator.GetEvent <ImportEvent>().Publish($"Fehler beim Ändern eines CashFlows: {ex.Message}"); } } else { //in der Datenbank wurde kein CashFlow gefunden => neuen CashFlow eintragen selectedCashFlow = new InvestorCashFlow(); selectedCashFlow.InvestorCommitmentId = commitment.InvestorCommitmentId; selectedCashFlow.CommitmentAmount = commitment.Commitment; selectedCashFlow.PsPlusId = import.PsPlusId; selectedCashFlow.CapitalGain = import.CapitalGain; selectedCashFlow.CashFlowAmount = import.AmountPeFundCurrency; selectedCashFlow.CashFlowAmountInInvestorCurrency = import.AmountInvestorCurrency; selectedCashFlow.Dividends = import.Dividends; selectedCashFlow.EffectiveDate = import.CashFlowDate; selectedCashFlow.PartnershipExpenses = import.PartnershipExpenses; selectedCashFlow.RecallableAmount = import.RecallableAmount; selectedCashFlow.ReturnOfCapital = import.ReturnOfCapital; selectedCashFlow.WithholdingTax = import.WithholdingTax; if (selectedCashFlow.CashFlowAmount < 0) { selectedCashFlow.CashFlowDescription = "Capital Call"; } else { selectedCashFlow.CashFlowDescription = "Distribution"; } if (selectedCashFlow.CashFlowAmount < 0) { selectedCashFlow.CashFlowType = "Capital Call"; } else { selectedCashFlow.CashFlowType = "Distribution"; } try { investorAccess.UpdateInvestorCashFlow(selectedCashFlow); } catch (Exception ex) { eventAggregator.GetEvent <ImportEvent>().Publish($"Fehler beim Einfügen eines CashFlows: {ex.Message}"); } } } // the remaining InvestorCashFlows in dbCashFlows are either after maxDate or need to be removed foreach (InvestorCashFlow icf in dbCashFlows) { if (icf.EffectiveDate > lastCashFlowDate) { continue; } // remove CashFlow from database } eventAggregator.GetEvent <ImportInformationEvent>().Publish(new ImportInformation() { ImportName = "CashFlows", Information = $"Es sind noch {cashFlows.Count.ToString()} Cashflows zu verarbeiten" }); } CurrentNumberOfRecords = $"Es wurden alle Datensätze verarbeitet."; }