public InvestorCommitment GetInvestorCommitmentById(int id)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.InvestorCommitmnents.FirstOrDefault(c => c.Id == id));
}
}
public Investor GetInvestorByHqTrustNumber(string hqTrustNumber)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.Investors.FirstOrDefault(i => i.InvestorHqTrustAccount.Equals(hqTrustNumber)));
}
}
public List <InvestorToDo> GetToDosForInvestor(int investorid)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.InvestorToDos.Where(t => t.InvestorId == investorid).ToList());
}
}
public Investor GetInvestorById(int investorId)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.Investors.FirstOrDefault(i => i.Id == investorId));
}
}
public void InsertInvestor(Investor investor)
{
//
// UpdateClientAdvisor has updated investor.ClientAdvisor to a new clientadvisor
//
if (investor.ClientIsOwnAdvisor == true)
{
UpdateClientAdvisor(investor);
}
Investor newInvestor = investor.Copy(investor);
newInvestor.BankAccounts = new List <BankAccount>();
newInvestor.DocumentAndLetters = new List <DocumentAndLetter>();
newInvestor.TaxInformations = new List <TaxInformation>();
newInvestor.EMailAccounts = new List <EMailAccount>();
newInvestor.Currency = new Currency();
using (HqTrustData dbContext = new HqTrustData())
{
dbContext.Investors.Add(newInvestor);
try
{
dbContext.SaveChanges();
investor.Id = newInvestor.Id;
}
catch (Exception ex)
{
throw new Exception($"Fehler beim Einfügen eines neuen Investors: {ex.InnerException.Message}");
}
}
UpdateBankAccountsForInvestor(investor);
UpdateDocumentAndLettersForInvestor(investor);
UpdateTaxInformationsForInvestor(investor);
UpdateEMailAccountsForInvestor(investor);
}
public ImportCommitment FindImportCommitment(string peNumber, string investorNumber)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.ImportCommitments.FirstOrDefault(i => i.PeFundNumber.Equals(peNumber) && i.InvestorNumber.Equals(investorNumber)));
}
}
public void DeleteInvestorCommitment(InvestorCommitment commitment)
{
using (HqTrustData dbContext = new HqTrustData())
{
InvestorCommitment old = dbContext.InvestorCommitmnents.
FirstOrDefault(c => c.Id == commitment.Id);
if (old == null)
{
throw new Exception($"Das Commitment mit der Id {commitment.Id} für den Investor {commitment.InvestorId} wurde nicht in der Datenbank gefunden");
}
dbContext.InvestorCommitmnents.Remove(old);
foreach (InvestorCommitmentDetail detail in commitment.InvestorCommitmentDetails)
{
InvestorCommitmentDetail icDetail = dbContext.InvestorCommitmentDetails.FirstOrDefault(d => d.Id == detail.Id);
if (icDetail == null)
{
throw new Exception($"Das CommitmentDetail mit der Id {icDetail.Id} für das Commitment {old.Id} wurde nicht in der Datenbank gefunden");
}
dbContext.InvestorCommitmentDetails.Remove(icDetail);
}
try
{
dbContext.SaveChanges();
}
catch (Exception ex)
{
throw new Exception("Das Commitment konnte in der Datenbank nicht gelöscht werden" + Environment.NewLine + ex.InnerException.Message);
}
}
}
public static List <PeFund> GetFundListForBeteiligungsnumer(string beteiligung)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.PeFunds.Where(p => p.FundHqTrustNumber.Equals(beteiligung)).ToList());
}
}
public static async Task <IEnumerable <ClientAdvisor> > GetAllClientAdvisorsAsync()
{
using (HqTrustData dbContext = new HqTrustData())
{
List <ClientAdvisor> allClientAdvisors = await dbContext.ClientAdvisors.
ToListAsync();
foreach (ClientAdvisor item in allClientAdvisors)
{
// set DisplayName
StringBuilder builder = new StringBuilder();
if (!string.IsNullOrEmpty(item.AdvisorName.Title))
{
builder.Append(item.AdvisorName.Title + " ");
}
if (!string.IsNullOrEmpty(item.AdvisorName.FirstName))
{
builder.Append(item.AdvisorName.FirstName + " ");
}
if (!string.IsNullOrEmpty(item.AdvisorName.LastName))
{
builder.Append(item.AdvisorName.LastName);
}
item.DisplayName = builder.ToString();
}
return(allClientAdvisors);
}
}
public static List <InvestorCommitment> GetCommitmentsForPeFund(int fundId)
{
List <InvestorCommitment> returnList = new List <InvestorCommitment>();
using (HqTrustData dbContext = new HqTrustData())
{
returnList = dbContext.InvestorCommitmnents.
Include("Investor").
Include("Investor.ClientAdvisor").
Include("InvestorCashFlows").
Include("InvestorPcaps").
Include("Investor.EMailAccounts").
Include("PeFund").
Include("PeFund.Currency").
Include("BankAccount").
Include("BankAccount.BankContact").
Where(c => c.PeFundId == fundId).
ToList();
}
//Add additional Information to properties
foreach (InvestorCommitment commitment in returnList)
{
FillFullName(commitment.Investor);
if (commitment.Investor.ClientAdvisorId > 0)
{
FillDisplayName(commitment.Investor.ClientAdvisor);
}
}
return(returnList);
}
/// <summary>
/// takes each element within a List of InvestorPcap and inserts or updates the database
/// </summary>
/// <param name="pcaps">List<InvestorPcap></InvestorPcap> </param>
public static void UpdateOrInsertPcaps(List <InvestorPcap> pcaps)
{
if (pcaps.Count() == 0)
{
return;
}
using (HqTrustData dbContext = new HqTrustData())
{
foreach (InvestorPcap pcap in pcaps)
{
if (pcap.Id == 0)
{
dbContext.InvestorPcaps.Add(pcap);
}
else
{
InvestorPcap existingPcap = dbContext.InvestorPcaps.FirstOrDefault(p => p.Id == pcap.Id);
if (existingPcap == null)
{
throw new Exception($"Das PCAP mit der Id {pcap.Id} wurde nicht gefunden");
}
dbContext.Entry(existingPcap).CurrentValues.SetValues(pcap);
}
}
try
{
dbContext.SaveChanges();
}
catch (Exception ex)
{
throw new Exception($"Fehler beim Ändern der Tabelle InvestorPcaps. {ex.InnerException.Message}");
}
}
}
public int DeleteInvestorCashFlows(List <InvestorCashFlow> cashFlows)
{
int counter = 0;
using (HqTrustData dbContext = new HqTrustData())
{
foreach (InvestorCashFlow cf in cashFlows)
{
InvestorCashFlow item = dbContext.InvestorCashFlows.FirstOrDefault(c => c.Id == cf.Id);
if (item != null)
{
dbContext.InvestorCashFlows.Remove(item);
}
counter++;
}
try
{
dbContext.SaveChanges();
return(counter);
}
catch (Exception ex)
{
throw new Exception($"Fehler beim Löschen von Cashflows. {ex.InnerException.Message}");
}
}
}
public static async Task <IEnumerable <InvestorCommitment> > GetCommitmentsForPeFundAsync(int fundId)
{
using (HqTrustData dbContext = new HqTrustData())
{
commitmentsForAFund = await dbContext.InvestorCommitmnents.
Include("Investor").
Include("Investor.Advisor").
Include("Investor.ClientAdvisor").
Include("Investor.TaxAdvisor").
Include("PeFund").
Include("PeFund.Currency").
Include("BankAccount").
Include("BankAccount.Currency").
Include("BankAccount.BankContact").
Where(c => c.PeFundId == fundId).
ToListAsync();
//Add additional Information to properties
foreach (InvestorCommitment commitment in commitmentsForAFund)
{
FillFullName(commitment.Investor);
if (commitment.Investor.ClientAdvisorId > 0)
{
FillDisplayName(commitment.Investor.ClientAdvisor);
}
}
return(commitmentsForAFund);
}
}
public static Initiator GetInitiatorById(int id)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.Initiators.FirstOrDefault(i => i.Id == id));
}
}
public static IEnumerable <ClientAdvisor> GetClientAdvisors()
{
if (clientAdvisors == null)
{
using (HqTrustData dbContext = new HqTrustData())
{
clientAdvisors = dbContext.ClientAdvisors.OrderBy(a => a.AdvisorName.LastName).ToList();
}
// generate DisplayName using a few properties
foreach (ClientAdvisor advisor in clientAdvisors)
{
StringBuilder stringBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(advisor.AdvisorName.Title))
{
stringBuilder.Append($"{advisor.AdvisorName.Title} ");
}
stringBuilder.Append($"{advisor.AdvisorName.FirstName} {advisor.AdvisorName.LastName}");
if (!string.IsNullOrEmpty(advisor.CompanyName))
{
stringBuilder.Append($" ({advisor.CompanyName})");
}
advisor.DisplayName = stringBuilder.ToString();
}
}
return(clientAdvisors);
}
public Currency GetCurrencyById(int currencyId)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.Currencies.FirstOrDefault(c => c.Id == currencyId));
}
}
public static string GetApplicationInformation(string Key)
{
using (HqTrustData dbContext = new HqTrustData())
{
AnwendungsInformationen info = dbContext.AnwendungsInformationen.FirstOrDefault(a => a.Key == Key);
if (info == null)
{
// if record is not found add Anwndungsinformation-Record
info = new AnwendungsInformationen()
{
Key = Key,
Value = string.Empty
};
dbContext.AnwendungsInformationen.Add(info);
try
{
dbContext.SaveChanges();
}
catch (Exception)
{
throw new Exception("Fehler beim Eintragen einer AnwendungsInformation");
}
}
if (info.Value == null)
{
return(string.Empty);
}
else
{
return(info.Value);
}
}
}
/// <summary>
/// returns InvestorPcap for an InvestorCommitment and a date
/// returns null if not found
/// </summary>
/// <param name="commitmentId"></param>
/// <param name="quarterend"></param>
/// <returns></returns>
public static InvestorPcap GetPcapForCommitmentAndDate(int commitmentId, DateTime quarterend)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.InvestorPcaps.FirstOrDefault(p => p.InvestorCommitmentId == commitmentId && p.AsOfDate == quarterend));
}
}
/// <summary>
/// returns investorpcap for a commitmnet prior to a date
/// returns null if not available
/// </summary>
/// <param name="c"></param>
/// <param name="selectedQuarter"></param>
/// <returns></returns>
public static InvestorPcap GetLastPCap(InvestorCommitment c, DateTime selectedQuarter)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.InvestorPcaps.OrderByDescending(p => p.AsOfDate).
Where(p => p.InvestorCommitmentId == c.Id && p.AsOfDate < selectedQuarter).FirstOrDefault());
}
}
public static List <UniqueCashFlowNumber> GetCashFlowsForFund(int fundId)
{
using (HqTrustData dbContext = new HqTrustData())
{
List <UniqueCashFlowNumber> listOfCashFlows = dbContext.UniqueCashFlowNumbers.OrderBy(u => u.CashFlowDate).Where(u => u.PeFundId == fundId).ToList();
return(listOfCashFlows);
}
}
public static PeFund GetFundByFundName(string partialName)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.PeFunds.Where(p => p.FundName.Contains(partialName)).
FirstOrDefault());
}
}
public static PeFund GetPeFundByBeteiligungsnummer(string beteiligung)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.PeFunds.
FirstOrDefault(p => p.FundHqTrustNumber.Equals(beteiligung)));
}
}
public List <InvestorCashFlow> GetCashFlowsForCommitment(int commitmentId)
{
using (HqTrustData dbContext = new HqTrustData())
{
List <InvestorCashFlow> resultList = dbContext.InvestorCashFlows
.Where(c => c.InvestorCommitmentId == commitmentId).ToList();
return(resultList);
}
}
public static IEnumerable <Initiator> GetInitiators()
{
using (HqTrustData dbContext = new HqTrustData())
{
initiators = dbContext.Initiators.OrderBy(i => i.InitiatorName).ToList();
}
return(initiators);
}
public List <Investor> GetInvestorsByClientAdvisorId(int clientAdvisorId)
{
using (HqTrustData dbContext = new HqTrustData())
{
return(dbContext.Investors.
Include("ClientAdvisor").
Where(i => i.ClientAdvisorId == clientAdvisorId).
ToList());
}
}
public static IEnumerable <Currency> GetCurrencies()
{
if (currencies == null)
{
using (HqTrustData dbContext = new HqTrustData())
{
currencies = dbContext.Currencies.ToList();
}
}
return(currencies);
}
public static IEnumerable <Advisor> GetAdvisors()
{
if (advisors == null)
{
using (HqTrustData dbContext = new HqTrustData())
{
advisors = dbContext.Advisors.OrderBy(a => a.FullName).ToList();
}
}
return(advisors);
}
public static IEnumerable <FundType> GetFundTypes()
{
if (fundTypes == null)
{
using (HqTrustData dbContext = new HqTrustData())
{
fundTypes = dbContext.FundTypes.OrderBy(f => f.FundTypeName).ToList();
}
}
return(fundTypes);
}
public static IEnumerable <FundCompanySize> GetFundCompanySizes()
{
if (companySizes == null)
{
using (HqTrustData dbContext = new HqTrustData())
{
companySizes = dbContext.FundCompanySizes.OrderBy(f => f.CompanySize).ToList();
}
}
return(companySizes);
}
public static IEnumerable <FundGeography> GetFundGeographies()
{
if (geographies == null)
{
using (HqTrustData dbContext = new HqTrustData())
{
geographies = dbContext.FundGeographies.OrderBy(f => f.Geography).ToList();
}
}
return(geographies);
}