public static async Task <Investor> GetInvestorAsync(this MasterSideLetterDataAccess dataAccess, int id, string userName)
{
return(await dataAccess.QueryFirstOrDefaultAsync <Investor>(
@"select i.*
, ui.IsFavorite
, ui.LastAccessedDate
from Investor i
left join UserInvestor ui
on i.Id = ui.InvestorId
and ui.UserName = @userName
where Id = @id", new { id, userName }));
}
public static async Task <IEnumerable <Investor> > GetInvestorsAsync(this MasterSideLetterDataAccess dataAccess, string userName, string query = null, string typeQuery = null, int?maxRows = null)
{
var sql = new StringBuilder("select ");
if (maxRows.HasValue)
{
sql.AppendFormat(" TOP {0} ", maxRows.Value);
}
sql.Append(
@"i.*
, ui.IsFavorite
, ui.LastAccessedDate
from Investor i
left join UserInvestor ui
on i.Id = ui.InvestorId
and ui.UserName = @userName
where 1 = 1 ");
if (!string.IsNullOrEmpty(query))
{
sql.Append(" and (i.Name like '%' + @query + '%' OR i.InvestorType like '%' + @query + '%') ");
}
if (!string.IsNullOrEmpty(typeQuery))
{
sql.Append(" and i.InvestorType like '%' + @typeQuery + '%'");
}
var investorList = (await dataAccess.QueryAsync <Investor>(sql.ToString(), new { userName, query, typeQuery })).ToList();
foreach (var investor in investorList)
{
var fundInvestor = await dataAccess.QueryFirstOrDefaultAsync <Investor>(
@"select
InvestorId,
SUM(Commitment) as aggregated,
Count(FundId) as fundNos
from [v_FundInvestor] where InvestorId = @investorId
group by InvestorId", new { investorId = investor.Id });
if (fundInvestor != null)
{
investor.Aggregated = fundInvestor.Aggregated;
investor.FundNos = fundInvestor.FundNos;
}
await dataAccess.AddRecentInvestmentsAsync(investor);
}
return(investorList);
}
public static async Task <Fund> GetFundAsync(this MasterSideLetterDataAccess dataAccess, int id, string userName)
{
return(await dataAccess.QueryFirstOrDefaultAsync <Fund>(
@"select
f.*
, uf.IsFavorite
, uf.LastAccessedDate
from v_Fund f
left Join UserFund uf
on f.Id = uf.FundId
and uf.UserName = @userName
where f.Id = @id
", new { id, userName }));
}
public static async Task <IEnumerable <Investor> > GetFavoriteInvestorsAsync(this MasterSideLetterDataAccess dataAccess, string userName, int?limit = null)
{
var sql = new StringBuilder("select ");
if (limit.HasValue)
{
sql.Append($" TOP {limit.Value} ");
}
sql.Append(@" i.*
, ui.IsFavorite
, ui.LastAccessedDate
from Investor i
join UserInvestor ui
on i.Id = ui.InvestorId
and ui.UserName = @userName
where ui.IsFavorite = 1");
var investorList = (await dataAccess.QueryAsync <Investor>(sql.ToString(), new { userName })).ToList();
foreach (var investor in investorList)
{
var fundInvestor = await dataAccess.QueryFirstOrDefaultAsync <Investor>(
@"select
InvestorId,
SUM(Commitment) as aggregated,
Count(FundId) as fundNos
from [v_FundInvestor] where InvestorId = @investorId
group by InvestorId", new { investorId = investor.Id });
if (fundInvestor != null)
{
investor.Aggregated = fundInvestor.Aggregated;
investor.FundNos = fundInvestor.FundNos;
}
await dataAccess.AddRecentInvestmentsAsync(investor);
}
return(investorList);
}
public static async Task <Document> GetFundInvestorSideLetterAsync(this MasterSideLetterDataAccess dataAccess, int id)
{
return(await dataAccess.QueryFirstOrDefaultAsync <Document>("select SideLetterFileName Name, SideLetterFileContent Content from FundInvestor where Id = @id", new { id }));
}
public static Task <BusinessUnit> GetBusinessUnitAsync(this MasterSideLetterDataAccess dataAccess, int id)
{
return(dataAccess.QueryFirstOrDefaultAsync <BusinessUnit>("select * from BusinessUnit where Id = @id", new { id }));
}
public static Task <BusinessUnit> GetBusinessUnitByNameAsync(this MasterSideLetterDataAccess dataAccess, string name)
{
return(dataAccess.QueryFirstOrDefaultAsync <BusinessUnit>("select * from BusinessUnit where Name = @name", new { name }));
}
public static async Task <MasterSideLetterContent> GenerateMasterSideLetterContentAsync(this MasterSideLetterDataAccess dataAccess, int fundId, IList <int> fundInvestorIds)
{
var msl = new MasterSideLetterContent();
msl.Fund = await dataAccess.QueryFirstOrDefaultAsync <Fund>("select * from v_Fund where Id = @fundId", new { fundId });
var fundInvestorIndex = new Dictionary <int, FundInvestor>();
var provisions = await dataAccess.QueryAsync <Provision>("select * from v_Provision where FundInvestorId in @fundInvestorIds and ProvisionType is not null", new { fundInvestorIds });
var provisionTypeGroups = provisions.OrderBy(p => p.ProvisionType).GroupBy(p => p.ProvisionType);
foreach (var provisionTypeGroup in provisionTypeGroups)
{
var provisionTypeSection = new MasterSideLetterContent.ProvisionTypeSection
{
ProvisionType = provisionTypeGroup.Key
};
var contentGroups = provisionTypeGroup.GroupBy(c => NormalizeContent(c.Content)).OrderByDescending(g => g.Count());
foreach (var contentGroup in contentGroups)
{
var provisionSection = new MasterSideLetterContent.ProvisionSection
{
Content = contentGroup.First().Content
};
foreach (var provision in contentGroup)
{
FundInvestor fundInvestor;
if (fundInvestorIndex.ContainsKey(provision.FundInvestorId))
{
fundInvestor = fundInvestorIndex[provision.FundInvestorId];
}
else
{
fundInvestor = new FundInvestor
{
Id = provision.FundInvestorId,
FundId = provision.FundId,
FundName = provision.FundName,
FundSponsorName = provision.FundSponsorName,
FundBusinessUnitName = provision.FundBusinessUnitName,
FundStrategyName = provision.FundStrategyName,
FundYear = provision.FundYear,
FundSize = provision.FundSize,
InvestorId = provision.InvestorId,
InvestorName = provision.InvestorName,
InvestorType = provision.InvestorType,
Entity = provision.Entity,
Commitment = provision.Commitment,
Counsel = provision.Counsel,
Notes = provision.Notes,
SideLetterFileName = provision.SideLetterFileName
};
fundInvestorIndex.Add(fundInvestor.Id, fundInvestor);
}
provisionSection.FundInvestors.Add(fundInvestor);
}
provisionTypeSection.ProvisionSections.Add(provisionSection);
}
msl.ProvisionTypeSections.Add(provisionTypeSection);
}
msl.FundInvestors.AddRange(fundInvestorIndex.Values.OrderBy(fi => fi.InvestorName).ThenBy(fi => fi.Entity));
return(msl);
}
public static async Task <Provision> GetProvisionAsync(this MasterSideLetterDataAccess dataAccess, int id)
{
return(await dataAccess.QueryFirstOrDefaultAsync <Provision>(@"select * from v_Provision where Id = @id", new { id }));
}
public static Task <Sponsor> GetSponsorAsync(this MasterSideLetterDataAccess dataAccess, int id)
{
return(dataAccess.QueryFirstOrDefaultAsync <Sponsor>("select * from Sponsor where Id = @id", new { id }));
}
public static Task <Sponsor> GetSponsorByNameAsync(this MasterSideLetterDataAccess dataAccess, string name)
{
return(dataAccess.QueryFirstOrDefaultAsync <Sponsor>("select * from Sponsor where Name = @name", new { name }));
}
public static Task <Fund> GetFundByNameAsync(this MasterSideLetterDataAccess dataAccess, string name)
{
return(dataAccess.QueryFirstOrDefaultAsync <Fund>("select * from Fund where Name = @name", new { name }));
}
public static async Task <SearchSettings> GetSearchSettingsAsync(this MasterSideLetterDataAccess dataAccess)
{
return(await dataAccess.QueryFirstOrDefaultAsync <SearchSettings>("select * from SearchSettings"));
}