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