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 <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);
}
private static PreparedQuery GetProvisionSearchQuery(SearchRequest request, bool?hasType = null)
{
var preparedQuery = new PreparedQuery(request);
preparedQuery.Sql.Append("select");
if (request != null && request.ProvisionSearchLimit > 0)
{
preparedQuery.Sql.Append($" TOP {request.ProvisionSearchLimit} ");
}
preparedQuery.Sql.Append(" p.* ");
preparedQuery.Sql.Append(" from v_Provision p ");
preparedQuery.Sql.Append(" where 1=1 ");
if (hasType.HasValue)
{
preparedQuery.Sql.Append(hasType == true ? " and p.ProvisionType is not null " : " and p.ProvisionType is null ");
}
if (request != null)
{
// apply target text to all text columns
if (!string.IsNullOrEmpty(request.TargetText))
{
SearchHelper.AppendSearchCondition(request.TargetText,
new List <string>
{
"p.ProvisionType",
"p.Content",
"p.FundName",
"p.FundSponsorName",
"p.FundBusinessUnitName",
"p.FundStrategyName",
"p.InvestorName",
"p.InvestorType",
"p.Entity",
"p.Counsel",
"p.Notes",
"p.SideLetterFileName"
},
preparedQuery);
}
if (request.FundValues != null && request.FundValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundName in @FundValues ");
}
if (request.InvestorValues != null && request.InvestorValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.InvestorName in @InvestorValues ");
}
if (request.SponsorValues != null && request.SponsorValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundSponsorName in @SponsorValues ");
}
if (request.BusinessUnitValues != null && request.BusinessUnitValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundBusinessUnitName in @BusinessUnitValues ");
}
if (request.StrategyValues != null && request.StrategyValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundStrategyName in @StrategyValues ");
}
if (request.InvestorTypeValues != null && request.InvestorTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.InvestorType in @InvestorTypeValues ");
}
if (request.ProvisionTypeValues != null && request.ProvisionTypeValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.ProvisionType in @ProvisionTypeValues ");
}
if (request.EntityValues != null && request.EntityValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.Entity in @EntityValues ");
}
if (request.CounselValues != null && request.CounselValues.Length > 0)
{
preparedQuery.Sql.Append(" and p.Counsel in @counselValues ");
}
if (request.SizeMin.HasValue)
{
preparedQuery.Sql.Append(" and p.FundSize >= @SizeMin ");
}
if (request.SizeMax.HasValue)
{
preparedQuery.Sql.Append(" and p.FundSize <= @SizeMax ");
}
if (request.YearMin.HasValue)
{
preparedQuery.Sql.Append(" and p.FundYear >= @YearMin ");
}
if (request.YearMax.HasValue)
{
preparedQuery.Sql.Append(" and p.FundYear <= @YearMax ");
}
if (request.CommitmentMin.HasValue)
{
preparedQuery.Sql.Append(" and p.Commitment >= @CommitmentMin ");
}
if (request.CommitmentMax.HasValue)
{
preparedQuery.Sql.Append(" and p.Commitment <= @CommitmentMax ");
}
if (request.AggregateSizeMin.HasValue)
{
preparedQuery.Sql.Append(" and p.Aggregated >= @AggregateSizeMin ");
}
if (request.AggregateSizeMax.HasValue)
{
preparedQuery.Sql.Append(" and p.Aggregated <= @AggregateSizeMax ");
}
if (request.FundId.HasValue)
{
preparedQuery.Sql.Append(" and p.FundId = @fundId ");
}
if (request.InvestorId.HasValue)
{
preparedQuery.Sql.Append(" and p.InvestorId = @investorId ");
}
if (request.FundInvestorIds != null && request.FundInvestorIds.Length > 0)
{
preparedQuery.Sql.Append(" and p.FundInvestorId in @fundInvestorIds ");
}
}
preparedQuery.Sql.Append(" order by p.Content ");
return(preparedQuery);
}
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));
}
