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