public static async Task <IEnumerable <Provision> > SearchProvisionsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = GetProvisionSearchQuery(request);
var results = await dataAccess.QueryAsync <Provision>(preparedQuery.Sql.ToString(), preparedQuery.Parameters);
return(results);
}
public static async Task <IEnumerable <Fund> > GetFundsAsync(this MasterSideLetterDataAccess dataAccess, string userName, string query = null, int?maxRows = null)
{
var sql = new StringBuilder("select ");
if (maxRows.HasValue)
{
sql.AppendFormat(" TOP {0} ", maxRows.Value);
}
sql.Append(
@"f.*
, uf.IsFavorite
, uf.LastAccessedDate
from v_Fund f
left join UserFund uf
on f.Id = uf.FundId
and uf.UserName = @userName");
if (!string.IsNullOrEmpty(query))
{
sql.Append(" where f.Name like '%' + @query + '%' or f.SponsorName like '%' + @query + '%' or f.BusinessUnitName like '%' + @query + '%' or f.StrategyName like '%' + @query + '%' ");
}
return(await dataAccess.QueryAsync <Fund>(sql.ToString(), new { userName, query }));
}
public static Task <IEnumerable <FundInvestor> > GetFundInvestorsByInvestorAsync(this MasterSideLetterDataAccess dataAccess, int investorId)
{
return(dataAccess.QueryAsync <FundInvestor>(
@"select fi.*
from v_FundInvestor fi
where fi.InvestorId = @investorId", new { investorId }));
}
public static async Task <IEnumerable <Provision> > GetProvisionsByFundInvestorAsync(this MasterSideLetterDataAccess dataAccess, int fundInvestorId)
{
var fundInvestorIds = new List <int> {
fundInvestorId
};
return(await dataAccess.QueryAsync <Provision>(@"select * from v_Provision p where p.FundInvestorId in @fundInvestorIds", new { fundInvestorIds }));
}
public static async Task <IEnumerable <string> > SearchProvisionTypesAsync(this MasterSideLetterDataAccess dataAccess, string query, int?limit = null)
{
var sql = new StringBuilder("select ");
if (limit.HasValue)
{
sql.Append($" TOP {limit} ");
}
sql.Append(" ProvisionType from v_Provision where ProvisionType like '%' + @query + '%' group by ProvisionType ");
return(await dataAccess.QueryAsync <string>(sql.ToString(), new { query, limit }));
}
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);
}
private static async Task AddRecentInvestmentsAsync(this MasterSideLetterDataAccess dataAccess, Investor investor)
{
var recentInvestments = await dataAccess.QueryAsync <Investment>(
@"select TOP 3
FundId,
FundName,
FundYear
from [v_FundInvestor]
where InvestorId = @investorId
and FundId is not null
and FundName is not null
group by FundId,FundName,FundYear
order by FundYear desc, FundName asc", new { investorId = investor.Id });
investor.RecentInvestments = recentInvestments.ToArray();
}
public static async Task <IEnumerable <Fund> > GetRecentFundsAsync(this MasterSideLetterDataAccess dataAccess, string userName, int?limit = null)
{
var sql = new StringBuilder("select ");
if (limit.HasValue)
{
sql.Append($" TOP {limit.Value} ");
}
sql.Append(@" f.*
, uf.IsFavorite
, uf.LastAccessedDate
from v_Fund f
join UserFund uf
on f.Id = uf.FundId
and uf.UserName = @userName
ORDER BY uf.LastAccessedDate DESC");
return(await dataAccess.QueryAsync <Fund>(sql.ToString(), new { 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 Task <IEnumerable <BusinessUnit> > GetBusinessUnitsAsync(this MasterSideLetterDataAccess dataAccess)
{
return(dataAccess.QueryAsync <BusinessUnit>("select * from BusinessUnit"));
}
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 <IEnumerable <Provision> > GetProvisionsAsync(this MasterSideLetterDataAccess dataAccess, List <int> provisionIds)
{
return(await dataAccess.QueryAsync <Provision>(@"select * from v_Provision p where p.Id in @provisionIds", new { provisionIds }));
}
public static Task <IEnumerable <Sponsor> > GetSponsorsAsync(this MasterSideLetterDataAccess dataAccess)
{
return(dataAccess.QueryAsync <Sponsor>("select * from Sponsor"));
}
public static async Task <List <Investor> > SearchInvestorsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.InvestorSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.InvestorSearchLimit} ");
}
preparedQuery.Sql.Append(" i.* , ui.IsFavorite, ui.LastAccessedDate ");
preparedQuery.Sql.Append(" from v_Investor i ");
preparedQuery.Sql.Append(" left join UserInvestor ui ");
preparedQuery.Sql.Append(" on i.Id = ui.InvestorId ");
preparedQuery.Sql.Append(" and ui.UserName = @UserName ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"i.Name",
"i.InvestorType"
},
preparedQuery);
}
// apply relevant filters
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and i.Name in @InvestorValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and i.InvestorType in @InvestorTypeValues ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and i.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and i.Aggregated <= @AggregateSizeMax ");
}
IEnumerable <Investor> results = await dataAccess.QueryAsync <Investor>(preparedQuery.Sql.ToString(), preparedQuery.Parameters);
var investors = results.ToList();
foreach (var investor in investors)
{
await dataAccess.AddRecentInvestmentsAsync(investor);
}
return(investors);
}
public static Task <IEnumerable <Strategy> > GetStrategiesAsync(this MasterSideLetterDataAccess dataAccess)
{
return(dataAccess.QueryAsync <Strategy>("select * from Strategy"));
}
public static async Task <IEnumerable <FundInvestor> > SearchFundInvestorsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.SideLetterSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.SideLetterSearchLimit} ");
}
preparedQuery.Sql.Append(" fi.* ");
preparedQuery.Sql.Append(" from v_FundInvestor fi ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"fi.FundName",
"fi.FundSponsorName",
"fi.FundBusinessUnitName",
"fi.FundStrategyName",
"fi.InvestorName",
"fi.InvestorType",
"fi.Entity",
"fi.Counsel",
"fi.Notes",
"fi.SideLetterFileName"
},
preparedQuery);
}
// apply relevant filters
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundName in @FundValues ");
}
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.InvestorName in @InvestorValues ");
}
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundSponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundBusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.FundStrategyName in @StrategyValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.InvestorType in @InvestorTypeValues ");
}
if (request.EntityValues != null && request.EntityValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.Entity in @EntityValues ");
}
if (request.CounselValues != null && request.CounselValues.Length > 0)
{
preparedQuery.Sql.Append(" and fi.Counsel in @CounselValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundSize >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundSize <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundYear >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundYear <= @YearMax ");
}
if (request.CommitmentMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.Commitment >= @CommitmentMin ");
}
if (request.CommitmentMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.Commitment <= @CommitmentMax ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and fi.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and fi.Aggregated <= @AggregateSizeMax ");
}
if (request.InvestorId.HasValue)
{
preparedQuery.Sql.Append(" and fi.InvestorId = @InvestorId ");
}
if (request.FundId.HasValue)
{
preparedQuery.Sql.Append(" and fi.FundId = @FundId");
}
return(await dataAccess.QueryAsync <FundInvestor>(preparedQuery.Sql.ToString(), preparedQuery.Parameters));
}
public static async Task <IEnumerable <string> > GetFilterContentAsync(this MasterSideLetterDataAccess dataAccess, FilterContentRequest request)
{
var sql = new StringBuilder("select ");
if (request.FilterLimit > 0)
{
sql.Append($" TOP {request.FilterLimit} ");
}
sql.Append(" * from (");
switch (request.FilterType)
{
case "Fund":
sql.Append(
@"select distinct Name
from Fund
where Name like '%' + @TargetText + '%' ");
break;
case "Investor":
sql.Append(
@"select distinct Name
from Investor
where Name like '%' + @TargetText + '%' ");
break;
case "Sponsor":
sql.Append(
@"select distinct SponsorName
from v_SponsorNames
where SponsorName like '%' + @TargetText + '%' ");
break;
case "BusinessUnit":
sql.Append(
@"select distinct BusinessUnitName
from v_BusinessUnitNames
where BusinessUnitName like '%' + @TargetText + '%' ");
break;
case "Strategy":
sql.Append(
@"select distinct StrategyName
from v_StrategyNames
where StrategyName like '%' + @TargetText + '%' ");
break;
case "InvestorType":
sql.Append(
@"select distinct InvestorType
from Investor
where InvestorType like '%' + @TargetText + '%' ");
break;
case "ProvisionType":
sql.Append(
@"select distinct ProvisionType
from v_Provision
where ProvisionType like '%' + @TargetText + '%' ");
break;
case "Entity":
sql.Append(
@"select distinct Entity
from FundInvestor
where Entity like '%' + @TargetText + '%' ");
break;
case "Counsel":
sql.Append(
@"select distinct Counsel
from FundInvestor
where Counsel like '%' + @TargetText + '%' ");
break;
}
sql.Append(") as s");
return(await dataAccess.QueryAsync <string>(sql.ToString(), request));
}
public static async Task <IEnumerable <Fund> > SearchFundsAsync(this MasterSideLetterDataAccess dataAccess, SearchRequest request)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request.FundSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.FundSearchLimit} ");
}
preparedQuery.Sql.Append(" f.*, uf.IsFavorite, uf.LastAccessedDate ");
preparedQuery.Sql.Append(" from v_Fund f ");
preparedQuery.Sql.Append(" left join UserFund uf ");
preparedQuery.Sql.Append(" on f.Id = uf.FundId ");
preparedQuery.Sql.Append(" and uf.UserName = @UserName ");
preparedQuery.Sql.Append(" where 1=1 ");
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"f.Name",
"f.SponsorName",
"f.BusinessUnitName",
"f.StrategyName"
},
preparedQuery);
}
// apply relevant filters
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.Name in @FundValues ");
}
// apply relevant filters
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.SponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.BusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and f.StrategyName in @StrategyValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and f.Size >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and f.Size <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and f.Year >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and f.Year <= @YearMax ");
}
preparedQuery.Sql.Append(" ORDER BY uf.LastAccessedDate DESC ");
return(await dataAccess.QueryAsync <Fund>(preparedQuery.Sql.ToString(), preparedQuery.Parameters));
}