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