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.";
}
